Repeatable Read Isolation Level
This Isolation Level addresses all concurrency related problems except Phantom reads. Unlike Read Committed, it does not release the shared lock once the record is read. It obtains the shared lock for reading and keeps till the transaction is over. This stops other transactions accessing the resource, avoiding Lost Updates and Nonrepeatable reads. Change the Isolation Level of the stored procedure we used for Read Committed sample.
IF OBJECT_ID(N’dbo.UpdateNotes’, N’P’) IS NOT NULL
BEGIN
DROP PROC dbo.UpdateNotes
END
GO
CREATE PROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext
AS
BEGIN
DECLARE @IsUpdated bit
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT @IsUpdated = CASE WHEN Notes IS NULL THEN 0 ELSE 1 END
FROM dbo.Employees
WHERE EmployeeID = @EmployeeID — new record
Now make two connections and execute below queries just as you did with Read Committed sample. Make sure you set the Note column value back to NULL before executing them.
With Connection1;
EXEC dbo.UpdateNotes 15, ‘Jane has a BA degree in English from the University of Washington.’
With Connection2;
EXEC dbo.UpdateNotes 15, ‘Jane holds a BA degree in English.’
Once you execute the code with Connection2, SQL Server will throw 1205 error and Connection2 will be a deadlock victim. This is because, Connection1 obtain and hold the lock on the resource until the transaction completes, stopping accessing the resource by others, avoiding Lost Updates. Note that setting DEADLOCK_PRIORITY to HIGH, you can choose the deadlock victim.
Since the lock is held until the transaction completes, it avoids Nonrepeatable Reads too. See the code below.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT Notes
FROM dbo.Employees
WHERE EmployeeID = 10
It reads a record from the Employees table. The set Isolation Level guarantees the same result for the query anywhere in the transaction because it holds the lock without releasing, avoiding modification from others. It guarantees consistency of the information and no Nonrepeatable reads.
Now let’s take another simple example. In this case, we add one new table called Allowances and one new column to Employees table called IsBirthdayAllowanceGiven. The code for changes are as below;
USE Northwind
GO
— table holds allowances
CREATE TABLE Allowances (EmployeeID int, MonthAndYear datetime, Allowance money)
GO
— additional column that tells whether the birthday allowance is given or not
ALTER TABLE dbo.Employees
ADD IsBirthdayAllowanceGiven bit DEFAULT(0) NOT NULL
GO
Assume that company pays an additional allowance for employees whose birth date fall on current month. The below stored procedure inserts allowances for employees whose birth date fall on current month and update employees record. Note that WAITFOR DELAY has been added hold the transaction for few seconds in order to see the problem related to it. And no Isolation Level has been set, default applies.
IF OBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’) IS NOT NULL
BEGIN
DROP PROC dbo.AddBirthdayAllowance
END
GO
CREATE PROC dbo.AddBirthdayAllowance
AS
BEGIN
BEGIN TRAN
— inserts records to allowances table
INSERT INTO Allowances
(EmployeeID, MonthAndYear, Allowance)
SELECT EmployeeID, getdate(), 100.00
FROM dbo.Employees
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
— hold the transaction for 5 seconds
— Consider this is as some other process that takes 5 seconds
WAITFOR DELAY ’00:00:05′
— update IsBirthdayAllowanceGiven column in Employees table
UPDATE dbo.Employees
SET IsBirthdayAllowanceGiven = 1
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
COMMIT TRAN
END
Before running any queries, make sure at least one employee’s birth date falls on current month. Now open a new connection (let’s name it as Connection1) and run the stored procedure. In my Northwind database, I have one record that stratifies the criteria; EmployeeId 6: Michael Suyama.
USE Northwind
GO
EXEC dbo.AddBirthdayAllowance
Immediately, open Connection2 and insert a new employee whose birth date falls into current month.
USE Northwind
GO
INSERT INTO dbo.Employees
(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate)
VALUES
(‘Creg’, ‘Alan’, ‘Sales Representative’, ‘Ms.’, ’07/13/1980′, ’07/20/2007′)
Go back to Connection2. Once the transaction completed, query the Allowances table and see. You will see a one record that is generated for Michael. Then open the Employees table and see that how many records have been updated. It has updated two, not only Michael but Alan. Note that no record has been inserted to the Allowances table for Alan. In this case, the new record is considered as a Phantom record and read of the new record called as Phantom Read. This cannot be avoided with default Isolation Level that is Read Committed. Change the stored procedure and set the Isolation Level as Repeatable Read.
IF OBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’) IS NOT NULL
BEGIN
DROP PROC dbo.AddBirthdayAllowance
END
GO
CREATE PROC dbo.AddBirthdayAllowance
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
— inserts records to allowances table
INSERT INTO Allowances
(EmployeeID, MonthAndYear, Allowance)
SELECT EmployeeID, getdate(), 100.00
FROM dbo.Employees
WHERE IsBirthdayAllowanceGiven = 0
AND MONTH(BirthDate) = MONTH(getdate())
Now bring the Employees table to original state.
UPDATE dbo.Employees
SET IsBirthdayAllowanceGiven = 0
DELETE dbo.Employees
WHERE FirstName = ‘Alan’
DELETE dbo.Allowances
Open two connections again and try the same. Check the result. Still the Phantom Reads problem exists. In order to avoid this problem, you need to use highest Isolation Level that is Serializable.