Chapter 3:Advanced locking concepts looks into how the factors of lock escalation and lock mode conversion dictate the path that the locking strategy will follow.  It continues the exploration of the key range lock and its sub types, and wraps it up with a look at two obscure type of locks: latches which are physical locks ( as opposed to the logical ones we've been examining so far) employed by the engine to protect its internal structures, and compile locks.

Chapter 4 shows the various ways of overriding SQL Server's default locking behavior :

• setting the isolation levels, as  already examined
• setting lock timeouts that upon their expiration force the application to skip waiting on a restore that's still blocked,
• bound connections,
• user defined application locks
• table wide lock hints which integrate into the query and force the engine into using the designated instruction

With reference to this last method the author notes that selecting "hint" was an unfortunate choice due to the word's interpretation as "suggestion", since SQL Server will always override the defaults and use the locking instruction dictated by the hint.

I can't help but think that lock hints are a more sophisticated form of the pre-ANSI SQL vendor-dependent locking so, exceptional circumstance aside, use this option parsimoniously and always let the engine make the call.

Chapter 5 adds an epilogue to pessimistic concurrency by going through the ways of identifying and resolving the most common bottleneck factors, mainly excessive locking, blocking and deadlocking.

Chapter 6, the last one, is dedicated to the pessimistic's model's alter ego, optimistic, which comes in two forms depending on the scope of the lock;

• Snapshot Isolation at the transaction level
• Read Committed Snapshot Isolation at the statement level.

The latter model offers a much greater degree of concurrency without compromising integrity, because readers do not block writers and writers do not block readers despite the fact that locks are only taken when data gets modified as writers still block writers.

SQL Server uses an implementation called row-versioning or MVCC, which works its magic by taking snapshots of the modified and committed rows, which is then able to re-construct the moment a transaction or statement begins executing, thus each transaction or statement gets a consistent view of the database.

Before MVCC, using a time stamp or comparing the client's copy of the data to the one currently on the server, were awkward ways being used to check for update conflicts. Sounds like a "poor man's optimistic concurrency"....

It's worth bearing in mind that when making the switch from pessimistic to optimistic, the latter provides an outcome related to the instant the query or transaction begins while the former, an outcome related to the instant the query or transaction finishes. What that actually means is left as an exercise to the reader, although I think that a side by side comparison of each model's outcome by using the same input would make the difference clear. But anyway, that difference can lead to mistakenly believing that  Snapshot either returned incorrect results or that it did malfunction.

This book is available as a free PDF from the Redgate website Click on the book cover to access download

In conclusion, the the author's clear and coherent explanations, the down-to-earth practical examples, the presentation with its small number of pages and great typography (big fonts, large padding and spacing), work together for an easy flowing and intriguing read.

Recommended to all developers working on database applications, DBA's monitoring their DBMS, scholars interested in the innards of concurrency, or even to students taking a class on databases, as the essential guide to turn to when performance, integrity and concurrency are not negotiable; and that is, always...