sqlsql-server

How to LOCK with READ COMMITTED SNAPSHOT


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?


Solution

  • 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);
    

    enter image description here