Concurrency Problems in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Concurrency Problems in SQL Server with Examples

In this article, I am going to discuss the Concurrency Problems in SQL Server with Examples. Please read our Transactions in SQL Server article before proceeding to this article. As part of this article, I am going to discuss the following things

1. What is Concurrency in SQL Server?
2. Different types of Concurrency Problems occurred in SQL  Server
3. How to Overcome the Concurrency Problems in SQL Server?
4. Understanding the different transaction isolation levels provided by SQL Server

What is Concurrency in SQL Server?

When we talk about transactions, one more thing which we need to handle is concurrency. Concurrency is nothing but is a situation where two users are trying to access the same information and while they are accessing the same information we do not want any kind of inconsistency result or abnormal behavior.

Understanding Concurrency with an example:

We are going to use the following Customer table to understand the concurrency in SQL Server. If you look at the table, currently the customer whose id 1 is having the customer code Code_1. Similarly, the customer whose id 2 is having the customer code Code_2, and so on.

Please use the below SQL Script to create the Customer table with the required sample data.

-- Create Customer Table
CREATE TABLE Customer
(
    CustomerID INT PRIMARY KEY,
    CustomerCode VARCHAR(10),
    CustomerName VARCHAR(50)
)
GO

-- Insert data into Customer Table
INSERT INTO Customer VALUES (1, 'Code_1', 'Ramesh')
INSERT INTO Customer VALUES (2, 'Code_2', 'Suresh')
INSERT INTO Customer VALUES (3, 'Code_3', 'David')
INSERT INTO Customer VALUES (4, 'Code_4', 'John')

Understand the SQL Server Concurrency:

Please have a look at the following image to understand the Concurrency in SQL Server. Currently, the customer whose id 1 is having the customer code Code_1. Let say we have two transactions (Transaction 1 and Transaction 2) and both the transaction read the Customer Code value of the same customer i.e. Customer id = 1. The first transaction then updates the customer code to Code_101 and then doing some other works for 10 seconds. Immediately, transaction 2 read the customer code and it found the updated value Code_101. After 10 seconds, the first transaction again updated the value to Code_1101. Meantime the second transaction read the value again and it found the updated value i.e. Code_1101. Finally, transaction 1 is rollback and it updates the Customer Code value to Code_1. And then transaction 2 read the value and found the value as Code_1. So, this will be confusing for transaction 2. Every time he reads the data he gets a new value.

So, what we want here is, when we are doing this kind of update operation, then we don’t want the end-user to see the inconsistent data i.e. the uncommitted data. We want the end-user to see only the committed and consistent data. We are getting above the concurrency problems because two transactions are accessing the same data at the same time.

As we know the Databases are powerful systems and they are potentially used by many users or many applications at the same time. So, allowing concurrent transactions is essential from the performance point of view but allowing concurrent transactions may also introduce some concurrency issues when two or more transactions are working with the same data at the same time.

Different Types of Concurrency Problems in SQL Server:

The common concurrency problems that we get in SQL Server are as follows

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

We will discuss what all these concurrency problems are in SQL Server and when these concurrency problems have occurred in detail with real-time examples from our next article.

One of the ways to solve all of the above concurrency problems is by allowing only one user to execute, only one transaction at any given point in time. If you do so, then imagine what could happen if you have a very large database system with several numbers of users who want to execute several transactions. Then all the transactions get queued and they may have to wait for a very long period of time before they could actually get a chance to execute their transactions. As a result, you will get very poor performance and the whole purpose of having a powerful database system is defeated if you solve the concurrency problems in this way. 

At this point you might be thinking, to achieve the best performance let’s allow all transactions of all users to execute concurrently. If you do so, then the problem is that you will get all sorts of concurrency problems (i.e. Dirty Reads, Lost Updates, Non-repeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.

How to Overcome the Concurrency Problems in SQL Server?

To overcome the above Concurrency Problems, SQL Server provides different types of Transaction Isolation Levels, to balance the concurrency problems and performance depending on our application’s need. The 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 Transaction Isolation Level you choose for your transaction, you will get varying degrees of performance and concurrency problems. The following table shows the list of isolation levels along with the concurrency problems.

If you choose the lowest isolation level (i.e. Read Uncommitted), then it increases the number of concurrent transactions that can be executed at the same time, but the downside is that you may get all sorts of concurrency problems. On the other hand, if you choose the highest isolation level (i.e. Serializable or Snapshot), then you will not get any concurrency problems, but the downside is that it will reduce the number of concurrent transactions that can be executed at the same time if those transactions are working with the same data.

In the next article, I am going to discuss the Dirty Read Concurrency Problem in SQL Server with examples. Here, in this article, I try to explain the basics of Concurrency Problems in SQL Server. I hope you enjoy this article.