Isolation levels Demo

READ UNCOMMITTED

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now select the value that's being updated using the following (in a separate query window):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

GO

SELECT EmpID, EmpName, EmpSalary

FROM dbo.TestIsolationLevels

WHERE EmpID = 2900

READ COMMITTED (snapshot)

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels WITH (READCOMMITTEDLOCK)

WHERE   EmpID = 2900

The query will hang as it is waiting for the key lock on EmpID to be released. Allow the query to execute by issuing in your first window:

ROLLBACK;

READ COMMITTED

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

The query will return the last consistent row of data. Note the empSalary column is 22000.00 despite the transaction being open and the update written (but uncommitted). This is correct, and the SELECT is reading from the previous row version, not the present state of the row. This is compliant with the C in ACID - consistency.

SNAPSHOT isolation

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SET NOCOUNT ON

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

You will note that, like READ COMMITTED, the correct snapshot of the data row is returned, yielding empSalary = 22000, which is consistent and correct. Rollback the transaction.

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

WAITFOR DELAY '00:00:10'

SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels

WHERE   EmpID = 2900

COMMIT

Run the below while the above is executing:

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels

SET     EmpSalary = 25000

WHERE   EmpID = 2900

COMMIT

Problem with this isolation level is, we can see phantom rows (newly inserted) within the transaction.

Serializable Isolation

SERIALIZABLE has all the features of READ COMMITTED, REPEATABLE READ but also ensures concurrent transactions are treated as if they had been run in serial. This means guaranteed repeatable reads, and no phantom rows. Be warned, however, that this (and to some extent, the previous two isolation levels) can cause large performance losses as concurrent transactions are effectively queued.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET NOCOUNT ON

GO

BEGIN TRAN

SELECT  EmpName

FROM    dbo.TestIsolationLevels

WAITFOR DELAY '00:00:10'

SELECT  EmpName

FROM    dbo.TestIsolationLevels

COMMIT

Run the below while the above is executing:

BEGIN TRAN

INSERT INTO dbo.TestIsolationLevels VALUES (3427, 'Phantom Employee 1', 30000)

COMMIT