Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server Transaction Isolation Level - SERIALIZABLE

Aug 30 2008 3:39PM by Jacob Sebastian   

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

Tags: SERIALIZABLE, REPEATABLE READ, READ UNCOMMITTED, TRANSACTION ISOLATION, READ COMMITTED, TRANSACTION,


Jacob Sebastian
1 · 100% · 32220
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]