CODEWITHSUNDEEP
sql-serverlockingtransaction-isolation
keth
keth

Reputation: 825

SQL Server allows to read rows with exclusive lock

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.

enter image description here

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

Upvotes: 2

Views: 2000

Answers (2)

cloudsafe
cloudsafe

Reputation: 2506

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.

Upvotes: 1

cloudsafe
cloudsafe

Reputation: 2506

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

Upvotes: 0

Related Questions