Mějme situaci, kdy chceme uzamknout záznam tak, aby ho jiná transakce nemohla přečíst. Používáme ISOLATION LEVEL READ COMMITTED a kvůli propustnosti nechceme zamykat více záznamů než je nutné. Napadne nás použít hinty ROWLOCK a XLOCK, ale ejhle, ono to nefunguje.

Uvedu příklad. V jednom okně management studia vytvořím tabulku, naplním ji daty, započnu transakci, přečtu jeden záznam tabulky, který uzamknu, aby ho nemohl číst nikdo další. Dále spustím sp_lock a ověřím, že záznam je opravdu exkluzivně zamčený.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
CREATE TABLE tabulka(id INT CONSTRAINT PK_tabulka PRIMARY KEY IDENTITY(1,1), hodnota INT)
INSERT INTO tabulka VALUES(1)
INSERT INTO tabulka VALUES(2)
INSERT INTO tabulka VALUES(3)

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (ROWLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Potom ve druhém okně management studia přečtu z tabulky všechna data, očekávajíc, že to neprojde.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tabulka

Ale ono to prošlo. Server totiž u ISOLATION LEVEL READ COMMITTED na zámky řádků moc nehledí. Všimne si, že záznam nebyl v jiné transakci změněn (je tedy COMMITTED) a klidně ho přečte. Pokud můžeme ovlivnit druhý SELECT, pak je řešením použít u něj hint HOLDLOCK. Pokud můžeme ovlivnit pouze první transakci, pak můžeme použít trik s UPDATE a vnutit tak serveru, že řádek se změnil a nemůže ho z jiné transakce číst.

UPDATE tabulka WITH (ROWLOCK) SET hodnota = hodnota + 0 WHERE id = 3

Pozor, SET hodnota = hodnota nestačí.

Další možností je, použít zamykání na úrovní stránky.

BEGIN TRAN
SELECT *, %%LOCKRES%% AS Resource FROM tabulka WITH (PAGLOCK, XLOCK) WHERE id = 3
EXEC sp_lock

Problém popsán taky v článku The madness of “exclusive” row locks.