Click here to Skip to main content
15,115,909 members
Articles / Database Development / SQL Server
Article
Posted 6 Feb 2018

Tagged as

Stats

6.8K views
1 bookmarked

Repeatable Read and Serializable Isolation Level

Rate me:
Please Sign up or sign in to vote.
2.00/5 (3 votes)
6 Feb 2018CPOL3 min read
To make sure your read data won't be altered during the current transaction.

Hello folks, hope you all are doing well.

In this article we are focusing to solve a specific problem.

Problem:

There is a stored procedure where multiple rows want to be read, multiple operations need to be performed on those rows and at the end of transaction those rows need to be committed within transaction, during current transaction, No other transaction should alter the data of those rows. What can we do to solve this problem?

Solution:

Here to solve this problem I think isolation levels can help us.

Repeatable Read isolation level

  • Repeatable Read isolation level enables current transaction to read rows which satisfied specific condition (if given) and lock those rows for update.
  • No other than current transaction can alter the data of those locked rows.
  • User can read those locked rows.
  • User can insert new rows to table which contains those locked rows. In very next read current transaction gets those newly inserted rows.
  • User cannot update data of those locked rows until current transaction finishes.
  • After completing the current transaction lock has been released from those rows.

Let's look how Repeatable Read can help us in action

  • First we are going to setup a sample database
SQL
--Create test database and insert some test data

USE master
GO


DROP DATABASE SampleDatabase
GO


CREATE DATABASE SampleDatabase
GO


USE SampleDatabase
GO


CREATE TABLE LogDetail (
  Id int NOT NULL,
  Name char(100) NOT NULL,
  [Description] varchar(3000) NOT NULL
)
GO


INSERT INTO LogDetail
  VALUES (1, 'Log In', REPLICATE('A', 3000))
INSERT INTO LogDetail
  VALUES (2, 'Log Out', REPLICATE('B', 1000))
INSERT INTO LogDetail
  VALUES (3, 'Error', REPLICATE('C', 3000))
GO

 

  • Open new window/new connection (we name this window User A), then execute below
SQL
--Set repeatable read isolation level

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

  --Acquire repeatable read lock
  SELECT
    *
  FROM LogDetail
  WHERE Id = 3

 

  • Open new window/new connection (we name this window User B), then execute below
SQL
--Read request from other connection
USE SampleDatabase
GO

SELECT
  *
FROM LogDetail

 

  • Open new window/new connection (we name this window User C), then execute below
SQL
USE SampleDatabase
GO

--Try Update row data which is being locked
UPDATE LogDetail
SET Name = 'Exception'
WHERE Id = 3

As you can see in above snap, update has to wait until current transaction completes as row for which update has been requested is locked by repeatable read.

 

  • Open new window/new connection (we name this window User D), then execute below
SQL
USE SampleDatabase
GO


--Insert new row to the table contains locked rows
INSERT INTO LogDetail
  VALUES (4, 'Save button clicked', REPLICATE('D', 100))

 

  • Now in User A window execute below
SQL
--Commit the current running transaction

COMMIT TRANSACTION

--All the locked rows are released now

 

  • Now you can see below thing in User C window

row has been updated.

Serializable Isolation Level

  • Serializable isolation level can also solve our problem. But there is one difference between Repeatable Read and Serializable.
  • Serializable enables current transaction to read rows which satisfied specific condition (if given) and lock those rows for update and insert both.
  • No other than current transaction can alter the data of those locked rows.
  • User can read those locked rows.
  • User cannot update data of those locked rows.
  • User cannot insert new row to the table that contains those locked rows until current transaction completes
  • After completing the current transaction lock has been released from those rows.

Let's look how Serializable can help us in action

  • Open new window/new connection (we name this window User A), then execute below
SQL
--Set serializable isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

   --Acquire serializable lock
  SELECT
    *
  FROM LogDetail
  WHERE Id = 3

 

  • Open new window/new connection (we name this window User B), then execute below
SQL
USE SampleDatabase
GO

--Try to insert new row
INSERT INTO LogDetail
  VALUES (4, 'Save button clicked', REPLICATE('D', 100))

As you can see in above snap, insert has to wait until current transaction completes.

 

  • Now in User A window execute below
SQL
--Commit the current running transaction

COMMIT TRANSACTION

--All the locked rows are released now

 

  • Now you can see below thing in User B window

new row has been inserted.

 

Repeatable read is less restrictive with compare to serializable isolation level.
One should choose between these as per the requirement.

That's all folks. See you in next article.

Thank you

Happy Engineering!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Viral Surani
Architect
India India
Software Design Expert with 4+ years of experience working in variety of industries. Very creative problem solver with strong interpersonal skills, Believe in flawless and robust system design, Known for consistently coming up with original ideas and being able to clearly communicate them.

Email : suraniviral@live.com

Comments and Discussions

 
Questionserializable Pin
dmjm-h8-Feb-18 11:23
Memberdmjm-h8-Feb-18 11:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.