MSSQL Concurrency Problems & Isolation Levels — Part 3

This is a continuation of this article.

we discussed Dirty Read and Lost Update concurrency problems. Now, let’s delve into Non-Repeatable Read and Phantom Reads.

3. Non-Repeatable Read

Non-Repeatable Read occurs when one transaction reads the same data twice, and another transaction updates the data between the first and second read of transaction one.

BEGIN TRANSACTION
SELECT * FROM Employees WHERE EmployeeId = 1002
WAITFOR DELAY '00:00:20'
SELECT * FROM Employees WHERE EmployeeId = 1002
COMMIT TRANSACTION

When this transaction is waiting for 20 seconds, we run the below SQL statement

UPDATE Employees SET Salary = 100000 WHERE EmployeeId = 1002

As we discussed, the first select statement is wrapped with the default isolation level. It cannot handle this concurrency issue. Repeatable read or any other higher isolation level should solve the non-repeatable read problem.

To fix the non-repeatable read problem, set the transaction isolation level of Transaction 1 to repeatable read. This will ensure that the data that Transaction 1 has read will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read problem.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM Employees WHERE EmployeeId = 1002
WAITFOR DELAY '00:00:20'
SELECT * FROM Employees WHERE EmployeeId = 1002
COMMIT TRANSACTION

4. Phantom Read

A phantom read occurs when one transaction executes a query twice and receives a different number of rows in the result set each time. This discrepancy happens when a second transaction inserts a new row that matches the WHERE clause of the query executed by the first transaction.

To fix the phantom read problem, set the transaction isolation level of Transaction 1 to serializable. This will place a range lock on the rows between 1 and 3, preventing any other transaction from inserting new rows within that range.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT * FROM Employees WHERE EmployeeId BETWEEN 1 AND 3
WAITFOR DELAY '00:00:10'
SELECT * FROM Employees WHERE EmployeeId BETWEEN 1 AND 3
COMMIT TRANSACTION

Repeatable read only prevents non-repeatable reads. The repeatable read isolation level ensures that the data read by one transaction will be protected from updates or deletions by any other transaction. However, it does not prevent new rows from being inserted by other transactions, which can lead to phantom read concurrency problems.

Serializable isolation level prevents both non-repeatable reads and phantom read problems. It ensures that the data read by one transaction will be protected from updates or deletions by any other transaction. Additionally, it prevents new rows from being inserted by other transactions, effectively eliminating both non-repeatable read and phantom read problems.