Sample Header Ad - 728x90

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