Blocking And READ_COMMITTED_SNAPSHOT Isolation level

I was troubleshooting sustained blocking in sql server 2005 that lead to deadlock from java application: As I tried to capture the inputbuffer of the processes involved in blocking found out that there is one process which is constantly changing its status from sleeping to runnable and vice versa. This process is the culprit (chain blocker). As I mentioned often times the blocker process was in sleep mode and yet blocking and keeping other process hostage preventing them from completing. As there are multiple application servers and web servers involved in sending batch processes to sql server it is reasonable to think that the batches could be running in transactions and dependent on some middle tier process as well. I thought of writing up simple test I did in a separate environment to prove about blocking:

Steps Performed:

1. Create test database in sql 2005 with default setting

— Create database test

2. Create test table with two columns as below:




/****** Object: Table [dbo].[test1] Script Date: 12/12/2012 4:42:41 PM ******/


CREATE TABLE[dbo].[test1]


3. Insert three sample rows

INSERT INTO[dbo].[test1] ([col1],[col2]VALUES (‘test1’,‘test2’)

INSERT INTO[dbo].[test1] ([col1],[col2]) VALUES (‘test2′,’test3’)

INSERT INTO[dbo].[test1] ([col1],[col2]) VALUES (‘test3′,’test4’)

4. Open query window and issue the below update statement with holdlock hint and with begin transaction without commit




begin tran

 UPDATE [dbo].[test1]with (holdlockSET[col1]=‘test_new’ WHERE col1=‘test2’


5. Then open another query window and issue the below simple select statement

select * from dbo.test1

 The above select query will never finish because it is blocked by the update statement from step 4

If you run the below query you see the blocked and blocking process similar to the output shown below

select spid,blocked,waitresource,open_tran,status from sys.sysprocesses where blocked<>0 or open_tran<>0

spid blocked Waitresource Open_tran Status
56 0   1 sleeping
57 56 TAB: 5:2073058421:0 0 suspended

However if the database isolation level was set to READ_COMMITTED_SNAPSHOT step 5 will return values with old copy of the test table as the update in step 4 is not yet committed.

To run the above steps with the database isolation level set to READ_COMMITTED_SNAPSHOT close all connections to test database, run the below statement and repeat step 1 to step 5



0 Responses to “Blocking And READ_COMMITTED_SNAPSHOT Isolation level”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


December 2012
« Nov    

%d bloggers like this: