Avoiding deadlocks by using READ_COMMITED_SNAPSHOT isolation level

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

Programmer 2: 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 calledREAD_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

Apply Operator

One of major features that I've been fascinated by in SQL Server 2005 has been the APPLY operator. This article discusses on some of the real time scenarios where you can make use of this feature. 

Scenario 1 - Running Total of a field

Consider the case where you need a running total to be calculated for a particular field. Here you can use the apply operator to calculate running total and return with each row.See below for illustration


declare @tbl table(Product_ID int identity(1,1),
Product_Name varchar(100)
)insert @tbl (Product_Name)select 'Boots' union allselect 'Peter England' union allselect 'Carona' union allselect 'Marks & Spencers'declare @tbl_items table(Item_ID int identity(1,1),
Product_ID int,
Item_Date datetime,
Price numeric(10,2)
)insert into @tbl_items (Product_ID,Item_date,Price)select 1,'20 dec 2009',150 union allselect 2,'13 Apr 2009',908 union allselect 1,'5 Mar 2009',1974 union allselect 3,'05 jan 2010',647 union allselect 2,'13 Jun 2009',832 union allselect 3,'19 May 2008',413 union allselect 3,'13 May 2009',235 union allselect 1,'23 Oct 2009',630 union allselect 1,'30 Aug 2009',110
select Product_Name,Item_date,Price, Totalfrom @tbl tjoin @tbl_items tion ti.Product_ID=t.Product_IDcross apply (select sum(Price) as Total
from @tbl_items
where Product_ID=ti.Product_ID
and Item_date<=ti.Item_date) t1Order by t.Product_ID,Item_Date

Scenario 2 - Get most recent or most ancient related data in case of 1-to -n relationship

Consider the case where you have one to many relationship existing between two tables and you want latest/earliest related detail from second table for every row of first table. In this case of you can use apply operator to achieve the result.

As an example using sample data from top, you can use below query to get latest sale detail of each product

select Product_Name,Item_date,Pricefrom @tbl tcross apply (select top 1 Item_date,Price
from @tbl_items
where Product_ID=t.Product_ID
Order by Item_date desc) t1Order by t.Product_ID

Result

Scenario 3 - Get concatenated string list of related field

Consider the case where you need to return a comma separated value of field which is related to master table field. Here also you can use apply operator coupled with for xml path to get the related values as csv list.

DECLARE @Student table(Student_ID int identity(1,1),Student_name varchar(100)
)INSERT @Student (Student_Name)SELECT 'Ram' union allSELECT 'Sita' union allSELECT 'Murali' union allSELECT 'Siddharth' union allSELECT 'Jacob'declare @Subjects table(Subject_ID int identity(1,1),
Subject_Name varchar(100)
)INSERT @Subjects (Subject_Name)SELECT 'Maths' union allSELECT 'English' union allSELECT 'Biology' union allSELECT 'Physics' union allSELECT 'Chemistry' union allSELECT 'Hindi'DECLARE @Student_subjects table(ID int identity(1,1),Student_ID int,
Subject_ID int
)INSERT INTO @Student_Subjects (Student_ID,Subject_ID)SELECT 1,1 union allSELECT 3,2 union allSELECT 4,1 union allSELECT 4,3 union allSELECT 4,5 union allSELECT 2,1 union allSELECT 3,1 union allSELECT 2,3 union allSELECT 1,3 union allSELECT 2,4 union allSELECT 5,3
SELECT s.Student_Name,left(t.u,len(t.u)-1)FROM @Student scross apply (select sub.Subject_name + ','
from @Subjects sub
inner join @Student_subjects ss
on ss.Subject_ID=sub.Subject_ID
where Student_ID=s.Student_ID
for xml path(''))t(u)

Scenario 4 - Call a table valued UDF passing each row value to do some processing

Consider the case where you need to parse a comma separated value in a field stored in a table field. Here also you can use apply operator to pass values to udf and return the individual values delimited by comma for each row of table.