Transactions Locks «Prev  Next»

Lesson 4 Isolation levels
Objective Define isolation levels for transactions

Defining Transaction Isolation Levels in Microsoft SQL Server 2019

As a SQL Server Database Administrator, understanding and defining transaction isolation levels is paramount to ensure data integrity, consistency, and optimal performance of your database system. Microsoft SQL Server 2019 provides a spectrum of isolation levels, each designed to balance the trade-offs between consistency and concurrency. This guide delves into the intricacies of defining and managing these isolation levels, equipping you with the knowledge to make informed decisions tailored to your specific database environment.
  1. Understanding Transaction Isolation Levels: Transaction isolation levels dictate how transactions interact with each other, particularly in terms of visibility to uncommitted changes. They are crucial in managing the phenomena of Dirty Reads, Non-Repeatable Reads, and Phantom Reads, ensuring that transactions operate with the desired level of isolation from each other.
  2. The Spectrum of Isolation Levels in SQL Server 2019: SQL Server 2019 offers the following isolation levels:
    • Read Uncommitted: The lowest level of isolation. Transactions can read uncommitted changes made by other transactions, leading to Dirty Reads.
    • Read Committed: The default isolation level. Transactions cannot read uncommitted changes but are still susceptible to Non-Repeatable Reads.
    • Repeatable Read: Transactions are protected from Dirty Reads and Non-Repeatable Reads, but Phantom Reads can still occur.
    • Serializable: The highest level of isolation. Transactions are completely isolated from one another, preventing Dirty Reads, Non-Repeatable Reads, and Phantom Reads.
    • Snapshot: Transactions operate on a snapshot of the data as it existed at the beginning of the transaction, providing consistent reads without locking.
  3. Setting the Transaction Isolation Level: To define the isolation level for a transaction in SQL Server, use the `SET TRANSACTION ISOLATION LEVEL` statement. 
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;
-- Your SQL commands go here.
COMMIT TRANSACTION;

    In this example, the isolation level is set to `REPEATABLE READ`, ensuring that once a row is read within the transaction, no other transactions can modify it until the transaction is committed.
  4. Managing Concurrency and Performance: Understanding the trade-offs of each isolation level is crucial. Higher isolation levels, while ensuring data consistency, can lead to increased locking and reduced concurrency, potentially impacting performance. Conversely, lower isolation levels improve concurrency but at the cost of potential data anomalies.
  5. Utilizing Snapshot Isolation: Snapshot isolation leverages row versioning to provide consistent reads without acquiring locks. To enable snapshot isolation, run the following commands: 
    ALTER DATABASE YourDatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE YourDatabaseName
SET READ_COMMITTED_SNAPSHOT ON;

    After enabling, you can set your transaction isolation level to `SNAPSHOT`. 
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;
-- Your SQL commands go here.
COMMIT TRANSACTION;
  6. Monitoring and Analysis: Utilize SQL Server’s comprehensive monitoring tools to analyze the impact of different isolation levels on your system’s performance. Monitor lock waits, deadlocks, and transaction duration to ensure that the chosen isolation level is meeting your system’s requirements.

In SQL Server 2019, adeptly defining and managing transaction isolation levels is a critical responsibility of a Database Administrator. It demands a nuanced understanding of the trade-offs between consistency and concurrency, a meticulous approach to balance performance with data integrity. Armed with this knowledge, you are now well-equipped to make informed decisions, ensuring the robustness and efficiency of your SQL Server database environments.



An isolation level is a term that defines how effectively one transaction is isolated from another.
You can control the isolation level in MS SQL Server 2019. There are four possible isolation levels:
  1. Read Uncommitted: Allows a transaction to read data from the database, whether it has been committed or not. This is the lowest level of isolation.
  2. Read Committed: Allows a transaction to read data from the database, but only data that has been committed. This ensures that no data will be read by one transaction while another transaction is in the process of updating. This is the SQL Server default isolation level. It is possible for an update to be lost at this level if two transactions modify exactly the same row, but only if each transaction bases the modification on the values originally retrieved in the transaction.
  3. Repeatable Read: Similar to Read Committed, except that SQL Server ensures that if a transaction re-queries the same data, no other transaction, whether committed or not, will negatively affect being able to repeat reading the exact same values. This is because no other transaction can modify data that is contained within your transaction. On the other hand, new rows can be added to the database. This level of isolation only protects existing data. If another transaction inserts new records, these are called phantom records.
  4. Serializable: Similar to Repeatable Read, except that this level of isolation protects not only existing data, but no new rows can be added either. Every statement in the transaction occurs serially (or one after another), hence the name. This isolation level prevents phantom records. This is the highest level of isolation.

Changing the Isolation Level

You change the isolation level by issuing one of the following four Transact-SQL statements:
  1. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  2. SET TRANSACTION ISOLATION LEVEL READ COMMITTED
  3. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
  4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Using the SET TRANSACTION ISOLATION LEVEL Transact-SQL statement changes the isolation level for all select statements within a transaction for the session. Therefore, the isolation level remains set until either the connection is terminated or the isolation level is changed again.
The next lesson discusses nested transactions.

How It Works

When you executed the BEGIN TRAN statement, you began writing your work into the transaction log but not into the database. Starting when you begin a transaction, your changes are isolated (within parameters) from other users. What you are doing is visible to you only, and is not really done until you COMMIT. After you execute the UPDATE statement, you can still query the database as if your change was complete, but only you can see that result. Anyone else attempting to look will get results based on their TRANSACTION ISOLATION LEVEL They could have to wait for your lock to be released, they could see the old value, or if they are daring they could get a dirty read. Once you ROLLBACK, the database reverts to a state as if you had never started (and woe to the user who got a dirty read).

SEMrush Software