Sql-server – Read Committed Shared Lock

isolation-levellockingsql server

I wanted to know when you are at read committed isolation level and in one session someone started an explicit transaction which has a select statement, say it takes 20 seconds. Can someone from a different session will be able to update the records from that table while the select statement is still executing.

I was discussing this with someone and according to them it will be able to update those records because that's where update locks come into play and gives the exclusive lock to the update while the query is still executing.
I can't find any documentation on this and also don't know how to test this

Best Answer

I can't find any documentation on this

Docs are here. An S lock is compatible with a U lock, but the UPDATE will reuquire an X lock to actually complete, which is incompatible with an S lock.

and also don't know how to test this

You can always run a a query like

select count(*)
from sales.SalesOrderDetail with (tablock), master..spt_values v, master..spt_values v2, master..spt_values v3

And see if you can update from another session. The tablock is to ensure that the whole table is locked with an S lock, as it may or may not be with a long-running SELECT.

You'll discover that unless the database has the READ COMMITTED SNAPSHOT option set, the SELECT will block the UPDATE.

Related Solutions

Sql-server – Shared Lock issued on IsolationLevel.ReadUncommitted

What is HOBT lock?

A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.

Why would I still get a S lock?

This happens on heaps. Example

SET NOCOUNT ON;

DECLARE @Query nvarchar(max) = 
   N'DECLARE @C INT; 
     SELECT @C = COUNT(*) FROM master.dbo.MSreplication_options';

/*Run once so compilation out of the way*/
EXEC(@Query);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;

PRINT 'READ UNCOMMITTED';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
EXEC(@Query);

PRINT 'READ COMMITTED';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
EXEC(@Query);

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

Output READ UNCOMMITTED

Process 56 acquiring Sch-S lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring S lock on HOBT: 1:72057594038910976 [BULK_OPERATION] (class bit0 ref1) result: OK

Process 56 releasing lock on OBJECT: 1:1163151189:0

Output READ COMMITTED

Process 56 acquiring IS lock on OBJECT: 1:1163151189:0  (class bit0 ref1) result: OK

Process 56 acquiring IS lock on PAGE: 1:1:169 (class bit0 ref1) result: OK

Process 56 releasing lock on PAGE: 1:1:169

Process 56 releasing lock on OBJECT: 1:1163151189:0

According to this article referencing Paul Randal the reason for taking this BULK_OPERATION shared HOBT lock is to prevent reading of unformatted pages.

Sql-server – Does SQL Server place shared locks on scanned records when using REPEATABLE READ

What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything about other rows qualifying in a second read.

Best Answer

Related Solutions

Sql-server – Shared Lock issued on IsolationLevel.ReadUncommitted

Sql-server – Does SQL Server place shared locks on scanned records when using REPEATABLE READ

Related Question