Comparison of SQL Server Serializable and Snapshot isolation levels

By:   |   Comments (1)   |   Related: > TSQL


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:

USE master
GO
	
CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE TestTable
(
	ID INT,
	DateChanged DATETIME DEFAULT GETDATE()
)

INSERT INTO TestTable(ID) 
VALUES (1),(2),(3)

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: 

--Query 1
USE TestDB
GO

SELECT @@SPID AS ProcessID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

	SELECT * FROM TestTable
	SELECT GETDATE() AS 'Time T1'

	WAITFOR DELAY '00:00:10'

	SELECT * FROM TestTable
	SELECT GETDATE() AS 'Time T3'

ROLLBACK

While the transaction is running, we open a new query window and run "Query 2": 

--Query 2
--Execute while "Query 1" is running
USE TestDB
GO

SELECT @@SPID AS ProcessID

INSERT INTO TestTable(ID) 
VALUES (4),(5)

SELECT ID, DateChanged AS  'Time T4' 
FROM TestTable
WHERE ID IN (4,5)

While "Query 1" and "Query 2" are still running, we run "Query 3" in a new query window to monitor locks:

--Query 3
--Execute while "Query 1" is running

USE TestDB
GO

SELECT OBJECT_ID('TestTable') AS TestTableID
GO

sp_lock

SELECT GETDATE() AS 'Time T2'
GO

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:

USE TestDB

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.

--Query 4
USE master
GO

ALTER DATABASE TestDB  SET ALLOW_SNAPSHOT_ISOLATION ON
USE TestDB
GO

SELECT @@SPID AS ProcessID

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

	SELECT * FROM TestTable
	SELECT GETDATE() AS 'T1'

	WAITFOR DELAY '00:00:10'

	SELECT * FROM TestTable
	SELECT GETDATE() AS 'T3'

ROLLBACK

Inserting the data while "Query 4" is running:

--Query 5
--Execute while "Query 4" is running
USE TestDB
GO

SELECT @@SPID AS ProcessID

INSERT INTO TestTable(ID) 
VALUES (6),(7)

SELECT ID, DateChanged AS  'Time T4' 
FROM TestTable
WHERE ID IN (6,7)

Monitoring locks: 

--Query 6
--Execute while "Query 4" is running
USE TestDB
GO

SELECT OBJECT_ID('TestTable') AS TestTableID
GO

sp_lock

SELECT GETDATE() AS 'Time T2'
GO

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:

Monitoring locks

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: 

--Query 7
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

	SELECT * FROM TestTable
	SELECT GETDATE() AS 'T1'

	WAITFOR DELAY '00:00:10'

	UPDATE TestTable
	SET ID=9
	WHERE ID=5

	SELECT GETDATE() AS 'T3'

COMMIT
	

--Query 8
--Execute while "Query 7" is running
USE TestDB
GO

UPDATE TestTable
SET ID=8
WHERE ID=5

SELECT GETDATE() AS 'Time T2'

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:

SELECT GETDATE

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

USE TestDB
GO

SELECT *
FROM TestTable

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:

--Query 9
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

	SELECT @@SPID AS ProcessID

	UPDATE TestTable
	SET ID=9
	WHERE ID=8

	SELECT GETDATE() AS 'T1'

	WAITFOR DELAY '00:00:10'

	SELECT * FROM TestTable
	
	SELECT GETDATE() AS 'T3'

COMMIT	

--Query 10
--Execute while "Query 9" is running
USE TestDB
GO

SELECT @@SPID ProcessID

UPDATE TestTable
SET ID=10
WHERE ID=8

SELECT GETDATE() AS 'Time T2'	

--Query 11
--Execute while "Query 9" is running
USE TestDB
GO

SELECT OBJECT_ID('TestTable') AS TestTableID
GO

sp_lock

SELECT GETDATE() AS 'Time T2'
GO

SELECT_OBJECT_ID

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

TRUNCATE_TABLE

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.

Let's truncate TestTable and insert two rows:

USE TestDB
GO

TRUNCATE TABLE TestTable

INSERT INTO TestTable(ID) VALUES (1), (2)

We run the first transaction with Snapshot isolation level and while it is in progress, we start the second also using Snapshot isolation level:

--Query 12
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRANSACTION

	UPDATE TestTable
	SET ID= 2, DateChanged=GETDATE()
	WHERE ID=1

	SELECT GETDATE() AS 'Time T1'

	WAITFOR DELAY '00:00:10'

	SELECT GETDATE() AS 'Time T3'

COMMIT

SELECT *
FROM TestTable

--Query 13
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION

	UPDATE TestTable 
	SET ID = 1, DateChanged=GETDATE()
	WHERE ID= 2

	SELECT GETDATE() AS 'Time T2'

COMMIT

ISOLATION_LEVEL

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. 

USE TestDB
GO

TRUNCATE TABLE TestTable

INSERT INTO TestTable(ID) VALUES (1), (2)

We run "Query 14":

--Query 14
USE TestDB
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

	UPDATE TestTable
	SET ID= 2, DateChanged=GETDATE()
	WHERE ID=1

	SELECT GETDATE() AS 'Time T1'

	WAITFOR DELAY '00:00:10'

	SELECT GETDATE() AS 'Time T3'

COMMIT

SELECT *
FROM TestTable

While "Query 14" is in progress, we run "Query 15"

--Query 15
USE TestDB
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

	UPDATE TestTable 
	SET ID = 1, DateChanged=GETDATE()
	WHERE ID= 2

	SELECT GETDATE() AS 'Time T2'

COMMIT

deadlock_occurrence

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, December 28, 2015 - 12:08:46 AM - Subrat Back To Top (40313)

Hi

This article is a very nice and simple description with Example.

 

Regards,

Subrat














get free sql tips
agree to terms