Understanding transaction isolation levels
I have been reading up about transaction isolation levels in SQL Server.
I understand the differences between READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, but the waters became muddy when I tried to understand SNAPSHOT.
How is the SNAPSHOT isolation level different from READ COMMITTED and REPEATABLE READ ?
I understand the differences between READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, but the waters became muddy when I tried to understand SNAPSHOT.
How is the SNAPSHOT isolation level different from READ COMMITTED and REPEATABLE READ ?
ASKER
Hi Scott. The thought arises, why have a SNAPSHOT at all since it does not take any locks ?
Also the difference between RC and RR, as you explain, is that RC blocks on a row only, so if you issue a RR then that will ensure that no changes take place until such time as the transaction is committed.
But how is that different from SERIALIZABLE ?
Also the difference between RC and RR, as you explain, is that RC blocks on a row only, so if you issue a RR then that will ensure that no changes take place until such time as the transaction is committed.
But how is that different from SERIALIZABLE ?
SNAPSHOT will never receive a dirty read, unlike RU / "WITH (NOLOCK)". Also, SNAPSHOT gives a consistent point-in-time as of when it starts. If your trans starts at 9:03:48AM, you will not see any UPDATEs from after that time, which is not necessarily true with either RU or RC.
SERIALIZABLE also holds locks until end of trans, like RR, and for the same reason: to prevent non-repeatable reads. But SER must also prevent phantom reads/rereads. It does this by creating and holding key-range locks (*) to prevent rows from being inserted into the range of data being read.
For example, say your SER query has:
WHERE key BETWEEN 1 AND 10
SQL would not only read and hold locks on the actual rows read, it would also issue a key-range lock for key values between 1 and 10 so that no new rows could be inserted -- or old rows deleted -- in that range before your transaction ends.
(*) Page or table locks might be used instead of key-range locks to lock that certain range of key values. SQL will use whatever physical mechanism it deems best at the time to insure no phantom reads.
SERIALIZABLE also holds locks until end of trans, like RR, and for the same reason: to prevent non-repeatable reads. But SER must also prevent phantom reads/rereads. It does this by creating and holding key-range locks (*) to prevent rows from being inserted into the range of data being read.
For example, say your SER query has:
WHERE key BETWEEN 1 AND 10
SQL would not only read and hold locks on the actual rows read, it would also issue a key-range lock for key values between 1 and 10 so that no new rows could be inserted -- or old rows deleted -- in that range before your transaction ends.
(*) Page or table locks might be used instead of key-range locks to lock that certain range of key values. SQL will use whatever physical mechanism it deems best at the time to insure no phantom reads.
ASKER
When you express with the term "prevent non-repeatable reads" it is a bit confusing because it looks like a double negative, so could you not also say "allow repeatable reads" and achieve the same meaning ?
If SER prevents phantom reads/rereads, then can I assume that RR does not ?
Also how does a phantom read occur ?
If SER prevents phantom reads/rereads, then can I assume that RR does not ?
Also how does a phantom read occur ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I understand. Thanks Scott !
RC = reads take a shared lock, but release the lock as soon as the query is done processing that row
RR = reads take shared locks and keep them until the end of the transaction
What this means in practical terms:
SI reads will never block data writers. SI is, thus, effectively a safe RU, since it won't get dirty reads. But it has additional overhead, so it's not a "free" way to avoid dirty reads (which I've sometimes seen it called).
RC reads could block writers, but typically only for a short period of time, until processing moves to the next row. Since shared locks are released immediately after each row, if you re-read the same row later in the same transaction, the column value(s) could be different than the first time (i.e. "non-repeatable" read).
RR reads could extensively block writers and are also more likely to lead to deadlocks. But you can never get not-repeatable reads, since the row is guaranteed to be unchanged during the trans (because of the held shared lock).