Monday, July 20, 2015

SQL - Explanation of Transaction Isolation levels

SQL - Transaction Isolation Levels
The concept of isolation levels was first introduced in under the name Degrees of Consistency. We know that commercial databases support different isolation levels to allow programmers to trade off consistency for a potential gain in performance.


To better understanding of each isolation level, will see the isolation level impacts on an employee master table
To understand the Read Uncommitted Transaction Isolation level, we will use two query windows in SSDT. In first query window, we will run an update command by including a WAITFOR DELAY statement, with five seconds and rollback the update statement again. Within this delay time frame, we will execute the second query by setting with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED as shown below –
Query 1 or Session 1
BEGIN TRANSACTION;

-- Update the existing Employee having EmpId=102
UPDATE EmployeeMaster
SET EmpName = 'Frank'
WHERE EmpID = 102;

-- Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05'

-- Force to roll back the changes 
ROLLBACK TRANSACTION;

Please keep in mind that the transaction includes a WAITFOR DELAY statement, with five seconds specified. This is included to give us time to run our statements in the second session.
Query 2 or Session 2
-- Set the Read Uncommitted Transaction Isolation level 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT [EmpId]       ,[EmpName]
      ,[EmpSalary]       ,[EmpAge]       ,[EmpStatus]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102
 

We will see that Query 2 or session 2 will not wait for Query 1 or session 1 to finish and the most important thing is that Query 2 returns dirty data due to Read Uncommitted Transaction Isolation level.
Remember Query 1 or session 1 rolls back all its changes however Query 2 or session 2 has returned the data anyway; this is because it didn't wait for all the other transactions with exclusive locks on this data it just returned what was there at the time of execution.

This is the default isolation level of SQL and supports to return only committed data via select statements.
Query 1 or Session 1
BEGIN TRANSACTION;

-- Update the existing Employee having EmpId=102
UPDATE EmployeeMaster
SET EmpName = 'Frank'
WHERE EmpID = 102;

-- Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05'

-- Force to roll back the changes 
ROLLBACK TRANSACTION;

Please keep in mind that the transaction includes a WAITFOR DELAY statement, with five seconds specified. 
Query 2 or Session 2
-- Set the Read Uncommitted Transaction Isolation level 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT [EmpId]       ,[EmpName]
      ,[EmpSalary]       ,[EmpAge]       ,[EmpStatus]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102
 

We will see that Query 2 or session 2 will wait for Query 1 or session 1 to complete before returning and also how the data returned is the data we started off with as Query 1 did a rollback. In this way, no dirty read is returned. But another problem is lurking: the non-repeatable read. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.

This is very similar to Read Committed but with the additional guarantee card that if we issue the same select twice in a transaction with WAITFOR DELAY statement, we will get the same results both times. Repeatable Read does this by holding on to the shared locks means any transactions that try to modify these records are forced to wait for the read transaction to complete.
Query 1 or Session 1
-----Query 1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

SELECT [EmpId] ,[EmpName]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102
 -- Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05'

SELECT [EmpId] ,[EmpName]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102

ROLLBACK TRANSACTION;
GO

In Query 2, during the Query 1 delay, we run the same UPDATE statement as shown below:
Query 2 or Session 2
-- Update the existing Employee having EmpId=102
UPDATE EmployeeMaster
SET EmpName = 'Ryan'
WHERE EmpID = 102; 

Notice that Query 1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query 1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

We won't see phantoms if the same query is issued twice within a transaction means this isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads.
Query 1 or Session 1
-----Query 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

SELECT [EmpId] ,[EmpName]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102
 -- Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:05'

SELECT [EmpId] ,[EmpName]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102

ROLLBACK TRANSACTION;
GO

In Query 2, during the Query 1 delay, we run the same UPDATE statement as shown below:
Query 2 or Session 2
--- Query 2
-- Insert new record into Employee
INSERT INTO [dbo].[EmployeeMaster]
           ([EmpName]
           ,[EmpSalary]
           ,[EmpAge]
           ,[EmpStatus])
     VALUES
           ('Daisy Smith',25000, 26, '0')
GO


In this Serializable Isolation level, query 2 will wait the Query 1 to complete before it runs eradicating the chance of a phantom read because Serializable isolation level relies on shared data locks to address concurrency issues.

This relies on row versions being maintained in the tempdb database to avoid locking contention issues. To enable snapshot isolation, we need to run an ALTER DATABASE statement to set the ALLOW_SNAPSHOT_ISOLATION option to ON as shown below:
ALTER DATABASE Keys
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

When we enable the ALLOW_SNAPSHOT_ISOLATION option, we activate a mechanism in our database for storing the versioned rows in tempdb. We can then set the Snapshot isolation level within your session for specific transactions:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

SELECT [EmpId] ,[EmpName]
  FROM [dbo].[EmployeeMaster]
  Where EmpId=102
 -- Simulate having some intensive processing here with a wait

COMMIT TRANSACTION;
GO
More recent research over the past decade has found several snapshot isolation methods that satisfy serializability as well.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands