MSSQL Concurrency Problems & Isolation Levels — Part 1

While working on a project with SQL, I faced some issues related to concurrency, which is essentially dealing with multiple users accessing and modifying data simultaneously. After doing a lot of research, I learned about common concurrency problems in SQL and figured out ways to solve them using different isolation levels. In this blog series, I’ll explain these what a transaction is, common concurrency issues, and how to deal with them.

Transaction is a group of commands that change the data stored in a database. A transaction is treated as a single unit of work. This ensures that all the commands are succeed or none of them. This follows the Integrity of the database transactions.

For this we use BEGIN TRANSACTION and COMMIT TRANSACTION keywords.

BEGIN TRY
 BEGIN TRANSACTION 
  UPDATE Employees SET Age = 25 where EmployeeId = 1
  UPDATE Employees SET Age = 28 where EmployeeId = 2
 COMMIT TRANSACTION
 -- Transaction is committed here --
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
 -- All the above transactions are rolled back if any error occur inside try block --
END CATCH

In the example above, both UPDATE statements are executed successfully, and transaction committed. This means that the changes made by the commands are permanently saved in the database.

BEGIN TRY
 BEGIN TRANSACTION 
  UPDATE Employees SET Age = 25 where EmployeeId = 1
  UPDATE Employees SET Age = '28' where EmployeeId = 2
  -- An error occur when Age is set to a VARCHAR --
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 ROLLBACK TRANSACTION
END CATCH

In the second UPDATE statement, Age property is expecting an INT value but here it's a VARCHAR. As a result, the transaction is not executed successfully, and the execution context jumps to the CATCH block. This safeguards data integrity by preventing the execution of partial or incorrect transactions.

ROLLBACK TRANSACTION is used to roll back all the set of transactions that execute inside a BEGIN-COMMIT TRANSACTION block.

Side note:

For testing purposes, I use SQL commands with BEGIN TRANSACTION as shown in the example below.

BEGIN TRANSACTION 
UPDATE Employees SET Age = 56 where EmployeeId = 1
UPDATE Employees SET Age = 45 where EmployeeId = 2

BEGIN TRANSACTION
UPDATE Departments SET DepartmentName = 'IT DEPT' where DepartmentId = 8

Without executing COMMIT TRANSACTION I can run any number of SQL commands. Then simply Run ROLLBACK or ROLLBACK TRANSACTION to revert all the changes that I have made.

The ROLLBACK command cannot be used without using BEGIN TRANSACTION. It produces the following error.

MSSQL Concurrency Problems & Isolation Levels — Part 2