Note that first four Isolation Levels described below are
ordered from lowest to highest. The two subsequent levels are new to SQL Server
2005, and are described separately.
Read Uncommitted Isolation Level
Read Uncommitted Isolation Level
This is the lowest level and can be set, so that it provides
higher concurrency but introduces all concurrency problems; dirty-reads, Lost
updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads. This
Isolation Level can be simply tested.
Connection1 opens a transaction and starts updating
Employees table.
USE Northwind
BEGIN TRAN
-- update the HireDate from 5/1/1992 to 5/2/1992
UPDATE dbo.Employees
SET HireDate = '5/2/1992'
WHERE EmployeeID = 1
Connection2 tries to read same record.
USE Northwind
SELECT HireDate
FROM dbo.Employees
WHERE EmployeeID = 1
You will see that Connection2 cannot read data because an exclusive lock has been set for the resource by Connection1. The exclusive locks are not compatible with other locks. Though this reduces the concurrency, as you see, it eliminates the data inaccuracy by not allowing seeing uncommitted data for others. Now let’s set the Isolation Level of Connection2 to Read Uncommitted and see.
USE Northwind
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT HireDate
FROM dbo.Employees
WHERE EmployeeID = 1
-- results HireDate
as 5/2/1992
As you expected, Connection2 can see the record that is being modified by Connection1 which is an uncommitted record. This is called dirty-reading. You can expect higher level of concurrency by setting the Isolation Level to Read Uncommitted but you may face all concurrency related problems. Imagine the consequences when Connection1 rolls back the transaction but Connection2 makes a decision from the result before the roll back.
Read Committed Isolation Level
This is the default Isolation Level of SQL Server. This
eliminates dirty-reads but all other concurrency related problems. You have
already seen this. Look at the sample used above. Connection2 could not read
data before the Isolation Level was set to Read Uncommitted. That is because it
had been set to the default Isolation Level which is Read Committed which in
turn disallowed reading uncommitted data. Though it stops dirty-reads, it may
introduce others. Let’s take a simple example that shows Lost Updates.
Employee table contains data related to employee. New
employee joins and record is made in the table.
USE Northwind
INSERT INTO dbo.Employees
(LastName, FirstName, Title,
TitleOfCourtesy, BirthDate, HireDate)
VALUES
('Lewis', 'Jane', 'Sales Representative', 'Ms.', '03/04/1979', '06/23/2007')
This table contains a column called Notes that describes the
employee’s education background. Data entry operators fill this column by
looking at her/his file. Assume that the update code has been written as below.
Note that no Isolation Level has been set, means default is set.
IF OBJECT_ID(N'dbo.UpdateNotes', N'P') IS NOT NULL
BEGIN
DROP PROC dbo.UpdateNotes
END
GO
CREATE PROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext
AS
BEGIN
DECLARE @IsUpdated bit
BEGIN TRAN
SELECT @IsUpdated = CASE WHEN Notes IS NULL THEN 0 ELSE 1 END
FROM dbo.Employees
WHERE EmployeeID =
@EmployeeID -- new record
-- The below statement added to hold the transaction for 5
seconds
-- Consider it is as a different process that do something
else.
WAITFOR DELAY '00:00:5'
IF (@IsUpdated = 0)
BEGIN
UPDATE
dbo.Employees
SET
Notes = @Notes
WHERE
EmployeeID = @EmployeeID
END
ELSE
BEGIN
ROLLBACK
TRAN
RAISERROR
('Note has been alreasy
updated!', 16,
1)
RETURN
END
COMMIT TRAN
END
Operator1 makes Connection1 and executes the following
query.
EXEC dbo.UpdateNotes 15, 'Jane has a BA degree in
English from the University
of Washington
Within few seconds (in this case, right after Operator1
started) Operator2 makes Connection2 and executes the same with a different
note, before completing the Operator1’s process.
EXEC dbo.UpdateNotes 15, 'Jane holds a BA degree in
English.'
If you query the record after both processes, you will see
that note that was entered by the Operator2 has been set for the record. Operator1 made the update and no error
messages were returned to it, but it has lost its update. This could be avoided
if the record was locked and held as soon as it was identified as a not updated
record. But obtaining and holding a lock is not possible with Read Committed
Isolation Level. Because of this, concurrency related problems such as Lost
Updates, Nonrepeatable reads and Phantom reads can happen with this Isolation
Level.
Repeatable Read Isolation Level
This Isolation Level addresses all concurrency related problems
except Phantom reads. Unlike Read Committed, it does not release the shared
lock once the record is read. It obtains the shared lock for reading and keeps
till the transaction is over. This stops other transactions accessing the
resource, avoiding Lost Updates and Nonrepeatable reads. Change the Isolation
Level of the stored procedure we used for Read Committed sample.
IF OBJECT_ID(N'dbo.UpdateNotes', N'P') IS NOT NULL
BEGIN
DROP PROC dbo.UpdateNotes
END
GO
CREATE PROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext
AS
BEGIN
DECLARE @IsUpdated bit
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT @IsUpdated = CASE WHEN Notes IS NULL THEN 0 ELSE 1 END
FROM dbo.Employees
WHERE EmployeeID =
@EmployeeID -- new record
Now make two connections and execute below queries just as
you did with Read Committed sample. Make sure you set the Note column value
back to NULL before executing them.
With Connection1;
EXEC dbo.UpdateNotes 15, 'Jane has a BA degree in
English from the University
of Washington
With Connection2;
EXEC dbo.UpdateNotes 15, 'Jane holds a BA degree in
English.'
Once you execute the code with Connection2, SQL Server will
throw 1205 error and Connection2 will be a deadlock victim. This is because,
Connection1 obtain and hold the lock on the resource until the transaction
completes, stopping accessing the resource by others, avoiding Lost Updates.
Note that setting DEADLOCK_PRIORITY to HIGH, you can choose the deadlock
victim.
Since the lock is held until the transaction completes, it
avoids Nonrepeatable Reads too. See the code below.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT Notes
FROM dbo.Employees
WHERE EmployeeID = 10
It reads a record from the Employees table. The set
Isolation Level guarantees the same result for the query anywhere in the
transaction because it holds the lock without releasing, avoiding modification
from others. It guarantees consistency of the information and no Nonrepeatable
reads.
Now let’s take another simple example. In this case, we add
one new table called Allowances and one new column to Employees table called
IsBirthdayAllowanceGiven. The code for changes are as below;
USE Northwind
GO
-- table holds
allowances
CREATE TABLE Allowances (EmployeeID int, MonthAndYear datetime, Allowance money)
GO
-- additional
column that tells whether the birthday allowance is given or not
ALTER TABLE dbo.Employees
ADD IsBirthdayAllowanceGiven bit
DEFAULT(0) NOT NULL
GO
Assume that company pays an additional allowance for
employees whose birth date fall on current month. The below stored procedure
inserts allowances for employees whose birth date fall on current month and
update employees record. Note that WAITFOR DELAY has been added hold the
transaction for few seconds in order to see the problem related to it. And no
Isolation Level has been set, default applies.
IF OBJECT_ID(N'dbo.AddBirthdayAllowance', N'P') IS NOT NULL
BEGIN
DROP PROC dbo.AddBirthdayAllowance
END
GO
CREATE PROC dbo.AddBirthdayAllowance
AS
BEGIN
BEGIN TRAN
-- inserts records to allowances table
INSERT INTO
Allowances
(EmployeeID, MonthAndYear,
Allowance)
SELECT EmployeeID, getdate(), 100.00
FROM dbo.Employees
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
-- hold the transaction for 5 seconds
-- Consider this is as some other process that takes 5
seconds
WAITFOR DELAY '00:00:05'
-- update IsBirthdayAllowanceGiven column in Employees
table
UPDATE dbo.Employees
SET
IsBirthdayAllowanceGiven = 1
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
COMMIT TRAN
END
Before running any queries, make sure at least one
employee’s birth date falls on current month. Now open a new connection (let’s
name it as Connection1) and run the stored procedure. In my Northwind database,
I have one record that stratifies the criteria; EmployeeId 6: Michael Suyama.
USE Northwind
GO
EXEC dbo.AddBirthdayAllowance
Immediately, open Connection2 and insert a new employee
whose birth date falls into current month.
USE Northwind
GO
INSERT INTO dbo.Employees
(LastName, FirstName, Title,
TitleOfCourtesy, BirthDate, HireDate)
VALUES
('Creg', 'Alan', 'Sales Representative', 'Ms.', '07/13/1980', '07/20/2007')
Go back to Connection2. Once the transaction completed,
query the Allowances table and see. You will see a one record that is generated
for Michael. Then open the Employees table and see that how many records have
been updated. It has updated two, not only Michael but Alan. Note that no
record has been inserted to the Allowances table for Alan. In this case, the
new record is considered as a Phantom record and read of the new record called
as Phantom Read. This cannot be avoided with default Isolation Level that is
Read Committed. Change the stored procedure and set the Isolation Level as
Repeatable Read.
IF OBJECT_ID(N'dbo.AddBirthdayAllowance', N'P') IS NOT NULL
BEGIN
DROP PROC dbo.AddBirthdayAllowance
END
GO
CREATE PROC dbo.AddBirthdayAllowance
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
-- inserts records to allowances table
INSERT INTO
Allowances
(EmployeeID, MonthAndYear, Allowance)
SELECT EmployeeID, getdate(), 100.00
FROM dbo.Employees
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
Now bring the Employees table to original state.
UPDATE dbo.Employees
SET
IsBirthdayAllowanceGiven = 0
DELETE dbo.Employees
WHERE FirstName = 'Alan'
DELETE dbo.Allowances
Open two connections again and try the same. Check the
result. Still the Phantom Reads problem exists. In order to avoid this problem,
you need to use highest Isolation Level that is Serializable.
Serializable Isolation Level
This is the highest Isolation Level and it avoids all the
concurrency related problems. The behavior of this level is just like the
Repeatable Read with one additional feature. It obtains key range locks based
on the filters that have been used. It locks not only current records that
stratify the filter but new records fall into same filter. Change the stored
procedure we used for above sample and set the Isolation Level as Serializable.
IF OBJECT_ID(N'dbo.AddBirthdayAllowance', N'P') IS NOT NULL
BEGIN
DROP PROC dbo.AddBirthdayAllowance
END
GO
CREATE PROC dbo.AddBirthdayAllowance
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
-- inserts records to allowances table
INSERT INTO
Allowances
(EmployeeID, MonthAndYear,
Allowance)
SELECT EmployeeID, getdate(), 100.00
FROM dbo.Employees
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
Run the clean up code again to bring the Employees table to
the original state.
Now test the stored procedure and INSERT statement with two
connections. You will notice that INSERT operation is blocked until Connection1
completes the transaction, avoiding Phantom Reads.
Run the clean up code again and drop the new table
Allowances and added column IsBirthdayAllowanceGiven in the Employees table.
Whenever we set the Isolation Level to a transaction, SQL
Server makes sure that the transaction is not disturbed by other transactions.
This is called concurrency control. All the Isolation Levels we discussed so
far come under a control called Pessimistic Control. The Pessimistic control,
SQL Server locks the resource until user performs the action she/he needs and
then release for others. The other concurrency control is Optimistic Control.
Under Optimistic Control, SQL Server does not hold locks but once read, check
for inconsistency for next read. The two newly introduced Isolation Levels with
SQL Server 2005 are Snapshot and Read Committed Snapshot. These two Isolation
Levels provide Optimistic Control and they use Row Versioning.
Snapshot Isolation Level
The Snapshot Isolation Level works with Row Versioning
technology. Whenever the transaction requires a modification for a record, SQL
Server first stores the consistence version of the record in the tempdb. If
another transaction that runs under Snapshot Isolation Level requires the same record,
it can be taken from the version store. This Isolation Level prevents all
concurrency related problems just like Serializable Isolation Level, in
addition to that it allows multiple updates for same resource by different
transactions concurrently.
Since there is a performance impact with Snapshot Isolation
Level it has been turned off by default. The impact is explained below with the
sample. You can enable it by altering the database.
ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION
ON
Let’s look at a simple sample. Make sure you have enabled
Snapshot Isolation Level in the database before running below query. Open a new
connection (Connection1) and execute query below;
USE Northwind
BEGIN TRAN
-- update the HireDate from 5/1/1992 to 5/2/1992
UPDATE dbo.Employees
SET HireDate = '5/2/1992'
WHERE EmployeeID = 1
Now open the second connection (Connection2) and try to
retrieve the same record.
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
As you have seen with examples discussed under other levels,
the record cannot be retrieved. Since we have enabled Snapshot Isolation Level
in the database, SQL Server stores version of the record. Use below dynamic
management view for retrieving versions stored in the store.
SELECT * FROM sys.dm_tran_version_store;
You will see one record in the store. Now set the Isolation
Level of the Connection2 as Snapshot and try to retrieve the record.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
This returns record from the store that was the last
consistence version of the record. Note that HireDate of the employee is
05/01/1992 not 05/02/1992. Now go back to the Connection1 and commit the transaction.
COMMIT TRAN
Again open the Connection2 and execute the query. Note that
even though the Connection1 has committed the change, Connection2 still gets
the older record. This is because it was the consistence record in the version
store when the Connection2 started the transaction and the same version is read
during the transaction. SQL Server keeps this version of the record until no
reference for it. If another transaction starts changing same record, another
version will be stored and goes on; results longer link list in the version
store. Maintaining longer link list and traversing through list will impact the
performance. Committing the transaction in Connection2 will remove the
reference for the first version and the first version in the store will be
removed from separate clean-up process.
There is another great feature with Snapshot Isolation
Level. It is Conflict Detection. One transaction reads a record from the
version store and later tries to update the record. Another transaction updates the same record
before previous transaction’s update. This conflict detects by the SQL Server
and aborts the previous transaction.
Open a connection (Connection1) and run the below query. The
update statement causes to add the current consistence version to the version
store.
USE Northwind
BEGIN TRAN
-- update the HireDate from 5/1/1992 to 5/2/1992
UPDATE dbo.Employees
SET HireDate = '5/2/1992'
WHERE EmployeeID = 1
Open the second connection (Connection2) and read the same
record. Note the Isolation Level.
USE Northwind
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRAN
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
Go back to Connection1 and commit the transaction.
COMMIT TRAN
Go back to Connection2 and try to update the record. Note
that the current transaction still runs. Whenever you execute the UPDATE
statement, SQL Server detects the modification that has been done by
Connection1 in between read and write, it throws an error.
UPDATE dbo.Employees
SET HireDate = '5/3/1992'
WHERE EmployeeID = 1
Snapshot isolation
transaction aborted due to update conflict. You cannot use snapshot isolation
to access table 'dbo.Employees' directly or
indirectly in database 'Northwind' to update,
delete, or insert the row that has been modified or deleted by another
transaction. Retry the transaction or change the isolation level for the
update/delete statement.
Once the conflict is detected, it terminates the transaction
in Connection2. Though this Isolation Level has some great advantageous, this
level is not recommended for a database that has many updates. This is suitable
for database that is mainly used for read data with occasional updates.
Read Committed Snapshot
Isolation Level
This is the new implementation of the Read Committed
Isolation Level. It has to be set not at session/connection level but database
level. The only different between Read Committed and Read Committed Snapshot
is, Read Committed Snapshot is Optimistic whereas Read Committed is
Pessimistic. The Read Committed Snapshot differs from Snapshot in two ways;
Unlike Snapshot, it always returns latest consistence version and no conflict
detection.
Let’s test this out. First, enable the Isolation Level.
ALTER DATABASE Northwind SET READ_COMMITTED_SNAPSHOT
ON
Now open a new connection (Connection1) and run the below
query.
USE Northwind
BEGIN TRAN
-- update the HireDate from 5/1/1992 to 5/2/1992
UPDATE dbo.Employees
SET HireDate = '5/2/1992'
WHERE EmployeeID = 1
This makes a last consistence version in the version store.
Now open the second connection (Connection2) and try to retrieve the record.
USE Northwind
GO
BEGIN TRAN
SELECT *
FROM dbo.Employees
WHERE EmployeeID = 1
You get a record from the version store. The value for the
HireDate will be the last consistence value that is 05/01/1992. Go back to
Connection1 and commit the transaction.
COMMIT TRAN
In Connection1, execute the SELECT statement again. Unlike
Snapshot the latest consistence is returned that has the HireDate as
05/02/1992. Commit the Connection2 transaction too.
Since the maintaining old versions are not necessary with
this level, there will be no impact for performance like Snapshot but all the
concurrency related problems except dirty reads can happen.
Finally, let’s summarize. The below table depicts importance
points of each level.
Dirty
Reads
|
Lost
Updates
|
Nonrepeatable
reads
|
Phantom
reads
|
Concurrency
model
|
Conflict
Detection
|
|
Read Uncommitted
|
Yes
|
Yes
|
Yes
|
Yes
|
Pessimistic
|
No
|
Read
Committed
|
No
|
Yes
|
Yes
|
Yes
|
Pessimistic
|
No
|
Repeatable
Read
|
No
|
No
|
No
|
Yes
|
Pessimistic
|
No
|
Serializable
|
No
|
No
|
No
|
No
|
Pessimistic
|
No
|
Snapshot
|
No
|
No
|
No
|
No
|
Optimistic
|
Yes
|
Read
Committed Snapshot
|
No
|
Yes
|
Yes
|
Yes
|
Optimistic
|
No
|
