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:

USE

 [test]

 GO

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

GO

CREATE TABLE[dbo].[test1]

 ([col1][nchar](10NULL,[col2][nchar](10NULL)

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

USE

 [test]

 GO

begin tran

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

 GO

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

ALTER DATABASE[test]SET READ_COMMITTED_SNAPSHOTON WITH NO_WAIT

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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




Categories

December 2012
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

%d bloggers like this: