Read Committed Shared Lock

Question

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

Answer 1

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.

Answer 2

Yes, others will be able to update the rows that the first session "are reading". The reson of that is that the shared lock is only acquired during the actual read operation of that row.

Imagine your rows in the table, let us refer to them rows as row 1 to row 100, and imagine the rows being read in that order by your SELECT (row 1, row 2, row 3, ...). Let us take row 50 as example. After handling/reading row 49:

• SQL server acquires a shared lock on row 50
• SQL Server reads row 50
• SQL Server releases that shared lock on page 50

I.e., before SQL server gets to this row (but after the SELECT was initiated), others can update that row. And immediately after that row was read (but while the SELECT is still in progress), others can modify that row.