New answers tagged locking
0
If you add an OUTPUT clause to your UPDATE statement you could do all the work using one statement thus eliminating race conditions
Available on SQL Server 2005 and later
https://www.sqlservercentral.com/articles/the-output-clause-for-update-statements
0
Based on trancount="2" in the XML, it looks like these statements are being run as part of an explicit transaction1. I would check to make sure that previous statements in the same transaction aren't holding these unexpected locks.
Also, confirm there are no triggers running on this table that could be taking more locks.
Based on the information ...
-2
In order to avoid the above mentioned error, you can use the LOCK command to explicitly lock in that transaction.
0
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, .....
3
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.....
2
In most cases, locks don't cause waits.
Imagine I have a simple banking system. In order to transfer money from account A to account B, I start a transaction and acquire a row-level lock on both account records. That lets me run my validations (i.e. A has enough money for the transfer, the fraud department doesn't have a flag on either account, etc.), ...
Top 50 recent answers are included
