Why does the SERIALIZABLE transaction isolation level not guarantee point-in-time views of data?
2
votes
2
answers
1738
views
I have been told that if your transaction consists of multiple statements, then the
SNAPSHOT
transaction isolation level is the only way to guarantee consistent point-in-time views of data. Why is the SERIALIZABLE
transaction isolation level inadequate for this task? By design, the locks that SERIALIZABLE
holds are very tight.
I think the gap in my understanding is that I am unsure when SERIALIZABLE
takes its very tight locks. A script like the below will likely be very helpful in finding what I am missing.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT TOP (1) * FROM [Hit_Me_First];
WAITFOR DELAY '00:02:00';
SELECT TOP (1) * FROM [Hit_Me_Second];
COMMIT;
Asked by J. Mini
(1225 rep)
May 3, 2024, 10:44 PM
Last activity: May 4, 2024, 08:33 AM
Last activity: May 4, 2024, 08:33 AM