MSSQL Concurrency Problems & Isolation Levels — Part 2

This is a continuation of this article.

There are no issues when only one process accesses the tables. However, this scenario is unlikely to occur. Multiple threads will attempt to access the same rows, leading to concurrency problems.

In SQL databases, four common concurrency issues can arise:

1. Dirty reads
2. Lost updates
3. Non-repeatable reads
4. Phantom reads

The complexity of addressing these issues gradually increases.

In MSSQL Server, the above-mentioned issues are handled using Transaction Isolation Levels, which include:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Snapshot
5. Serializable

These transaction levels provide different level of isolation levels.

When assigning an isolation level to a transaction, the isolation level should be specified before the transaction begins.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION
UPDATE Employees SET FirstName = 'Mark' WHERE EmployeeId = 1002
COMMIT TRANSACTION

If an isolation level is not explicitly mentioned, the default isolation level will be assigned to the transaction. In MSSQL, the default isolation level is Read Committed.

Read Uncommitted is the lowest isolation level in SQL Server’s transaction isolation hierarchy. Snapshot and Serializable isolation levels can handle every concurrency issue.

Let’s discuss both isolation levels and concurrency issues simultaneously.

1. The Dirty Read

A dirty read occurs when one transaction is allowed to read data that has been modified by another transaction but has not yet been committed. If the first transaction is rolled back after the second transaction reads the data, the second transaction ends up with dirty data that no longer exists.

Here, we use the test Employees table. The row is as follows.

-- Transaction 1

BEGIN TRANSACTION
UPDATE Employees SET FirstName = 'Mark' WHERE EmployeeId = 1002

WAITFOR DELAY '00:00:10'
ROLLBACK

In this query, the transaction is not committed. The transaction waits for 10 seconds and then rolls back.

Before rolling back, we run the second transaction.

-- Transaction 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Employees WHERE EmployeeId = 1002

Here, we specifically set the transaction level to Read Uncommitted. This reads the employee like this.

But after the rollback, the FirstName reverts back from ‘Mark’ to ‘Shehan’. This means our second transaction gets a dirty read.

To resolve this issue, we can use a higher level of transaction isolation than Read Uncommitted.

2. Lost Update

The “lost update” problem refers to a scenario where two transactions attempt to update the same data concurrently, but one transaction’s update is overwritten by the other transaction’s update, leading to a “lost” update.

The initial condition of the table is as follows.

The transactions happen like this.

-- Transaction 1 Starts running
BEGIN TRANSACTION
UPDATE Employees SET FirstName = 'Mark' WHERE EmployeeId = 1002
-- Transaction 2 Starts running
COMMIT TRANSACTION

-- Transaction 2 Starts running
BEGIN TRANSACTION
UPDATE Employees SET FirstName = 'John' WHERE EmployeeId = 1002
COMMIT TRANSACTION

Transaction 2 starts running before transaction 1 is committed. Therefore, one transaction will be lost.

The Lost Update issue is critical when it happens on an online shopping website. If there is only one item left and two customers try to buy it at the same time, credits will be deducted from both customers, but there was only one item available.

To resolve this issue, we can use a higher level of transaction isolation than Read Committed.

MSSQL Concurrency Problems & Isolation Levels — Part 3