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 |
About the author:
Dinesh Priyankara is a SQL Server MVP in Sri Lanka and is currently employed as a Database Administrator in a private firm. He teaches, consults and runs training programs for various institutes/companies in Sri Lanka and he is one of founders of www.sqlserveruniverse.com. Dinesh maintains his technical blog at http://dineshpriyankara.spaces.live.com.
]]>