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 will 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 will discuss the following pointers in detail.

1. What is Concurrency in SQL Server?
2. Different types of Concurrency Problems occurred in SQL  Server
3. How Do We Overcome the Concurrency Problems in an SQL Server?
4. What are the Different Transaction Isolation Levels Provided by SQL Server?

What is Concurrency in SQL Server?

When we talk about transactions, one more thing that we need to handle is concurrency. Concurrency is a situation where two users or transactions are trying to access the same information. While they access the same information, we do not want any inconsistent results or abnormal behavior.

Concurrency in SQL Server refers to the ability of the database system to handle multiple operations or transactions simultaneously without causing inconsistencies or conflicts in the data. It is a fundamental concept of database management systems, aiming to maximize database performance and ensure data integrity when multiple users or applications access and modify the data at the same time.

Understanding Concurrency with an example:

We will use the following Customer table to understand Concurrency in SQL Server. If you look at the table, the customer whose ID 1 currently has the customer code Code_1. Similarly, the customer whose ID 2 has the customer code Code_2, and so on.

Please use the SQL script below 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 has the customer code Code_1. Let’s say we have two transactions (Transaction 1 and Transaction 2), and both transactions 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 does some other work for 10 seconds. Immediately, transaction 2 read the customer code and found the updated value Code_101. After 10 seconds, the first transaction again updated the value to Code_1101. In the meantime, the second transaction reread the value, and it found the updated value, i.e., Code_1101. Finally, transaction 1 is rolled back, and the Customer Code value is updated to Code_1. 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 that when we do this kind of update operation, 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 the above concurrency problems because two transactions are accessing the same data at the same time.

As we know, databases are powerful systems, and many users or applications potentially use them 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 work 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. Lost Updates: Occurs when two or more transactions retrieve the same row for updating, and the last transaction to commit overwrites changes made by the others, causing those changes to be lost. This typically happens when transactions read a value, make changes based on that value, and then write the result back, all without locking the resource during the entire operation.
2. Dirty Reads: This happens when a transaction reads data that has not yet been committed by another transaction. If the other transaction rolls back it changes or updates the data again. The first transaction will have read data that is considered never to have existed, leading to potential inconsistencies.
3. Non-repeatable Reads: This occurs when a transaction reads the same row twice and gets a different value each time because another transaction has updated the row in between the two reads. This can lead to inconsistencies within the same transaction because the same query yields different results at different times.
4. Phantom Reads: Similar to non-repeatable reads, but in this case, the second read returns a set of rows that includes rows that were not visible in the first read due to insertion by another transaction. This can affect operations like reports or calculations that expect a consistent set of data across reads.

We will discuss all these concurrency problems 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 can 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 will be defeated if you solve the concurrency problems in this way. 

At this point, you might think, to achieve the best performance, let’s allow all users’ transactions to execute concurrently. If you do so, 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 simultaneously.

How Do We Overcome the Concurrency Problems in SQL Server?

To overcome the above Concurrency Problems, the SQL Server provides different types of Transaction Isolation Levels to balance concurrency problems and performance, depending on the application’s needs. The Transaction Isolation Levels provided by SQL Server are as follows.

1. Read Uncommitted: Allows dirty reads, but no locking is issued, so performance is not degraded. This level does not prevent any of the concurrency problems mentioned.
2. Read Committed: The default isolation level. It uses shared locks to prevent dirty reads but does not protect against Lost Update, Non-Repeatable reads, or Phantom Reads.
3. Repeatable Read: Prevents Dirty Reads, Non-Repeatable Reads, and Lost Updates by placing locks on all data that is read during a transaction, ensuring that rows cannot be modified or deleted by other transactions until the current transaction completes. This level does not prevent the Phantom Read concurrency problem.
4. Serializable: The highest isolation level, which prevents dirty reads, non-repeatable reads, phantom reads, and lost updates by performing range locking. This ensures that no other transactions can insert rows that would qualify for the queries of the current transaction.
5. Snapshot: Provides a view of the database as it existed at the start of the transaction, effectively avoiding dirty reads, non-repeatable reads, and phantom reads without acquiring locks on read operations, thus offering high concurrency.

Depending upon the Transaction Isolation Level you choose for your transaction, you will experience varying degrees of performance and concurrency problems. The following table shows the list of isolation levels and concurrency problems.

Suppose you choose the lowest isolation level (i.e., Read Uncommitted). In that case, 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. However, 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.

So, choosing the appropriate isolation level requires balancing the need for accuracy and consistency in data against the performance implications of the various locking strategies. Understanding these concurrency problems and how to manage them is essential for developing efficient and reliable database applications.

In the next article, I will discuss the Dirty Read Concurrency Problem in SQL Server with examples. In this article, I will try to explain the basics of concurrency problems in SQL servers. I hope you enjoy this article.