Archive

Archive for the ‘Isolation Level’ Category

Reads in Read Committed Isolation Level

23/07/2012 Leave a comment

I’ll start this blog post with a question: “What kinds of locks in SQL Server are acquired during simple select statement in read committed isolation level (READ_COMMITTED_SNAPSHOT database option is set to OFF)?”

Few days ago, I would answer: “Intent-Shared locks at table and pages level and shared locks on a row by row basis.”

But, I found out that it’s not correct.  In some situations shared locks are not issued at all. Let’s demonstrate that. First let’s create a database and table with 3 rows.

 CREATE DATABASE TestLocks
GO
ALTER DATABASE TestLocks SET READ_COMMITTED_SNAPSHOT OFF
GO
USE TestLocks
GO
-- Create table
CREATE TABLE dbo.DemoTable ( id int, col1 varchar(4000))
GO
-- Insert 3 rows = 2 pages
INSERT INTO dbo.DemoTable
SELECT 1, REPLICATE('a',4000) UNION ALL
SELECT 2, REPLICATE('a',4000) UNION ALL
SELECT 3, REPLICATE('a',4000) 

If we run DBCC IND we can see that table contains 2 data pages (in my case page 78 and 80).

DBCC IND('TestLocks','DemoTable',1)

Let’s run simple select statement.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
SELECT * FROM dbo.DemoTable
GO

I used SQL Profiler to check acquired lock.

We can see that only Intent-Shared (IS) locks at the table and page levels are issued. There are no row-level shared (S) locks.

I was very surprised when I noticed that. I thought that shared locks are always acquired and that optimizer only decides about granularity of locks (row or page level shared lock).

I searched about this lock behavior in SQL Server and found Paul White’s post “The Case of the Missing Shared Locks” where he wrote about locking optimization.

It seems that SQL Server avoids acquiring row-level shared (S) locks when it knows that no data has changed on a page. I didn’t find more information about shared lock optimization. If you know more you can leave a comment.