SQL Server allows to read rows with exclusive lock

Question

Recently I came across a scenario related to Microsoft SQL Server locking and it looks quite simple. However I have a trouble of understanding the real concept behind one of the scenarios.

I have created a table in SQL Server called locktest with this statement:

CREATE TABLE [dbo].[LockTest]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [age] [int] NULL,

    CONSTRAINT LockTest_PK PRIMARY KEY (id)
);

Then I inserted a couple of records into that table and I ran the below script on that table.

begin tran

update locktest 
set age = 1 
 where id = 3

Then I ran another SQL query to find out the locks generated by above transaction.

SELECT 
    dm_tran_locks.request_session_id,
    dm_tran_locks.resource_database_id,
    DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
    CASE
       WHEN resource_type = 'OBJECT'
           THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
       ELSE OBJECT_NAME(partitions.OBJECT_ID)
    END AS ObjectName,
    partitions.index_id,
    indexes.name AS index_name,
    dm_tran_locks.resource_type,
    dm_tran_locks.resource_description,
    dm_tran_locks.resource_associated_entity_id,
    dm_tran_locks.request_mode,
    dm_tran_locks.request_status
FROM 
    sys.dm_tran_locks
LEFT JOIN 
    sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN 
    sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID 
                AND indexes.index_id = partitions.index_id
WHERE 
    resource_associated_entity_id > 0
    AND resource_database_id = DB_ID()
ORDER BY 
    request_session_id, resource_associated_entity_id

And there are 3 locks generated by this transaction.

And there you can see an exclusive (X) lock has been obtained for row to be updated. Now theoretically no other operations can read this row as shared locks are not compatible with exclusive locks. But when I ran a select query as below to query the same row, it immediately return the result even the above transaction has not been committed or rolled back.

begin tran

    select * 
    from LockTest

rollback tran

Why does SQL Server allow reading the row with an exclusive Lock? Appreciate your help

Thanks, Keth

score 1 · Answer 1 · answered Feb 26 '18 at 15:16

1

The exclusive lock is on an index. If this is nonclustered, your SELECT statement is likely not using it. There will only be an exclusive lock on the table if the update is changing the value. If the new and old values are the same, no lock is made.

answered Feb 26 '18 at 15:16

cloudsafe

2,444
1
8
24

thanks for the explanation. Can you explain the theory behind your explanation? – keth Feb 26 '18 at 18:05
1

If all rows for columnA are of value 1, Update t set columnA=1 will not hold an exclusive lock under an implicit transaction. – cloudsafe Feb 27 '18 at 10:28
this is the exact same scenario. I was trying to update the column with the same existing value. However it happened only the first time i update the initial value to a different value. Later i tried to update the column with same value, but it always acquired a shared lock. Any thoughts on this? – keth Feb 27 '18 at 12:22

score 0 · Answer 2 · answered Feb 26 '18 at 10:46

0

Check Options -> Query Execution -> SQL Server -> Advanced - SET TRANSACTION ISOLATION LEVEL. It might not be set to READ COMMITTED.

answered Feb 26 '18 at 10:46

cloudsafe

2,444
1
8
24

i just checked the configuration it is set to READ COMMITTED – keth Feb 26 '18 at 13:37

SQL Server allows to read rows with exclusive lock

2 Answers2