I could not figure out how to explicitly lock a table from reading in READ_COMMITTED_SNAPSHOT mode?
Based on my knowledge:
when READ_COMMITTED_SNAPSHOT
is OFF
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Locks the table from reading (Fine)
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);
-- Do other stuff in backend
-- Locks the table from reading (Fine)
update Orders set Reference = 'ORD-001';
-- Table is already locked, so no worry
update IncrementalNumbers set LastOrderNumber = 1;
COMMIT
when READ_COMMITTED_SNAPSHOT
is ON
BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Does not lock the table from reading (even if WITH (TABLOCKX) specified) (Wrong)
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);
-- Do other stuff in backend
-- Does not lock the table from reading (Fine)
update Orders set Reference = 'ORD-001';
-- Should be locked in the select above, but it's not
update IncrementalNumbers set LastOrderNumber = 1;
COMMIT
The question is how to use READ_COMMITTED_SNAPSHOT mode and at the same time in some situations be able to lock the table from reading? Eg. to avoid incremental numbers (eg. [LastOrderNumber]
) being read and then overridden. Is it possible?
Thanks to David Browne - https://stackoverflow.com/a/78653068/1143349 tip, I realized what the answer is.
It does not lock [IncrementalNumbers]
from reading. But it does from locking.
So the second example is correct as soon as others would need to always try to lock the [IncrementalNumbers] table on reading.
So everyone who wants to read the LastOrderNumber
for modification (not just for display purpose) would need to always read with lock:
select LastOrderNumber from IncrementalNumbers WITH (TABLOCKX);