Comparison of SQL Server Serializable and Snapshot isolation levels

Problem

In this tip we compare Serializable and Snapshot transaction isolation levels. Both Serializable and Snapshot isolation levels exclude concurrency issues
such as Dirty Reads,
Non-repeatable Reads and Phantoms - which are also called phenomena in a RDBMS. Moreover, none of
the other isolation levels (Read
Uncommitted, Read Committed and Repeatable Read) prevent the mentioned
phenomena.

However, Serializable and Snapshot isolation levels avoid these issues in quite different ways. We will explain the differences between snapshot and serializable
isolation levels and illustrate with examples. It is assumed that you are familiar with the main concepts of database isolation (database engine isolation levels, transaction phenomena and so on).

Solution

Let's see this with an example, so we will first create database TestDB and table TestTable:

SQL Server Serializable Isolation Test

Serializable isolation level provides full data consistency. It means that SELECTs in
a transaction with serializable isolation level can read only committed data (eliminating dirty reads),
in addition issuing the same SELECT statement more than once in the current transaction we will receive the same values for the rows that have already been read in this transaction
(eliminating non-repeatable reads), moreover the number of rows returned by the same SELECT statement will be the same during the transaction (eliminating phantoms).

Serializable isolation
level achieves this consistency by placing a range lock or table level lock during the transaction, so other transactions cannot change data used by this transaction.
The locks are held until the transaction completes.

Now, in TestDB database we start a transaction with Serializable isolation level:

We can see that "Query 2" finishes only after "Query 1" is finished and 'Time T4' which is the data insertion time into TestTable is greater than 'Time T3'.
Therefore "Query 2" waits for the transaction in "Query 1" to finish and only then inserts the data. In other words,
the transaction in "Query 1" locks the table
during the whole transaction (it doesn't release the lock after the first SELECT statement finishes) and releases only when the transaction is finished. After that
"Query 2" is able to insert rows into the table.

We can see in the result set for "Query 3" that "Query 1" places a shared lock on TestTable (the transaction only reads
the data, so the lock is shared, in the case of an insert or update the lock will be exclusive) and "Query 2" waits to place
an Intent Exclusive (IX) lock:

SQL Server Snapshot Isolation Test

As opposed to Serializable isolation level, Snapshot isolation level does not block other transactions
from accessing the rows that are used by the transaction, thus
reducing concurrency problems. It uses row versioning to provide data consistency. It means that when
a row is changed by another transaction, the old
version of that row is stored in the tempdb database and the current transaction sees the modified data as it was when
the transaction started. In other words, the transaction with
Snapshot isolation level can see only changes which have been committed before
the transaction starts and any changes which have been made by other transactions
after the start of the current transaction are invisible to it.

Now we will illustrate the behavior of a transaction with snapshot isolation level.
First we must set ALLOW_SNAPSHOT_ISOLATION option to ON, to be able to use Snapshot isolation level in the database. We execute "Query 4" and while it is
running and before the second SELECT statement will start executing, we execute "Query 5" and after that ( while "Query 4" is still running ) we run "Query 6"
to monitor locks.

As we can see "Query 5" has not been blocked by "Query 4". It successfully inserts the data, however these new rows are not visible for
statements in transaction in "Query 4". Comparing the completion times we can see that data was inserted between SELECT statements
for the transaction, however both SELECT statements return
the same result. Moreover, we can see that there are no locks on data which is used by our transaction:

Now let's see what will happen when we try to update a row in a transaction with Snapshot isolation level which has been already updated by
another transaction,
after the start of the first transaction. We start "Query 8" after "Query 7" starts and while the latter is still in progress:

As a result we receive an error which notes that it is not possible to modify the row which has already been modified by
another
transaction which was committed after the start of the transaction with Snapshot isolation level:

And we can see that the row with ID equal to "5", now becomes "8":

USE TestDB
GO
SELECT *
FROM TestTable

But if we change the order of the SELECT and UPDATE statements in "Query 7" and then try to update a row with
another transaction
we can see that the second transaction waits until the first finishes and then starts the update:

As a result, the updated value is 9, which is updated by the transaction with snapshot isolation level, and the second transaction cannot find the row
equal to 8,
therefore nothing is updated by it:

USE TestDB
GO
SELECT *
FROM TestTable

The result of "Query 11" shows, that locks are placed by the first transaction and the second waits to place
an Update lock.
It is important to understand that in the case of snapshot isolation level, writes do not block reads and reads do not block
writes, but writes block each other.

Test Using the Same SQL Server Isolation Levels for Both Sessions

Now we will illustrate one more difference between Snapshot and Serializable isolation levels. In
the previous examples
the second query which is running while the first one is in progress, uses the default isolation level - Read Committed. Let's see
the transactions
behavior when both transactions use the same isolation level - Snapshot or Serializable.
First, we will update the data using two transactions with Snapshot isolation level and see what happens.

From the result we can that "Query 12" updated the row with ID=1 to ID=2. After that, "Query 13" updated the row with ID=2 to ID=1, but
for this transaction there was only one row with ID=2, because the transaction read from
the snapshot and the row which was updated by "Query 12"
was not visible to this transaction.

In the case of transactions using Serializable isolation level, the result is different.

The result shows that in this case the table was updated only by the first transaction and
a deadlock
occurred. The reason for the deadlock is that both transactions
wait for each other to update the same data.

Conclusion

As we can see Serializable and Snapshot isolation levels protect against all phenomena, however they achieve it by different methods.
We should consider that using Serializable isolation level can cause concurrency problems, because it uses locks to achieve full data consistency.
In case of Snapshot isolation level we avoid more locking and blocking, however, as old versions of rows are stored in tempdb database more storage
is
needed and it increase some overhead.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.