All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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.
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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂