Locking, isolation, and read consistency

Today I ran into a really interesting locking / blocking problem that I want to discuss… originally, a related discussion came up last week in our IEPTO1 course (Immersion Event on Performance Tuning – Part 1) but today I ran into a similar problem that brought me back to the original discussion and I was able to reproduce the original scenario with a really interesting combination of things (some of which I did not expect and that led me down a super interesting path). However, before I can get to the weirdness (and try to make sense of it), I need to make sure the foundation is set well… In IEPTO1, I discuss locking types and how long locks are held for the different isolation levels – spending a great deal of time talking about the default mode of locking (read committed – with locking) vs. all other options:

• Read uncommitted ( same as using NOLOCK ) and a lower isolation level than the default. This allows dirty reads.
• Read committed using locking ( this is the DEFAULT when read_committed_snapshot has NOT been turned on )
• Read committed using version ( this is the DEFAULT when read_committed_snapshot has been turned on )
• Repeatable reads ( which also uses locking – even when one or both flavors of versioning has been enabled )
• Serializable ( which also uses locking [ same as using HOLDLOCK ] – even when one or both flavors of versioning has been enabled )
• Snapshot Isolation ( which is ALLOWED if allow_snapshot_isolation has been turned on and the client requests it, but won’t be used by anyone unless SET TRANSACTION ISOLATION LEVEL SNAPSHOT has been requested. Here you’ll have all the overhead of versioning without anyone using it unless you make code changes for snapshot isolation.)

Essentially, your database can be configured for one of FOUR possible states:

1. No options set (connections default to read committed using locking)
2. ONLY read_committed_snapshot set ( connections default to read committed using versioning; no other code changes needed for read committed statements ). This provides statement-level read consistency. Every read will reconcile to the point in time when the STATEMENT started.
3. ONLY allow_snapshot_isolation set ( connections DEFAULT to read committed using LOCKING – because of the absence of read_committed_snapshot ). For those that request snapshot isolation, this provides transaction-level read consistency. Every read will reconcile to the point in time when the TRANSACTION started.
4. BOTH read_committed_snapshot and allow_snapshot_isolation set. Without snapshot transaction isolation level requested, statements will reconcile to the point in time that the statement started and when snapshot isolation is requested, then all statements will reconcile to the point in time that the transaction began.

To be honest, I’ve been wanting to get that “simple” view of the locking world written down for a while. Even the combination of options ( that a database can really be configured into one of four possible states ) is not very well known. Often content about versioning just states that you need to turn both options on and they don’t describe that they’re really distinct environments.

Statement-level Read Consistency

What I love about read_committed_snapshot is that you get statement-level read consistency… what this means is that you can get a definable point in time to which your statement reconciles – that point, the time when the statement started. So, if you ask for a count of rows, you get THE count of rows that were present when your statement started. The best part about it is that this count is not only accurate to the point in time that hte statement started, it also does so without preventing transactions / locks on the object where you’re counting rows. Without a lot of detail here – it does this by copying the transactionally consistent VERSION of the row into the version store and leaving behind a pointer to allow readers to use without being blocked AND without blocking other writers. The marketing tagline makes it sound perfect ( readers don’t block writers and writers don’t block readers ). All of this happens AUTOMATICALLY and without code changes for all statements running with read committed isolation. If a statement has a hard-coded lock hint ( like NOLOCK or HOLDLOCK, etc. ) then their statement-level hints will override this and use locking.

NOTE: Windows Azure SQL Database defaults to read committed using VERSIONING for new databases.

Transaction-level Read Consistency

What I love about allow_snapshot_isolation is that you don’t have to use it, unless you really want version-based TRANSACTIONS. And, oddly, many of you won’t want this for the majority of your complex updates / OLTP transactions ( where you’ll also have to deal with version-based reads and update conflicts ). If your writers are in read committed using locking OR read committed using versioning then an update will use locks and not version-based reads so conflicts are prevented. The place to use transaction-level read consistency is for “transactions” that will handle multiple reads across volatile data. Imagine that you want to have 3 reports / queries reconcile to the same point in time ( and you want to do this real-time reporting in your OLTP environment ). What you could do is this:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION
SELECT... --query1
SELECT... --query2
SELECT... --query3
COMMIT TRANSACTION
GO

So, your use of snapshot isolation should be a bit more limited IMO. And, largely limited to controlled reporting. Yes, you can do this with modifications / transactions… but if you have transaction-level read consistency and then you have multiple readers / writers to the same data then you’ll have to make sure you have good error handling ( you should be using TRY / CATCH ) so that you can deal with the conflict detected by SQL Server and resolved with the following error:

Msg 3960, Level 16, State 2, Line 6
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.member' directly or indirectly in database 'Credit' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

In Summary

So, to bring it all together. All LOCKING discussions really need to start with how the session and the database are handling isolation. Different behaviors occur in the different configurations and some locks are held for different amounts of time depending on your isolation level.

And, that’s a good start for now. I wanted to get a few of these basics out there first as I have a couple of problems I want to describe (and resolve) with long running transactions and blocking. And, some special cases with schema locks as well as some special code I’ve written to reduce some of the horrible problems you can run into with long blocking chains (and my code works across earlier versions of SQL Server and for MORE statements than just partition switching and index rebuilds… but, I’m getting ahead of myself on this one). Stay tuned, I’m going to get to those in tomorrow’s post and I’ll compare them against the low-priority lock wait feature in SQL Server 2014.

Also, if you’re more interested in learning more about versioning – check out the whitepaper I wrote (for SQL 2005) and which was updated for name changes and a few other things by Neal Graves ( thanks Neal! ): SQL Server 2005 Row Versioning-based Transaction Isolation

Thanks for reading!
k