Isolation Levels 1

dbtut

6 years ago

Isolation Level determines how other transactions will behave in response to a transaction.

First, consider the concurrency problems that can occur in the database.

We will then examine with examples how these problems arise at which isolation level.

Dirty Read:

When a transaction selects, it reads the uncommitted state of the update made by another transaction.

If another transaction rolls back instead of commit, the select made by the first transaction will read the invalid data.

This is why it is called dirty read.

Lost update:

Two transactions will read the same data and update the value they read with a new value.

The first update will disappear. This is why it is called lost update.

Non-repeatable read:

When a select is performed in a transaction, an update is made by another transaction that modifies this select result, a different value will return when the same select is called the second time in the first transaction.

Phantom read:

When a select is executed more than once in a transaction, a different number of records can be return each time.

Suppose you have a select on the first transaction.

If another transaction adds or deletes a record to the range contained in the select operation in the first transaction, the number of records returned in the first transaction will be different when the select operation is performed second time in the first transaction.

Now let’s examine the Isolation Levels by making examples. We will do the examples on the AdventureWorks2012 database.

1) Read Uncommitted:

Data that has been updated but has not yet been committed can be read. In this Isolation Level, all concurrency problems can occur.

Besides this, performance is more effective. Let’s do some examples of concurrency problems that might occur in this Isolation Level.

Dirty Read:

Let’s run the following query. In this query we will process records with FirstName Abigail, LastName Jones and BusinessEntityID 12038.

In the following example, we set the FirstName of this record to ‘DirtyReadsExample’.

Then we wait 10 seconds and rollback the update. And finally, we take our record with the necessary filters.

USE AdventureWorks2012;
BEGIN TRANSACTION;
UPDATE Person.Person
SET FirstName = 'DirtyReadsExample'
WHERE LastName = 'Jones' AND BusinessEntityID=12038;
WAITFOR DELAY '00:00:10.000';
ROLLBACK TRANSACTION;
SELECT FirstName
,LastName,BusinessEntityID
FROM Person.Person
WHERE LastName = 'Jones' AND BusinessEntityID=12038;

USE AdventureWorks2012;

BEGIN TRANSACTION;

UPDATE Person.Person

SET FirstName = 'DirtyReadsExample'

WHERE LastName = 'Jones' AND BusinessEntityID=12038;

WAITFOR DELAY '00:00:10.000';

ROLLBACK TRANSACTION;

SELECT FirstName

,LastName,BusinessEntityID

FROM Person.Person

WHERE LastName = 'Jones' AND BusinessEntityID=12038;

Open a second session and run the following script.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName ,LastName,BusinessEntityID
FROM Person.Person
WHERE LastName = 'Jones' AND BusinessEntityID=12038;

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName ,LastName,BusinessEntityID

FROM Person.Person

WHERE LastName = 'Jones' AND BusinessEntityID=12038;

Because we rollback the update we made in the first session, the value of FirstName came as Abigail in the select statement that was executed at the end of the query.

The second session received the change even though the first session did not commit the update and saw FirstName as DirtyReadsExample.

If we put the WITH (NOLOCK) hint at the end of the queries, it will run as Read Uncommitted even though we did not set the Isolation Level in our queries.

Lost Update:

Let’s run the following query in the first session.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 5;
BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

WAITFOR DELAY '00:00:10.000';

UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;
SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @SafetyStockLevel int = 0

,@Uplift int = 5;

BEGIN TRAN;

SELECT @SafetyStockLevel = SafetyStockLevel

FROM Production.Product

WHERE ProductID = 1;

SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

WAITFOR DELAY '00:00:10.000';

UPDATE Production.Product

SET SafetyStockLevel = @SafetyStockLevel

WHERE ProductID = 1;

SELECT SafetyStockLevel

FROM Production.Product

WHERE ProductID = 1;

COMMIT TRAN;

Let’s run the following query in a second session.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @SafetyStockLevel int = 0
,@Uplift int = 100;

BEGIN TRAN;
SELECT @SafetyStockLevel = SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;

SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

UPDATE Production.Product
SET SafetyStockLevel = @SafetyStockLevel
WHERE ProductID = 1;

SELECT SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1;
COMMIT TRAN;

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @SafetyStockLevel int = 0

,@Uplift int = 100;

BEGIN TRAN;

SELECT @SafetyStockLevel = SafetyStockLevel

FROM Production.Product

WHERE ProductID = 1;

SET @SafetyStockLevel = @SafetyStockLevel + @Uplift;

UPDATE Production.Product

SET SafetyStockLevel = @SafetyStockLevel

WHERE ProductID = 1;

SELECT SafetyStockLevel

FROM Production.Product

WHERE ProductID = 1;

COMMIT TRAN;

As you can see below, the value of the column with the ProductID value 1 in the first transaction is set to @SafetyStockLevel first.

Then @Uppfliet is added to @SafetyStockLevel and Waiting for 10 seconds starts.

In the second transaction, same processes are performed up to the waiting process.

And without waiting, the update process is performed. As a result, in the second transaction, you see that the value of 1100 is returned.

In the first transaction, do the update after the wait.

This time we see that the value is 1005 and the value of 1100 in the second transaction is lost.

To better understand this example, let’s assume that two people use a common account.

Let’s imagine that there is 1000 dollar in the account.

In the second transaction, let’s assume that the second person deposite 100 dollar in his account.

In the first transaction, first person deposite 5 dollar in his account.

If there is a problem like this, 100 dollar deposited by the second person will be lost.

Non-repeatable read:

Run the following script in the first session.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person
ORDER BY LastName;
WAITFOR DELAY '00:00:10.000';
SELECT TOP 5
FirstName
,MiddleName
,LastName
,Suffix
FROM Person.Person
ORDER BY LastName;
COMMIT TRANSACTION;

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

SELECT TOP 5

FirstName

,MiddleName

,LastName

,Suffix

FROM Person.Person

ORDER BY LastName;

WAITFOR DELAY '00:00:10.000';

SELECT TOP 5

FirstName

,MiddleName

,LastName

,Suffix

FROM Person.Person

ORDER BY LastName;

COMMIT TRANSACTION;

In the second session, we run the following script.

USE AdventureWorks2012;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
UPDATE Person.Person
SET Suffix = 'Junior'
WHERE LastName = 'Abbas'
AND FirstName = 'Syed';
COMMIT TRANSACTION;

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION;

UPDATE Person.Person

SET Suffix = 'Junior'

WHERE LastName = 'Abbas'

AND FirstName = 'Syed';

COMMIT TRANSACTION;

As you can see below, Suffix values are null in the first select in the first session.

In the second session, the Suffix value of the column is set to Junior, with FirstName being Syed and LastName being Abbas.

After waiting 10 seconds in the first session, the same select is repeated again and it returns a different result as you see it.

Phantom Read:

Run the following query in the first session.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION

--T1:RowCount=2
SELECT *
  FROM [AdventureWorks2012].[HumanResources].[Department]
  where GroupName='Manufacturing'

  WAITFOR DELAY '00:00:10.000';

--T2:RowCount=3
SELECT *
  FROM [AdventureWorks2012].[HumanResources].[Department]
  where GroupName='Manufacturing'
COMMIT TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRANSACTION

--T1:RowCount=2

SELECT *

FROM [AdventureWorks2012].[HumanResources].[Department]

where GroupName='Manufacturing'

WAITFOR DELAY '00:00:10.000';

--T2:RowCount=3

SELECT *

FROM [AdventureWorks2012].[HumanResources].[Department]

where GroupName='Manufacturing'

COMMIT TRANSACTION

In the second session, we run the following query.

USE [AdventureWorks2012]
GO
INSERT INTO [HumanResources].[Department]
           ([Name]
           ,[GroupName]
           ,[ModifiedDate])
     VALUES
           ('PhantomReadExample'
           ,'Manufacturing'
           ,'2002-06-01 00:00:00.000')
GO

USE [AdventureWorks2012]

INSERT INTO [HumanResources].[Department]

([Name]

,[GroupName]

,[ModifiedDate])

VALUES

('PhantomReadExample'

,'Manufacturing'

,'2002-06-01 00:00:00.000')

As you can see below, the first select in the first transaction returns two records while the second select returns 3 records.

When we use the READ UNCOMMITTED Isolation Level, all the concurrency problems can occur.

In addition to this, select queries provide some gains in terms of performance because they get results without waiting.

If you are working on a system where data consistency is not very important, you can choose this Isolation Level.

2)Read Committed:

Read Committed is the default Isolation Level in SQL SERVER.

Unlike Read Uncommitted, another transaction can not read this data until the transaction that is being updated in the transaction is committed.

This prevents the creation of dirty reads. On the other hand concurrency and performance will be reduced.

Lost Update, Non-repeatable read and Phantom Read will behave as in Read Uncommitted at this Isolation Level.

Let’s repeat our dirty read example using this Isolation Level.

Dirty Read:

Unlike READ UNCOMMITTED, when we run the second session, it will hold us until the first session is over. After the first session is over, the process will be completed in the second session, and as a result, non-dirty data will be returned to us as Abigail Jones 12038.

3) Repeatable Read:

The purpose of this Isolation Level is to ensure that the result of the select within a transaction remains the same until the end of the transaction.

If a select query is pulled in a transaction that is running with Repeatable Read, this data can not be updated by another transaction.

In this Isolation Level, our dirty read example and phantom read example will behave like READ COMMITTED Isolation Level.

Repeat our Lost Update and Non-repeatable read examples.

Lost Update:

When we implement our example, we see that the second session is waiting for the first session to finish.

If we run the update command in the first session, we see that the second session gets the following error.

We see that the Repeatable Read Isolation Level prevents the select in the first session from changing in the second session, so no lost update occurs. Transaction (Process ID X) was deadlocked on lock resources with a deadlock victim.

Rerun the transaction.

Non-Repeatable Read:

When we implement our example, we see that the second session is waiting for the first session to finish.

Unlike the READ COMMITTED Isolation Level when the first session is committed, we see that the two select results are the same.

REPEATABLE READ did not allow SELECT to be updated by another transaction until the transaction finished.

But after the transaction is over, we see that the second session is making the changes that it wants.

4) Serializable:

If select is done within a transaction at this Isolation Level, the select range of the first transaction can not be accessed within another transaction until the end of the first transaction. (Insert, Update, Delete)

And if data modification operations are performed in a transaction, other transactions can not read in the transaction range.

If we repeat our Dirty Read, Lost Update and Non-repeatable Read samples at this Isolation Level, we will get the same results as the Repeatable Read Isolation Level results.

But when we repeat our Phantom Read example, we will see that this problem has ceased to exist.

Phantom Reads:

When we execute our queries in SERIALIZABLE Isolation Level, we get the following result from the first query.

As you can see, at this Isolation Level, the second transaction insert could not be performed until the first transaction finishes.

In the second article of our series, we will examine the Isolation Levels (RCSI and SNAPSHOT) based on row versioning.

By defining these Isolation Levels, we will see how the concurrency problem occurs at which isolation level, and the advantages and disadvantages of row versioning based on examples.