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

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

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

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

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

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

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

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

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

--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!!!