In the previous posts we have examined READ COMMITTED, READ UNCOMMITTED and REPEATABLE READ transaction isolation levels. We will focus on SERIALIZABLE isolation level, in this post.

SERIALIZABLE is very close to REPEATABLE READ isolation level, except that it prevents phantom rows. The principal difference between SERIALIZABLE and REPEATABLE READ is that SERIALIZABLE applies a range lock so that you cannot insert new rows within the range locked by the transaction. This behavior prevents Phantom rows.

Let us look at an example to understand this. Let us create the table needed for the examples and fill some sample data.

---------------------------------------------------------------------
-- Create tables
---------------------------------------------------------------------
IF OBJECT_ID('Consultants') IS NOT NULL DROP TABLE Consultants
GO

CREATE TABLE Consultants (
    ConsultantID INT IDENTITY,
    ConsultantName VARCHAR(20),
    Technology VARCHAR(15),
    HireDate DATETIME, 
    HourlyRate MONEY)

---------------------------------------------------------------------
-- Fill some data
---------------------------------------------------------------------
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Jacob', 'SQL Server', '2000-10-03', 120
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Smith', 'ASP.NET', '2005-01-01', 140    
INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Michael', 'C#.NET', '2002-10-30', 100


SELECT * FROM Consultants
/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
2            Smith                ASP.NET         2005-01-01 00:00:00.000 140000.00
3            Michael              C#.NET          2002-10-30 00:00:00.000 80000.00
*/

Let us once again revisit how a phantom row can be created with REPEATABLE READ isolation. Then let us see how SERIALIZABLE prevents it. Run the following code in Query Window 1.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * 
FROM Consultants
WHERE Technology = 'SQL server'

/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
*/

Now open Query Window 2 and run the following.

INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Mike', 'SQL Server', '2000-10-03', 120

Now switch back to Query Window 1 and run the same query again.

SELECT * 
FROM Consultants
WHERE Technology = 'SQL server'
/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
4            Mike                 SQL Server      2000-10-03 00:00:00.000 120.00
*/

COMMIT TRAN

Note that the result has a new row that was not present when we ran the query at the first attempt. When we ran it for the second time, it retrieved a phantom row.

Now let us see what happens with SERIALIZABLE isolation level. Rerun the scripts to create the sample table and to fill sample data. Open a new query window (Query Window 1) and run the following.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * 
FROM Consultants
WHERE Technology = 'SQL server'

/*
ConsultantID ConsultantName       Technology      HireDate                HourlyRate
------------ -------------------- --------------- ----------------------- ---------------------
1            Jacob                SQL Server      2000-10-03 00:00:00.000 120.00
*/

Open another query window (Query Window 2) and try to insert a new record of a consultant having expertise in SQL Server.

INSERT INTO Consultants (ConsultantName, Technology, HireDate, HourlyRate)
    SELECT 'Mike', 'SQL Server', '2000-10-03', 120

Note that this query blocks. The range lock acquired by the transaction in the other session will block this query until the other transaction commits or rolls back.

SERIALIZABLE isolation level gives the highest level of isolation and should be used very carefully. If not used carefully, you might end up with unexpected blocking behavior and even dead locks.

See also

1. Introduction to SQL Server Transaction Isolation Levels
2. SQL Server Transaction Isolation Level - READ COMMITTED
3. SQL Server Transaction Isolation Level - READ UNCOMMITTED
4. SQL Server Transaction Isolation Level - REPEATABLE READ
5. SQL Server Transaction Isolation Level - SERIALIZABLE
6. SQL Server Transaction Isolation Level - SNAPSHOT