BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Transaction Isolation Levels

Permitting concurrent transactions in database management systems is essential for performance but introduces the possibility of reading inconsistent data. To balance concurrency problems and performance, SQL Server has four transaction isolation levels.

Serializable

The most restrictive isolation level is serializable. This adds further locks that prevent rows from being added or removed by other transactions if they would appear in a query that has already been executed in the current transaction. When using this isolation level the database meets the requirements to be described as serializable. All of the concurrency problems are removed, at the cost of decreased concurrency and average performance.

We can demonstrate by trying to create a phantom read. In the first window, set the isolation level, create a new transaction and perform the selection we used earlier:

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM People

Now try using the second SSMS window to insert a new row. As this row is covered by the query executed in the uncommitted, serializable transaction, this insertion is blocked.

-- Transaction 2
INSERT INTO People VALUES ('Tim')

NB: The INSERT is blocked because the row would appear in the query if it was executed again. If the query included a WHERE clause, the insertion would only be blocked if the data matched that WHERE clause.

5 August 2012