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);


-- In one session run this


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


-- 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:

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:

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.

  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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: