Defensive Db Programming Chapter 09

Comments: No Comments
Published on: November 22, 2010

It is down to the final two chapters of the book by Alex Kuznetsova.  Check out the previous chapter here.  The review of this book is certainly taking longer to produce than I had planned.  However, I think you find that the book is still worth it and that this little series is also worth it.  I hope that the spread of this series is at least getting to a few more people here and there and that more people may get a copy of “Defensive Database Programming”.

This chapter is about concurrent queries and Isolation Levels.  I think these seem to be a hot button of late – at least I have been hearing more and more talk about them than in the near past.  Alex starts the chapter with a stark realization.  He states:

Even the simplest SELECT, against one table, may retrieve incorrect results when the base table is being modified at the same time. (p. 297)

Think about that statement for a bit.  I imagine it won’t take you long to realize that it is true.  I am certain that you have come up with several examples that underscore that statement.

Alex then progresses through Isolation levels giving a demonstration of what will happen under the described circumstances.  This chapter is not intended to explain the differences between the Isolation levels, but merely to demonstrate what could happen – as has been the theme throughout the book to this juncture.  These examples that Alex uses first describe a theoretical problem and then he reproduces the theoretical problem with real world scenarios.

Alex also outlines in this chapter some considerations for choosing the correct Isolation level for your business needs.  Pros and Cons are weighed and usefulness of the Isolation level is discussed.  Alex also offers up the notion of using a query hint to issue a table lock when querying the database.  Due to the reciprocal effects Alex also states that it is almost never really an option.

Alex goes the extra mile in this chapter by discussing how we can minimize deadlocks.

In the end Alex makes a recommendation as to which Isolation level he would use.  Check it out and see for yourself.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

November 2010
« Oct   Dec »


  • @mexicanrooster: #sqlhelp Hi all: Does anyone know how to get the file that is being used by extended event? I want to query the file after not longer used.
  • @SQLTom: @AMtwo Thanks Andy, that's my plan. I'm short on time though and there is no actual RPT/RTO yet. Getting them is high priority. #sqlhelp
  • @AMtwo: @SQLTom If native backups can meet your requirements, then why use (and spend money on) 3rd party tools? #sqlhelp #OccamsRazor
  • @AMtwo: @SQLTom Start with your RPO, RTO, and restore requirements/scenarios. Then work your way back to backup solutions that work #sqlhelp
  • @wendy_dance: @SQLTom #sqlhelp However, that is much more easily avoided if you take native backups and let the 3rd party tool back THOSE up
  • @wendy_dance: @SQLTom #sqlhelp Don't recall which vendor, but one had to take the db offline to do the 'backup' which was copying the .mdf, etc files
  • @wendy_dance: @SQLTom #sqlhelp Most of the issues aren't the process of the backup, but rather the Restore. Run a few tests. Validity? Time to Restore?
  • @SQLTom: @AMtwo I don't want 2 avoid them, but if I can't show specific cases of problems I'm going to be forced to use 3rd party #sqlhelp
  • @AMtwo: @SQLTom Why do you want to avoid native backups? Most people want the opposite--to avoid 3rd party dependencies. #sqlhelp
  • @SQLTom: NE1 using CommVault exclusively for backups? Have u had any issues? How about other backup solutions that avoid native SQL backups? #sqlhelp

Welcome , today is Tuesday, December 1, 2015