THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Read Committed isolation level, indexed views and locking behavior

From BOL, "Key-Range Locking" article:

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction. (bold is mine - MZ)


I had a blocking locks problems recently. What should have been working concurrently from multiple threads actually worked single-threaded only. Here is demo - table of employees and indexed view above it that shows number of employees and total salary per department. I populated it randomly with up to 10 deparments.


USE [tempdb]
GO

SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET
NUMERIC_ROUNDABORT OFF
GO

CREATE TABLE dbo.Employees
(
  
EmployeeID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  
DepartmentID SMALLINT NOT NULL,
  
Salary MONEY NOT NULL
)
GO

CREATE VIEW dbo.V_Departments
WITH SCHEMABINDING
AS
   SELECT
      
DepartmentID,
      
SUM(Salary) AS DepartmentSalary,
      
COUNT_BIG(*) AS TotalEmployees
  
FROM dbo.Employees
  
GROUP BY DepartmentID
GO

CREATE UNIQUE CLUSTERED INDEX PK_V_Departments ON dbo.V_Departments( DepartmentID ASC )
GO

INSERT INTO dbo.Employees( DepartmentID, Salary )
SELECT
  
[object_id] % 10 + 1 AS DepartmentID,
  
[object_id] % 3000 + 1 AS Salary
FROM sys.objects
GO

Now I open 2 concurrent transactions and try to add new employee from both. The important thing here is that both belong to new departments: 11 and 13 (in the demo data we had departments from 1 to 10).


Session 1 Session 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

INSERT INTO
dbo.Employees( DepartmentID, Salary )
VALUES( 11, 2500 )
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

BEGIN TRAN

INSERT INTO
dbo.Employees( DepartmentID, Salary )
VALUES( 13, 3500 )

Second transaction is locked. Why? We don't have anything else that updates department 12, so why it is locked? Let's find out


SELECT
  
OBJECT_NAME(i.[object_id]) AS ObjectName,
  
i.name AS IndexName,
  
l.request_mode AS LockRequestMode,
  
l.resource_type AS LockResourceType,
  
l.resource_description AS LockResourceDescription
FROM
  
sys.dm_tran_locks AS l
  
INNER JOIN sys.dm_os_waiting_tasks AS wt ON l.lock_owner_address = wt.resource_address
  
INNER JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
  
INNER JOIN sys.indexes AS i ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
WHERE wt.session_id = 57
AND l.request_status = 'WAIT'


image


RangeS-U mode means shared lock for the range between values and update lock for values themselves. One value is obvious - department 11 that we've inserted in the first session. Second value, as we see in the query result, is (ffffffffffff) which indicates max int. Storage Engine knows to lock rows and other objects that are scanned by Query Processor. But we don't have row with department greater than 11. So right boundary of locked range is set to maximum possible value.

So we understand what range is locked. But why? If we work in pure read committed isolation level, we hold exclusive lock on rows we modify but nobody prevents another user from inserting or deleting another row with same department value. But we expect that our operation would update corresponding department row in the view before anybody else updates same row (e.g. if we read data after our transacion is complete, it won't be affected by other transactions). Which is classic serializable behavior. So instead of forcing customer to require serializable isolation level expicitely, SQL Server implicitely converted read-committed to serializable for this particular operation. It appears that same scenario occurs with cascading updates on foreign keys.

Published Tuesday, March 15, 2011 6:31 PM by Michael Zilberstein
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement