SQL Talk

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

The isolation level that you choose for your transaction, defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Depending on the isolation level you have chosen you get varying degrees of performance and concurrency problems. The table here has the list of isoltaion levels along with concurrency side effects.

Isolation Level	Dirty Reads	Lost Update	Nonrepeatable Reads	Phantom Reads
Read Uncommitted	Yes	Yes	Yes	Yes
Read Committed	No	Yes	Yes	Yes
Repeatable Read	No	No	No	Yes
Snapshot	No	No	No	No
Serializable	No	No	No	No

If you choose the lowest isolation level (i.e Read Uncommitted), it increases the number of concurrent transactions that can be executed at the same time, but the down side is you have all sorts of concurrency issues. On the other hand if you choose the highest isolation level (i.e Serializable), you will have no concurrency side effects, but the downside is that, this will reduce the number of concurrent transactions that can be executed at the same time if those transactions work with same data.

These 5 isolation levels work on two major concurrency models:

1. Pessimistic model - In the pessimistic model of managing concurrent data access, the readers can block writers, and the writers can block readers.
2. Optimistic model - In the optimistic model of managing concurrent data access, the readers cannot block writers, and the writers cannot block readers, but the writer can block another writer.

Note that readers are users are performing the SELECT operations. Writers are users are performing INSERT, ALTER, UPDATE, S.E.T. operations.

Out of the five isolation levels, Read Uncommitted, Read Committed, Repeatable Read, and Serializable come under the pessimistic concurrency model. Snapshot comes under the optimistic concurrency model. These levels are ordered in terms of the separation of work by two different processes, from minimal separation to maximal.

Let's look at each of these isolation levels and how they affect concurrency of operations.

Read Uncommitted

This is the first level of isolation, and it comes under the pessimistic model of concurrency. In Read Uncommitted, one transaction is allowed to read the data that is about to be changed by the commit of another process. Read Uncommitted allows the dirty read problem.

When this level is set, the transaction can read uncommitted data resulting in the Dirty Read problem. With this isolation level, we allow a transaction to read the data which is being updated by other transaction and not yet committed. Suppose User A is trying to read a row which is being updated by User B. Here, we are allowing User A to read the un-updated/uncommitted data i.e old data.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. READ UNCOMMITTED  
3. BEGIN TRANSACTION MyTransaction  
4. BEGIN TRY  
5. UPDATE Account SET Debit=100 WHERE Name='John Cena'  
6. UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'  
7. COMMIT TRANSACTION MyTransaction  
8. PRINT 'TRANSACTION SUCCESS'  
9. END TRY  
10. BEGIN CATCH  
11. ROLLBACK TRANSACTION MyTransaction  
12. PRINT 'TRANSACTION FAILED'  
13. END CATCH  

Read Committed

This is the second level of isolation and also falls under the pessimistic model of concurrency. In the Read Committed isolation level, we are only allowed to read data that is committed, which means this level eliminates the dirty read problem. In this level, if you are reading data then the concurrent transactions that can delete or write data, some work is blocked until other work is complete.

1. This prevents Dirty Read. When this level is set, the transaction can not read the data that is being modified by the current transaction. This will force user to wait for the current transaction to finish up its job. Suppose User A is trying to read a row which is being updated by User B. Here, we are asking User A to wait for the User B to finish its update task, and giving the updated/correct data to User A. But the problem with this level is - it can't resolve Phantom Read or Inconsistency Analysis i.e it asks User A to wait for Read but not for update or insert.
   
   Example
   
   1. SET TRANSACTION ISOLATION LEVEL   
   2. READ COMMITTED  
   3. BEGIN TRANSACTION MyTransaction  
   4. BEGIN TRY  
   5. UPDATE Account SET Debit=100 WHERE Name='John Cena'  
   6. UPDATE ContactInformation SET Mobile='1234567890' WHERE Name='The Rock'  
   7. COMMIT TRANSACTION MyTransaction  
   8. PRINT 'TRANSACTION SUCCESS'  
   9. END TRY  
   10. BEGIN CATCH  
   11. ROLLBACK TRANSACTION MyTransaction  
   12. PRINT 'TRANSACTION FAILED'  
   13. END CATCH  

Repeatable Read

The Repeatable Read isolation level is similar to the Read Committed level and eliminates the Non-Repeatable Read problem. In this level, the transaction has to wait till another transaction's update or read query is complete. But if there is an insert transaction, it does not wait for anyone. This can lead to the Phantom Read problem.

This level does every work that Read Committed does. but it has one additional benefit. User A will wait for the transaction being executed by User B to execute it's Update query as well, like Read Query. But Insert query doesn't wait, this also creates Phantom Read problem.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. REPEATABLE READ 

Serializable

This is the highest level of isolation in the pessimistic model. By implementing this level of isolation, we can prevent the Phantom Read problem. In this level of isolation, we can ask any transaction to wait until the current transaction completes.

This is the maximum level of Isolation level provided by SQL Server transaction. We can prevent Phantom Read problem by implementing this level of isolation. It asks User A to wait for the current transaction for any kind of operation he wants to perform.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. SERIALIZABLE  

Snapshot

Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its copy of data, so if a user tries to perform a transaction like an update or insert, it asks him to re-verify all the operation before the process gets started executing.

This level takes a snapshot of current data. Every transaction works on its own copy of data. When User A tries to update or insert or read anything, we ask him to re-verify the table row once again from the starting time of its execution, so that he can work on fresh data. with this level. We are not giving full faith to User A that he is going to work on fresh data but giving high-level changes of data integrity.

Example

1. SET TRANSACTION ISOLATION LEVEL   
2. SNAPSHOT  

Note: Isolation level also has a problem called "Dead Lock"- "Both the transactions lock the object and waits for each other to finish up the job". DeadLock is very dangerous because it decreases the concurrency and availability of database and the database object.

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.

Databases are powerful systems and are potentially used by many users or applications at the same time. Allowing concurrent transactions is essential for performance but may introduce concurrency issues when two or more transactions are working with the same data at the same time.

Some of the common concurrency problems

• Dirty Reads
• Lost Updates
• Nonrepeatable Reads
• Phantom Reads

One way to solve all these concurrency problems is by allowing only one user to execute, only one transaction at any point in time. Imagine what could happen if you have a large database with several users who want to execute several transactions. All the transactions get queued and they may have to wait a long time before they could get a chance to execute their transactions. So you are getting poor performance and the whole purpose of having a powerful database system is defeated if you serialize access this way.

At this point you might be thinking, for best performance let us allow all transactions to execute concurrently. The problem with this approach is that it may cause all sorts of concurrency problems (i.e Dirty Reads, Lost Updates, Nonrepeatable Reads, Phantom Reads) if two or more transactions work with the same data at the same time.

1. Dirty Reads

A dirty read happens when one transaction is permitted to read data that has been modified by another transaction that has not yet been committed. In most cases this would not cause a problem. However, if the first transaction is rolled back after the second reads the data, the second transaction has dirty data that does not exist anymore.

Dirty Read Example : In the example below, Transaction 1, updates the value of ItemsInStock to 9. Then it starts to bill the customer. While Transaction 1 is still in progress, Transaction 2 starts and reads ItemsInStock value which is 9 at the moment. At this point, Transaction 1 fails because of insufficient funds and is rolled back. The ItemsInStock is reverted to the original value of 10, but Transaction 2 is working with a different value (i.e 10).



Transaction 1 : 
Begin Tran

Update tblInventory set ItemsInStock = 9 where Id=1

-- Billing the customer

Waitfor Delay '00:00:15'

-- Insufficient Funds. Rollback transaction

Rollback Transaction

Transaction 2 :

Set Transaction Isolation Level Read Uncommitted

Select * from tblInventory where Id=1

Read Uncommitted transaction isolation level is the only isolation level that has dirty read side effect. This is the least restrictive of all the isolation levels. When this transaction isolation level is set, it is possible to read uncommitted or dirty data. Another option to read dirty data is by using NOLOCK table hint. The query below is equivalent to the query in Transaction 2.

Select * from tblInventory (NOLOCK) where Id=1

2. Lost Updates

Lost update problem happens when 2 transactions read and update the same data. Let's understand this with an example. We will use the following table tblInventory for this example.



As you can see in the diagram below there are 2 transactions - Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 iPhone. It sees ItemsInStock as 10.

At this time Transaction 2 is processing another order for 2 iPhones. It also sees ItemsInStock as 10. Transaction 2 makes the sale first and updates ItemsInStock with a value of 8.

At this point Transaction 1 completes the sale and silently overwrites the update of Transaction 2. As Transaction 1 sold 1 iPhone it has updated ItemsInStock to 9, while it actually should have updated it to 7.



Example : The lost update problem example. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Transaction 1 is processing an order for 1 iPhone, while Transaction 2 is processing an order for 2 iPhones. At the end of both the transactions ItemsInStock must be 7, but we have a value of 9. This is because Transaction 1 silently overwrites the update of Transaction 2. This is called the lost update problem.

-- Transaction 1

Begin Tran

Declare @ItemsInStock int

Select @ItemsInStock = ItemsInStock

from tblInventory where Id=1

-- Transaction takes 10 seconds

Waitfor Delay '00:00:10'

Set @ItemsInStock = @ItemsInStock - 1

Update tblInventory

Set ItemsInStock = @ItemsInStock where Id=1

Print @ItemsInStock

Commit Transaction

-- Transaction 2

Begin Tran

Declare @ItemsInStock int

Select @ItemsInStock = ItemsInStock

from tblInventory where Id=1

-- Transaction takes 1 second

Waitfor Delay '00:00:1'

Set @ItemsInStock = @ItemsInStock - 2

Update tblInventory

Set ItemsInStock = @ItemsInStock where Id=1

Print @ItemsInStock

Commit Transaction

Both Read Uncommitted and Read Committed transaction isolation levels have the lost update side effect. Repeatable Read, Snapshot, and Serializable isolation levels does not have this side effect. If you run the above Transactions using any of the higher isolation levels (Repeatable Read, Snapshot, or Serializable) you will not have lost update problem. The repeatable read isolation level uses additional locking on rows that are read by the current transaction, and prevents them from being updated or deleted elsewhere. This solves the lost update problem.



For both the above transactions, set Repeatable Read Isolation Level. Run Transaction 1 first and then a few seconds later run Transaction 2. Transaction 1 completes successfully, but Transaction 2 competes with the following error.
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

3. Nonrepeatable Reads
Non repeatable read problem happens when one transaction reads the same data twice and another transaction updates that data in between the first and second read of transaction one. 

We will use the following table tblInventory in this demo


The following diagram explains the problem : Transaction 1 starts first. Reads ItemsInStock. Gets a value of 10 for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and UpdatesItemsInStock to 5. Transaction 1 then makes a second read. At this point Transaction 1 gets a value of 5, reulting in non-repeatable read problem.



Non-repeatable read example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Notice that when Transaction 1 completes, it gets different values for read 1 and read 2, resulting in non-repeatable read.

-- Transaction 1

Begin Transaction

Select ItemsInStock from tblInventory where Id = 1

-- Do Some work

waitfor delay '00:00:10'

Select ItemsInStock from tblInventory where Id = 1

Commit Transaction

-- Transaction 2

Update tblInventory set ItemsInStock = 5 where Id = 1

Repeatable read or any other higher isolation level should solve the non-repeatable read problem.


Fixing non repeatable read concurrency problem : To fix the non-repeatable read problem, set transaction isolation level of Transaction 1 to repeatable read. This will ensure that the data that Transaction 1 has read, will be prevented from being updated or deleted elsewhere. This solves the non-repeatable read problem. 

When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same value for ItemsInStock.

-- Transaction 1

Set transaction isolation level repeatable read

Begin Transaction

Select ItemsInStock from tblInventory where Id = 1

-- Do Some work

waitfor delay '00:00:10'

Select ItemsInStock from tblInventory where Id = 1

Commit Transaction

-- Transaction 2

Update tblInventory set ItemsInStock = 5 where Id = 1

4. Phantom Reads

Phantom read happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new row that matches the WHERE clause of the query executed by the first transaction.

We will use the following table tblEmployees in this demo

Scrip to create the table tblEmployees

Create table tblEmployees

(

    Id int primary key,

    Name nvarchar(50)

)

Go

Insert into tblEmployees values(1,'Mark')

Insert into tblEmployees values(3, 'Sara')

Insert into tblEmployees values(100, 'Mary')

The following diagram explains the problem : Transaction 1 starts first. Reads from Emp table where Id between 1 and 3. 2 rows retrieved for first read. Transaction 1 is doing some work and at this point Transaction 2 starts and inserts a new employee with Id = 2. Transaction 1 then makes a second read. 3 rows retrieved for second read, reulting in phantom read problem.



Phantom read example : Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Notice that when Transaction 1 completes, it gets different number of rows for read 1 and read 2, resulting in phantom read.

-- Transaction 1

Begin Transaction

Select * from tblEmployees where Id between 1 and 3

-- Do Some work

waitfor delay '00:00:10'

Select * from tblEmployees where Id between 1 and 3

Commit Transaction

-- Transaction 2

Insert into tblEmployees values(2, 'Marcus')

Serializable or any other higher isolation level should solve the phantom read problem.


Fixing phantom read concurrency problem : To fix the phantom read problem, set transaction isolation level of Transaction 1 to serializable. This will place a range lock on the rows between 1 and 3, which prevents any other transaction from inserting new rows with in that range. This solves the phantom read problem. 

When you execute Transaction 1 and 2 from 2 different instances of SQL Server management studio, Transaction 2 is blocked until Transaction 1 completes and at the end of Transaction 1, both the reads get the same number of rows.

-- Transaction 1

Set transaction isolation level serializable

Begin Transaction

Select * from tblEmployees where Id between 1 and 3

-- Do Some work

waitfor delay '00:00:10'

Select * from tblEmployees where Id between 1 and 3

Commit Transaction

-- Transaction 2

Insert into tblEmployees values(2, 'Marcus')