Avoiding deadlocks using new READ_COMMITED_SNAPSHOT isolation level in SQL 2005

Programmer A: There's a deadlock happening when this procedure is run

ProgrammerB: Did you try using NOLOCK hint in SELECT statements? It should prevent most deadlocks from happening

...

How many times have we heard the above type of conversation in our daily life?

One of most common and quick solutions provided for handling deadlock scenarios involves the use of NOLOCK table hint. NOLOCK hint actually ignores the normal locks that are placed on the object and held by a transaction and it allows the current query to complete without waiting for the first transaction to finish and release the locks. The problem with this approach is that it often returns unstable data like data which is uncommited and in use by the current transaction and you may end with dirty reads. So unless you're 100% sure that data you're dealing with is non volatile NOLOCK should not be used.

Fortunately SQL 2005 has brought for us a new isolation level called READ_COMMITTED_SNAPSHOT which we can leverage upon for dealing with similar situations. READ_COMMITTED_SNAPSHOT isolation level works based on row versioning concept and hence improves read concurrency avoiding deadlocks on most cases

READ_COMMITTED_SNAPSHOT works closely like the READ_COMMITTED isolation level which is the default mode. Only difference is that it uses row versioning rather than locking with the aim of providing read consistency.

When a statement runs in the READ_COMMITTED isolation level with READ_COMMITTED_SNAPSHOT option turned on, it sees snapshot of stable data as on start of current transaction. This is done by creating a row-versioned snapshot which will be used for any subsequent retrieval of data (reads). Since it uses the row-versioned snapshot any subsequent reads will not block writes and vice versa. In comparison, earlier isolation levels make use of locking which is the cause for concurrency issues. Please keep in mind that a write operation will still block another write operation to prevent any possible data corruption. This will help us to avoid most of the deadlocking situations and also prevents any dirty data from being read by our transaction.

You can turn this feature on at database level using below statement

ALTER DATABASE
SET READ_COMMITTED_SNAPSHOT ON