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 LevelDirty ReadsLost UpdateNonrepeatable ReadsPhantom 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.