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
|
No comments:
Post a Comment