Search code examples
sql-serverdatabase

Why are shared locks not visible in case of Select queries?

I have a table with the following schema :

CREATE TABLE [dbo].[Events]
(
    [sequenceNumber] [bigint] IDENTITY(1,1) NOT NULL,
    [event] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

To debug some locking related issues I was trying to understand the locking mechanisms of SQL Server and trying to replicate the situations locally.

But there is one thing I am unable to test and that is the shared locks that the SELECT queries acquire on the rows being read. To test this, I was running the following T-SQL :

BEGIN TRANSACTION;

SELECT TOP (1) 
    sequenceNumber, event 
FROM
    Events 
ORDER BY
    sequenceNumber DESC;

From my understanding, this should have resulted in a S lock on the rows or table? But upon checking both sys.dm_tran_locks and sp_lock, there are no locks visible.

On the other hand if I try to execute a write and in parallel run the read command, then I am able to observe the X and S locks.

-- Write query
BEGIN TRANSACTION;

INSERT INTO Events (event) VALUES (CONVERT(varbinary, 'Two'))

-- Read query run after above write
BEGIN TRANSACTION;

SELECT TOP (1) 
    sequenceNumber, event 
FROM
    Events 
ORDER BY
    sequenceNumber DESC;

Result of sp_lock in this case :

Result of sp_lock

Why are the shared locks not visible with only the SELECT statement?

Does the engine perform the reads without any shared locks in these cases?

Solution

  • I think you're running into a timing issue. Reading the docs for the query hint serializable:

    Equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level.

    (emphasis mine) Which is to say that the locks are obtained, the data is read, and the lock is released faster than you can tab over to another session and run sp_lock.

    To further your exploration, I can see (at least) two options

    • Use the serializable hint (or isolation level) on your query so that the select doesn't release the locks.
    • Use extended events to track locks being acquired and released.

    Of those, I think the XE route is better as you're not changing concurrency semantics which can change the nature and duration of locks. But the serializable route is certainly easier to set up.