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
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 TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT
*
FROM LogDetail
WHERE Id = 3
- Open new window/new connection (we name this window User B), then execute below
USE SampleDatabase
GO
SELECT
*
FROM LogDetail
- Open new window/new connection (we name this window User C), then execute below
USE SampleDatabase
GO
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 INTO LogDetail
VALUES (4, 'Save button clicked', REPLICATE('D', 100))
- Now in User A window execute below
COMMIT TRANSACTION
- 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 TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT
*
FROM LogDetail
WHERE Id = 3
- Open new window/new connection (we name this window User B), then execute below
USE SampleDatabase
GO
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 TRANSACTION
- 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!!!
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