Case of exclusive lock not blocking a share lock

Disregarding any of the higher isolation levels and using the out-of-box read committed isolation level you’d expect an exclusive lock to block a share lock. Here’s a case, admittedly not that common, where blocking does not occur.


-- Create a demo table

CREATE TABLE dbo.t (c int NOT NULL);

INSERT INTO dbo.t VALUES (1);

-- In one session run this

BEGIN TRAN;

UPDATE dbo.t SET c = c;

-- Examine the locks. You'll see the RID X lock

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID

-- In another session run this

SELECT c FROM dbo.t

-- The SELECT is not blocked

This is a contrived example. The column value is being updated to itself but this may happen, for example, in an ETL load where your transaction begins, the daily update value doesn’t change, an update trigger on the table fires that updates other tables. In the transaction time frame you rely on SQL Server’s locking mechanisms to prevent other users reading data while transactions involving it are taking place. As the example shows, for this data scenario your users won’t be blocked.

The real world cases aside, there’s also a SQL Server inconsistency. If the table definition is changed so that a clustered primary key is placed on the column then blocking does occur. You’ll see a granted exclusive key lock from the updating spid and a waiting share key lock from the selecting spid. So the behaviour is dependent on whether the table is a heap or a btree.

Realistically speaking there’s little chance of this being addressed. Nevertheless, the following CONNECT item has been raised:

https://connect.microsoft.com/SQLServer/feedback/details/739700/row-id-rid-exclusive-lock-x-not-blocking-a-select

UPDATE: Martin Smith kindly replied to the CONNECT item. He cited a couple of posts by Paul White that explain the behaviour described in this post:

http://sqlblog.com/blogs/paul_white/archive/2010/11/01/read-committed-shared-locks-and-rollbacks.aspx

http://sqlblog.com/blogs/paul_white/archive/2010/08/11/the_2D00_impact_2D00_of_2D00_update_2D00_statements_2D00_that_2D00_don_2D00_t_2D00_change_2D00_data.aspx

Using fn_dblog for the two scenarios mentioned here show no log entries for the heap case compared to a delete and an insert operation for the btree case. This explains the blocking for the btree case.

Advertisements
  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

%d bloggers like this: