Demonstrations of Transaction Isolation Levels in SQL Server

Problem

You are looking at ways of ensuring atomicity and consistency in your database by ensuring transactional integrity. You may want to avoid locks, or ensure problems such as lost updates or dirty reads cannot occur, or otherwise ensure the quality of your data. Transactional isolation is a narrow but somewhat complex subject that might be ideal for your needs.

Solution

This article will cover the five transaction isolation settings - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION. You'll find short explanations on the differences between them with emphasis on practical code examples to demonstrate the effects at different isolation levels. Finally, you'll find some links at the bottom for further reading. Please execute the code in 'Setting up the Test Environment' below in your development database context to get started, or read the 'ACID: Review' section below for a bit of background in the four principles of an RDBMS system.

ACID: Review

Before I dive into transaction isolation, it's worth taking a brief look at four of the fundamental principles underlying relational database management systems. These four principles are referred to as 'ACID', and each letter is an acronym for one property of RDBMS systems that is non-negotiable for the sake of the integrity of the system. These are:

Atomic(ity) - The principle that each transaction is 'all-or-nothing', i.e. it either succeeds or it fails, regardless of external factors such as power loss or corruption. On failure or success, the database is left in either the state in which it was in prior to the transaction or a new valid state. The transaction becomes an indivisible unit.




Consistency - The principle that the database executes transactions in a consistent manner, obeying all rules (constraints). For example, consider the following table:

CREATE TABLE dbo.MyTestTable ( ColA SMALLINT,

olA UNIQUE )

 CONSTRAINT uq_C

Now consider the following valid transactions that will leave the database in a consistent state:


• INSERT INTO dbo.MyTestTable VALUES (1)
• INSERT INTO dbo.MyTestTable VALUES (9)


But the following statements, if executed and allowed to modify data, will leave the database in an inconsistent state, since they violate some constraint (or allowed datatype) of the defined table. Hence an error is returned:


• INSERT INTO dbo.MyTestTable VALUES ('Hello')
• INSERT INTO dbo.MyTestTable VALUES (3),(3),(3)


Isolation - This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction. This property level is variable, and as this article will discuss, SQL Server has five levels of transaction isolation depending on the requirements of the database.


Durability - This property means that the data written to the database is durable, i.e. it is guaranteed to be in storage and will not arbitrarily be lost, changed or overwritten unless specifically requested. More formally, it means that once a transaction is committed, no event can 'un-commit' the transaction - it is written and cannot be changed retrospectively unless by another transaction.

Setting up the Test Environment

-- init

SET NOCOUNT OFF

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
GO
CREATE TABLE dbo.TestIsolationLevels (

EmpSalary MONEY,
CO
EmpID INT NOT NULL,
EmpName VARCHAR(100),

INSERT INTO dbo.TestIsolationLevels 
V
NSTRAINT pk_EmpID PRIMARY KEY(EmpID) )
GO
ALUES 
(2322, 'Dave Smith', 35000),

hns', 18000) 
GO

(2900, 'John West', 22000),
(2219, 'Melinda Carlisle', 40000),
(2950, 'Adam J

o

Experiment 1: Read using READ UNCOMMITTED

READ UNCOMMITTED is the most optimistic concurrency isolation option available in SQL Server. It allows a transaction to get the value in a row even when locks are present on the row/object or it hasn't yet been committed to disk. Reads like this are also known as 'dirty reads' since they effectively read from the transaction log rather than disk or cache - the data is unpersisted. (Note if no concurrent transactions are occurring, the read will occur from disk/cache). To show the effects of READ UNCOMMITTED, we can open a transaction as follows:

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

ame, EmpSalary
FROM dbo.TestIsol
SELECT EmpID, Emp
NationLevels

 2900

WHERE EmpID 
=

Note the value for empSalary reflects the current *uncommitted* value. You can view the intent lock on the key (empID) and the intent exclusive locks on the object containers (the page on which the row is located and the object) imposed by the UPDATE statement using the following:

SELECT      es.login_name, tl.resource_type, 
            tl.resource_associated_entity_id,

equest_status
FROM        sys
            tl.request_mode, 
            tl.
r.dm_tran_locks tl

exec_sessions es ON tl.request_session_id = es.session_id 
WHERE       es.log
INNER JOIN  sys.dm

_in_name = SUSER_SNAME() AND tl.resource_associated_entity_id <> 0

Now rollback the transaction to reset the EmpSalary for this employee to 22000.00:

ROLLBACK;

Experiment 2: Read using READ COMMITTED (snapshot)

There are two levels of READ COMMITTED isolation, which are locking and snapshot. Locking is the most straightforward, and simply means that once an UPDATE transaction is open, exclusive and intent-exclusive locks are taken out on the page, key range (if appropriate) and object. When reading the row using READ COMMITTED while using locking, the SELECT query used will hang until the value of LOCK_TIMEOUT (session-level parameter, if set) has expired, at which point an error will be returned.

If the value of the database-level option READ_COMMITTED_SNAPSHOT is False, locking mode for READ COMMITTED transactions is the default option. If it is True, then snapshot is the default option unless overridden by the READCOMMITTEDLOCK table hint.

Here's a demonstration of READ COMMITTED isolation with locking, by using the table hint in a database with READ_COMMITTED_SNAPSHOT ON. You don't need the table hint if this value is OFF:

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 with snapshot is different from locking. The UPDATE statement above will take the same locks on the objects, but with the SELECT transaction session specifying snapshot isolation, the row returned will be the last row version before the UPDATE statement began. This row version is stored internally and represents the last consistent state of the row. Logically it follows that if you are using row versioning, this capability must be DB-wide, since otherwise the transaction with the UPDATE statement would not know to maintain a version of the row before issuing the UPDATE. Therefore, to use snapshot isolation the option must be set using the ALTER DATABASE statement (note that all database user connections will be killed when doing this).

Note About Row Versioning

Row versioning is an internal feature used by SQL Server to maintain recent copies of rows that have been changed, for the purposes of maintaining table consistency and ensuring better isolation from reads or writes of transactions that concurrently access the same rows. Row versioning, also called 'Row-Level Versioning (RLV)' was first introduced in SQL Server 2005. Historical rows are kept in the 'version store', inside TEMPDB, and each row that has been 'versioned' has a row pointer added to it which allows the query engine to locate the versioned row. Interestingly, the 'inserted' and 'deleted' tables used with triggers and the OUTPUT clause (to name two uses) use a similar method of versioning. There are performance sacrifices made when using this level of transaction isolation - please see the note under 'Next Steps' for more information.

Below is an example of using the READ COMMITTED with snapshot isolation:

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

tionLevels 
SE
SET NOCOUNT ON
GO
BEGIN TRAN
UPDATE  dbo.TestIsol
aT     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.

Experiment 3: Read using SNAPSHOT isolation

For all intents and purposes, reads using READ COMMITTED - snapshot and SNAPSHOT are almost identical - but not identical. There are some differences when it comes to details and behavior. READ COMMITTED - snapshot will read the most recent consistent row version since the start of the statement being issued, where snapshot isolation will read the most consistent row version since the transaction started. This can cause problems with concurrent transactions since SELECTs inside the transaction that occur later than the COMMIT time of the UPDATE transaction will return an incorrect value. Likewise, update conflicts can occur for the same reason when concurrent updates are attempted. See Experiments 4 and 5 for demonstrations of this, and please refer to Kalen Delaney's excellent text, 'SQL Server 2008 Internals' (Microsoft, 2009, Ch: 10 Pgs: 641-643) for an in-depth discussion of this behavior.

To use snapshot isolation, you must first enable the feature as follows:

ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION ON

Now start the UPDATE again, and issue the SELECT in a separate query window like so:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON
GO
BEGIN TRAN

ls 
SET     EmpSalary = 25000
WH
UPDATE  dbo.TestIsolationLev

eERE   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.

Experiment 4: Read using REPEATABLE READ

The REPEATABLE READ isolation level is similar to the READ COMMITTED isolation level, in that it guarantees the output of uncommitted transactions won't be read by other concurrent transactions. However, if a separate concurrent transaction commits before the first one, it is possible to read the same row twice within the transaction and obtain different values. Likewise it is possible that additional 'phantom' rows could be present depending on the behavior of the concurrent transaction.

Execute the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
SET NOCOUNT ON
GO
BEGIN TRAN

lary
FROM    dbo.TestIsolationLev
SELECT  EmpID, EmpName, EmpS
aels 
WHERE   EmpID = 2900
WAITFOR DELAY '00:00:10'

olationLevels 
WHERE   EmpID = 29
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestI
s00

IT

COM
M

Now while this is executing, execute the following in a separate query window:

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels 
SET     EmpSalary = 25000

T

WHERE   EmpID = 2900
COMMIT

Note the results below. Despite the two SELECTs being in one explicit transaction, the empSalary value differs between the individual statements in that transaction. The next isolation level helps to solve this problem.

REPEATABLE READ is the isolation level to use if read requests (note: not updates) are returning inconsistent data *within one transaction*, and consists of a superset of the READ COMMITTED isolation type features (i.e. it encapsulates READ COMMITTED characteristics). Here is an example of using REPEATABLE READ when a concurrent UPDATE is occurring:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



SET NOCOUNT ON
GO
BEGIN TRAN

lary
FROM    dbo.TestIsolationLev
SELECT  EmpID, EmpName, EmpS
aels 
WHERE   EmpID = 2900
WAITFOR DELAY '00:00:10'

olationLevels 
WHERE   EmpID = 29
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIslationlevels

COMMIT

Run the below while the above is executing:

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels
SET     EmpSalary = 25000

T

WHERE   EmpID = 2900
COMMIT

You'll notice that the UPDATE transaction is waiting on the SELECT transaction, and that the SELECT transaction yields the correct data if the transaction consistency as a whole is considered. Interestingly though, this still doesn't hold true for phantom rows - it's possible to insert rows into a table and have the rows returned by a calling SELECT transaction even under the REPEATABLE READ isolation level.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpName

Y '00:00:10'
SELECT  EmpName
FRO
FROM    dbo.TestIsolationLevels 
WAITFOR DEL
AM    dbo.TestIsolationLevels 


COMMIT

Run the below while the above is executing:

BEGIN TRAN

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

COMMIT

To counter this problem, we need to use the SERIALIZABLE isolation level - the toughest of the bunch.

Experiment 5: 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. Here's the phantom rows example used in the previous section again but this time using the SERIALIZABLE isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpName

Y '00:00:10'
SELECT  EmpName
FRO
FROM    dbo.TestIsolationLevels 
WAITFOR DEL
AM    dbo.TestIsolationLevels 


COMMIT

Run the below while the above is executing:

BEGIN TRAN

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

COMMIT

Source collected from mssqltips.com