Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How to Set Isolation Level in SQL Server – Querychat

Isolation is one of the listed ACID properties. It refers to the extent to which the data can go wrong for the sake of database performance in concurrent transactions. This article explains the various isolation levels in SQL Server database and the syntax that we can use to configure the transaction isolation level to avoid concurrency issues. 

Concurrency Issues

We can find various types of issue in concurrent transactions where data can be accessed and modified by lots of users simultaneously. Those issues are:

  • Dirty reads, where we read uncommitted data. These happen when a transaction is updating a row, and there is a second transaction that reads the row before the first one is committed. The second transaction read the uncommitted changes, even though the data in the first transaction are never committed to the database.
  • Non-repeatable reads. These occur when one transaction is updating data, and a second one is reading the same data while the update is in progress. The data retrieved before the update will not match data retrieved after the update.
  • Phantom reads. These occur when a transaction issues two reads, and between the two reads, the underlying data is updated with insertion or deletion. The update causes the results of each query to differ. Rows returned in one query but did not appear in the other are called phantom rows.
  • Lost updates. These occur when two transactions update the same value. The last transaction will win, and the first one is lost.

SQL Server isolation levels use locking mechanisms to avoid concurrency issues such as dirty reads, non-repeatable reads, phantom reads, and lost updates. The locking mechanisms control the competing activity of simultaneous transactions. 

Syntax

Following is the syntax to set the transaction isolation level in SQL Server database.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE }

Isolation Level

Let’s take a look at the following table describing various isolation levels in SQL Server in more detail. We need to understand each option to be able to set the proper isolation level for our database because it can affect the database performance. 

Available isolation levels in SQL ServerTable 1. Available isolation levels in SQL Server.

Example

The following steps show how to set the isolation level of a transaction to SERIALIZABLE and how it makes another transaction unable to update the locked data. We will use AdventureWorks2017 sample database for this demonstration.

Step 1: Open two query windows and set the connection to AdventureWorks2017 database.

Step 2: In the first query window, type the following T-SQL commands:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
GO

BEGIN TRAN
SELECT TOP 5 * FROM Person.Person ORDER BY BusinessEntityID

Step 3:  In the second query window, type the following command to update the rows selected in step 2. 

UPDATE Person.Person 
SET FirstName = UPPER(FirstName)
WHERE BusinessEntityID IN (SELECT TOP 5 BusinessEntityID 
FROM Person.Person 
ORDER BY BusinessEntityID)

Step 4: Execute the command in the second query. We will see that it is not executed right away because the first five rows are locked.

An UPDATE transaction is waiting because the rows are locked

Figure 1. An UPDATE transaction is waiting because the rows are locked.

Step 5: Switch back to the first query window and execute this command:

COMMIT TRAN

Step 6: Then, switch back to our second query window. We will see that the update command was executed after we did COMMIT in the first query window to release the lock on the result set.

An UPDATE transaction was successfully executedFigure 2. An UPDATE transaction was successfully executed.

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc