SQL Server Concurrent Transactions

SQL Server Concurrent Transactions

In this article, I am going to discuss the basics of SQL Server Concurrent Transactions with examples. As part of this article, I am going to discuss the following pointers

  1. What is a transaction in SQL Server?
  2. The problems that might occur when the transactions are run concurrently
  3. What are the different transaction isolation levels provided by SQL Server to solve the concurrency problems?

I strongly recommended you to read the following article before proceeding to this article where we discussed the Transaction Management in SQL Server.

https://dotnettutorials.net/lesson/transaction-management-in-sql-server/

What is a transaction in SQL Server?

A transaction is a unit of work or we can consider a transaction in SQL Server as a set of SQL statement such as INSERT, UPDATE and DELETE which should be executed as one unit.

A transaction ensures that either all of the SQL statements executed successfully or none of them. That means if one of the statements in the transaction fails, then all of the commands fail and any data that was modified by the transaction in the database is rolled back. In this way, a transaction in SQL Server maintains the consistency and integrity of data in a database.

Let us understand this with an example. We are going to use the following Accounts table.

SQL Server Concurrent Transactions


Please use below SQL Script to create and populate the Accounts table with the required data.

-- Create the Accounts table
CREATE TABLE Accounts
(
  ID INT PRIMARY KEY,
  Name VARCHAR(100),
  Balance INT
)

-- Insert the following data
INSERT INTO Accounts VALUES(1001, 'Anurag', 10000)
INSERT INTO Accounts VALUES(1002, 'Sambit', 20000)
Example: Transfer $100 from Anurag to Sambit Account

The following is an example of a transaction which ensures that either both of the UPDATE commands succeed or both of them fail if there is a problem with one of the UPDATE command. 

-- Transfer $100 from Anurag to Sambit Account
BEGIN TRY
    BEGIN TRANSACTION
         UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1001
         UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 1002
    COMMIT TRANSACTION
    PRINT 'Transaction Committed'
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'Transaction Rolled back'
END CATCH

As we know the Databases are powerful systems and are used by many applications and many users at the same point of time. From a performance point of view, it very essential to allow concurrent transactions. But the problem is that, when we allow concurrent transactions, then we may get some concurrency issues when two or more transactions are working with the same data at the same time. The concurrency issues that we may get are as follows when allowing concurrent transactions in SQL Server.

  1. Dirty Reads
  2. Lost Updates
  3. Non-repeatable Reads
  4. Phantom Reads

From our next article, we will discuss what these concurrency problems are and when these concurrency problems have occurred in detail with real-time examples.

How to Solve the Concurrency Problems?

If you want the solve all the above Concurrency Issues then you may be thinking that just allow only one user to execute, only one transaction at any given point of time. If you do so, then you just imagine what could happen, if your database is a big one and your database having several numbers of users who want to execute several transactions at the same time. Then all the transactions get queued and the users have to wait a very long period of time to get a chance to execute their transactions. As a result, you will get very poor performance if you solve the concurrency problems in this way.

In order to achieve the best performance, at this point, you may be thinking that let’s allow all the transactions of all users to execute concurrently. If you do so, then definitely you will get the best performance. But the problem is that you may get all the concurrency problems such as Dirty Reads, Nonrepeatable Reads, Lost Updates, and Phantom Reads when two or more transactions going to work with the same data at the same time.

So to overcome the above two problems (i.e. Performance and Concurrency Issues), the SQL Server provides us with different types of transaction isolation level which we can use to balance the performance and concurrency issues as per our applications need.

What are the different Transaction Isolation Levels Provided by SQL Server?

The different Transaction Isolation Levels provided by SQL Server are as follows

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

Depending upon the isolation level we choose for our transaction, we will get varying degrees of performance and concurrency problems. The following table shows the list of isolation levels along with the concurrency problems.

SQL Server Concurrent Transactions

As shown in the above image, if you choose the lowest isolation level i.e. Read Uncommitted, then it increases the number of concurrent transactions to be executed at the same point of time. As a result, you will get the best performance. But the problem is that you may get all concurrency problems. On the other hand, if you choose the highest transaction isolation level i.e. Serializable, then you will not get any concurrency problems. But the downside is that it will reduce the number of concurrent transactions to be executed at the same point of time. 

So what transaction Isolation Level you need to use that totally depends on your application’s need. In the next article, I am going to discuss the Dirty Read Concurrency Problem in SQL Server with examples.

SUMMARY

In this article, I try to explain the basics of SQL Server Concurrent Transactions. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *