Apr 16, 2023

Learning About and Implementing Isolation Levels In SQL Server:

Are you looking to boost your SQL Server's concurrency and consistency? SQL Server has your back with various transaction isolation levels. Each level provides a unique balance between consistency and concurrency, making SQL Server's job of managing concurrency a whole lot easier. Wondering which level is the right fit for your needs? We've got your back with a brief overview of each isolation level. And if you're looking for more detailed examples using SQL Server, keep reading.

Default Isolation Level and NOLOCK

The default isolation level in SQL Server is "Read Committed" isolation level. This means that by default, SQL Server will use the Read Committed isolation level to control concurrency and ensure data consistency. However, it's important to note that the default isolation level can be changed at the database level or query level using the SET TRANSACTION ISOLATION LEVEL statement. Additionally, some operations such as index creation or bulk loading may temporarily change the isolation level to improve performance. It's important to carefully consider the implications of changing the isolation level and to test any changes thoroughly before implementing them in a production environment.

You can check the current isolation level setting in SQL Server by running the following query:

DBCC USEROPTIONS;

This query will return a result set with various options and settings, including the current transaction isolation level. Look for the "isolation level" option to see the current setting.

What is the difference between setting an SQL Server isolation level and setting Nolock?

Setting an isolation level determines how concurrent transactions interact with each other, including how they acquire locks and read data. The isolation level affects the consistency and concurrency of the data in a database. For example, setting the isolation level to Read Committed means that a transaction can read only committed data and that it will acquire shared locks on the data it reads, which can prevent other transactions from modifying that data until the first transaction releases its locks.

On the other hand, setting NOLOCK is a query hint that tells SQL Server to read data without acquiring locks. It allows a SELECT statement to read data that is currently being modified by another transaction, which can improve performance, but may lead to inconsistent or incorrect results. For example, using the NOLOCK hint with a SELECT statement that reads a table can allow the query to return dirty or uncommitted data, which may not reflect the true state of the database.

Here's an example of how these two concepts can be used in practice:

Suppose you have a table called Customers with the columns ID, Name, and City, and two concurrent transactions, T1 and T2, that modify the data in the table. T1 updates the City column of a particular customer, while T2 reads the City column of the same customer.

If the isolation level of both transactions is set to Read Committed, T1 will acquire an exclusive lock on the row it updates, which will prevent T2 from reading the data until T1 releases its lock. This ensures that T2 reads the updated data and not the old data.

If the isolation level of T2 is set to Read Uncommitted, and the NOLOCK hint is used with the SELECT statement that reads the data, T2 will read the data without acquiring any locks. This means that T2 may read the old, unmodified data while T1 is updating the City column, which can result in inconsistent data.

In summary, isolation levels and the NOLOCK hint are both used to control how concurrent transactions access data in a SQL Server database, but they are used in different ways and have different effects on data consistency and concurrency.

Read uncommitted:

This is the lowest isolation level in SQL Server and allows read dirty data reads, non-repeatable reads, and phantom reads. This means that transactions can read data that has been modified but not yet committed by other transactions, which can result in inconsistent data. You might use this isolation level in situations where data consistency is not critical, and high concurrency is required.

Repeatable read:

This isolation level prevents non-repeatable reads, but phantom reads can still occur. Under this isolation level, locks are placed on all data that is read by a transaction, and other transactions cannot modify the locked data until the first transaction completes. You might use this isolation level in situations where data consistency is important, and you need to prevent non-repeatable reads.

Serializable:

This is the highest isolation level in SQL Server and prevents all three types of anomalies (dirty reads, non-repeatable reads, and phantom reads). Under this isolation level, transactions acquire range locks on all data they read or modify, which prevents other transactions from modifying the same data. You might use this isolation level in situations where data consistency is critical, and you can tolerate a lower degree of concurrency.

Snapshot:

This isolation level is similar to Read committed enable snapshot isolation and optimistic isolation levels, in that it uses a versioning mechanism to maintain multiple versions of each row. However, unlike the Read committed enable snapshot isolation, and the read committed no snapshot only isolation levels, the snapshot isolation level provides a consistent view of the data for the duration of a transaction, without allowing any other transactions to modify the same data. You might use this isolation level in situations where you need a consistent view of the data, but you don't want to use locking-based isolation levels.

Read uncommitted Detail:

Read uncommitted is the lowest isolation level in SQL Server, and it allows transactions to only read only committed data that has been modified but not yet the only committed data used by other transactions. This means that transactions can see "dirty" data, which can result in inconsistent data.

Under Read uncommitted isolation, no locks are placed on data that is read by transactions, which allows transactions to read data without waiting for other transactions to release locks on that data. This can result in higher concurrency but at the cost of data consistency.

As a result, transactions operating under Read uncommitted isolation level can experience non-repeatable reads and phantom reads. A non-repeatable read occurs when a transaction reads the same row twice, but another transaction between the reads has modified the data in the row. A phantom read occurs when a transaction reads a set of rows based on a certain condition, and another transaction inserts or deletes rows that satisfy the same condition before the first transaction completes.

Read uncommitted isolation level is generally not recommended for most applications since it can lead to inconsistent or incorrect data back. However, it might be useful in some scenarios, such as when you need to run ad-hoc queries and do not require accurate results, or when you need to provide temporary access to a specific set of data without affecting other transactions.

For example here is a typical example in a bank. if we have a balance of $50 but have a pending auto deposit of another $50. However at the same instance we walk up to the ATM and view our account, we night get the incorrect result of $50 dollars in our account.

When To Use Read Uncommitted

Read uncommitted read committed isolation level one, or read committed, level is the lowest isolation level in SQL Server and is generally not recommended for most applications since it can lead to inconsistent data. However, there are some situations where it might be useful:

Reporting: When generating reports that do not need to be 100% accurate or where data consistency is not important, Read uncommitted isolation level can be used. In such scenarios, using Read uncommitted can help to improve the performance of the reports by allowing multiple users to read the same data simultaneously.
Data analysis: When performing ad-hoc queries on a database, Read uncommitted isolation level can be useful. Ad-hoc queries are typically one-time queries used to analyze data, and the results don't need to be 100% accurate. In such scenarios, using Read uncommitted isolation level can improve the query performance by allowing multiple users to run queries on sample data simultaneously.
Data migration: When migrating large amounts of data from one a database engine to another, Read uncommitted isolation level can be used to speed up the process. Since data consistency is not critical during migration, using Read uncommitted can allow multiple transactions to read the same data simultaneously, which can speed up the migration process.

In SQL Server, you can set the transaction isolation level to Read uncommitted at the connection level or at the transaction level. Here's previous example of how you can implement Read uncommitted isolation level in SQL Server:

Connection committed isolation level: You can set the connection string transaction isolation level to either Read committed snapshot or uncommitted at the connection level by using the SET TRANSACTION ISOLATION LEVEL command. For example, the following T-SQL code sets the transaction isolation at pooled connection level to Read committed snapshot uncommitted for the current connection:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Transaction level: You can also set the transaction isolation level to Read uncommitted at the transaction level by using the SET TRANSACTION ISOLATION LEVEL command inside a transaction block. For example, the following T-SQL code sets the transaction isolation level to Read uncommitted for a specific transaction:

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Perform some queries or modifications here
COMMIT TRANSACTION

To implement Read uncommitted and isolation levels and level in a query, you can also use the WITH (NOLOCK) query hint. For example, the following T-SQL code uses the WITH (NOLOCK) query hint to select data from the Sales table using the Read uncommitted isolation level:

SELECT * FROM Sales WITH (NOLOCK)

In SQL Server, you cannot set transaction isolation level snapshot below the transaction isolation level to Read uncommitted at the instance or set transaction isolation level snapshot above. Transaction isolation levels are set at the connection, database engine or transaction level, and they apply only to the connection or transaction that they are set on.

However, you can configure the default transaction isolation level for all new connections by using the sp_configure stored procedure. The default transaction isolation level determines the default isolation level for user database name, that is used when a new connection is established to the SQL Server instance.

To set the default transaction isolation level to Read uncommitted, you can execute the following T-SQL command:

sp_configure 'user options', 512
RECONFIGURE

The user options configuration option is used to specify the default transaction isolation level. A value of 512 indicates Read uncommitted isolation as transaction started level. After executing the above command, all new connections to the SQL Server instance will use Read uncommitted isolation level as the default transaction isolation level.

However, changing the default connection string transaction isolation level at the instance level can have unintended consequences, as it can affect all applications and users that connect to the SQL Server instance. It is generally not recommended to change the default connection string transaction isolation level at the instance level, and it's better to set the default isolation level at the same connection string or transaction level as needed.

Repeatable read

Repeatable read is an isolation level in SQL Server that guarantees that any data read during a transaction will be the same if it is read again within the same transaction. This means that if a transaction reads a row and then reads the same row again later in the same transaction, it will see the same data both times, regardless of any changes made by other transactions in the meantime.

In Repeatable read isolation level, shared locks are held on the version store all the data modifications that is read by a transaction, and these locks are held until the transaction is completed. This prevents other transactions from modifying data modifications or deleting the same version store the same version store the data that is being read by the same version store the current transaction, thereby guaranteeing data consistency for subsequent connections.

However, Repeatable read isolation level does not prevent non-repeatable reads or phantom reads. Non-repeatable reads occur when a same row versions is read twice within a transaction, but the second read returns different results because the row was modified by another transaction in the meantime. Phantom reads occur when a transaction reads a row version a set of rows that satisfy a certain condition, and then another transaction inserts or deletes a row that also satisfies the same condition, causing the first transaction to see a row versioning a different set of rows when it reads the row versions with the same condition again.

Repeatable read is set transaction isolation level for snapshot transactions and can be set using the SET TRANSACTION ISOLATION LEVEL command. For example, the following T-SQL code sets the transaction isolation level to Repeatable read:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

It's important to note that Repeatable read isolation level can lead to increased locking and blocking, which can negatively impact the performance of concurrent transactions. It should be used judiciously and only in cases where it is necessary to guarantee repeatable reads within a transaction.

To set the Repeatable read isolation level for a specific transaction, you can use the SET TRANSACTION ISOLATION LEVEL command in your SQL query.

Here is an example of how to set the Repeatable read isolation level in a query:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- your SQL statements here

COMMIT

In this example, the BEGIN TRAN statement starts a new transaction. The SET TRANSACTION ISOLATION LEVEL REPEATABLE READ command sets the transaction isolation level to Repeatable read for the current transaction. You can then execute your SQL statements between the BEGIN TRAN and COMMIT statements. Finally, the COMMIT statement ends the transaction.

It's important to note that the Repeatable read isolation level can lead to increased locking and blocking, which can negatively impact the performance of concurrent transactions. Therefore, you should use it judiciously and only when necessary to guarantee repeatable reads within a transaction.

When Should You Use Repeatable Read

Repeatable Read isolation level can be useful in scenarios where you need to ensure that the data read during a transaction remains consistent throughout the transaction. For example, if you are running a financial transaction that involves multiple reads and writes, you may want to use Repeatable Read isolation level to ensure that the data being read remains consistent throughout the transaction.

In general, Repeatable Read isolation level is suitable when:

You need to ensure that the data being read in a transaction is not modified by other transactions during the transaction.
You need to ensure that the data being read multiple times within a transaction remains consistent throughout the transaction.

However, Repeatable Read isolation level can cause more blocking and deadlocks active transactions, as it holds locks on all rows read until the first transaction itself is completed. This can negatively impact the performance of concurrent transactions.

Therefore, it is important to use Repeatable Read isolation level judiciously and only when necessary. In most cases, Read Committed Snapshot isolation level can provide a good balance between data consistency and performance.

Serializable Isolation Level

Serializable isolation level is the highest level of transaction isolation in SQL Server. It provides the strongest guarantees of data consistency and prevents all concurrency issues, such as dirty reads, non-repeatable reads, and phantom reads.

In Serializable isolation level, each transaction is executed as if it is the only transaction running on the system, even though there may be multiple transactions executing concurrently with one transaction. It ensures that transaction sequence numbers and number of transactions execute in a serializable order, which means that the result of a whole transaction sequence number of numbers concurrent transactions is equivalent to running them one after another in some serial order.

Serializable isolation level works by placing a range lock on all the data that is read during a transaction, preventing other transactions from modifying or inserting data within that range. This ensures that the data read by a transaction remains the same throughout the transaction.

However, because of the locking mechanism used, Serializable isolation level can cause more blocking and deadlocks compared to other isolation levels. This can negatively impact the performance of concurrent transactions.

Serializable isolation level can be set using the SET TRANSACTION ISOLATION LEVEL command. For example:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Serializable isolation level should only be used when it is absolutely necessary to ensure the strongest level of data consistency, and when the potential impact on performance is acceptable.

Serializable isolation level is the highest level of transaction isolation in SQL Server and should only be used when it is absolutely necessary to ensure the strongest level of data consistency. It provides the strongest guarantees of data consistency and prevents all concurrency issues, such as dirty reads, non-repeatable reads, and phantom reads.

You may consider using Serializable isolation level in scenarios where:

You have critical transactions that must be executed with the highest level of data consistency and accuracy, such as financial transactions or healthcare data management.
Your system has a low level of concurrency, and the potential impact on performance due to locking is acceptable.

However, Serializable isolation level can cause more blocking and deadlocks compared to other isolation levels, which can negatively impact the performance of concurrent transactions. Therefore, it is important to use it judiciously and only when necessary.

In most cases, a lower isolation level, such as Read Committed or Repeatable Read, can provide a good balance between data consistency and performance. It is important to carefully consider the requirements of your application before deciding on the appropriate isolation level to use.

Snapshot Isolation level Detail

Snapshot committed snapshot isolation in sql* level is a transaction committed snapshot isolation level used in SQL Server that provides a high level of data consistency while minimizing the potential for blocking and deadlocks caused by locking. It works by allowing transactions to read and write data that has been read committed snapshot isolation by other transactions, but not yet fully the committed data to the database.

In Snapshot isolation, each transaction gets a full row versioning of the data that it reads, and any changes made by other transactions are not visible to it. This means that the row versioning to store the data read by a transaction remains consistent throughout the transaction, even if other transactions modify or insert data into the row versioning the same table.

Snapshot isolation uses row versioning to keep track of changes to the versioned rows of the data. When a transaction reads data from read committed snapshot, it gets the versioned rows of the data that was available at the start of the transaction read committed snapshot. Any changes made to row versions of the data by other transactions after the start of the transaction read committed snapshot are not visible to it.

Snapshot isolation can be enabled at the database level using the ALTER DATABASE command, as follows:

ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

Once enabled, you can set the Snapshot isolation level for a transaction using the SET TRANSACTION ISOLATION LEVEL command, as follows:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

Snapshot isolation is useful in scenarios where you need to have snapshot isolation levels ensure high data consistency while minimizing blocking and deadlocks caused by locking. However, it can increase the overhead of the system, as it requires additional storage to maintain the row of consistent snapshot data versions.

Summary Graphic

Setting A Combination READ COMMITTED & SNAPSHOT Isolation

Read Committed Snapshot Isolation (RCSI) is an only snapshot isolation at level available in SQL Server that provides a higher level of concurrency than traditional Read Committed snapshot isolation in sql*. RCSI allows transactions to read data that has been modified by other transactions, even before those transactions have been read committed back to the database. This is achieved by maintaining a snapshot of the data as it existed at the start of the transaction, and allowing the transaction to read from this snapshot rather than the live data.

When a transaction modifies data, the changes are made to a copy of the data rather than the original, and other transactions can continue to read from the original data until the changes are committed. This allows multiple transactions to read and write to the same data without conflicts, improving concurrency and performance.

RCSI is particularly useful in high-concurrency environments where multiple transactions are accessing the same data simultaneously, as it can reduce the likelihood of lock contention and deadlocks. However, it's important to note that RCSI can increase the overhead of maintaining and managing the snapshot, and may not be appropriate for all scenarios.

The advantages and disadvantages of Read Committed Snapshot Isolation (RCSI) in SQL Server include:

Improved concurrency: RCSI allows multiple transactions to read and write to the same data simultaneously without conflicts, reducing lock contention and deadlocks and improving overall concurrency.

Consistent results: RCSI provides consistent and repeatable results for queries, as each transaction reads from a consistent snapshot of the data rather than the live data. This can help to avoid issues such as non-repeatable reads and phantom reads.
Reduced blocking: Since RCSI uses row versioning instead of locks, it can reduce blocking and improve performance by allowing transactions to continue to read data even when it is being modified by another transaction.
Faster queries: RCSI can improve query performance by reducing the overhead of acquiring and releasing locks, and by reducing the need for expensive locking mechanisms such as table locks.
Improved scalability: By improving concurrency and reducing blocking and contention, RCSI can help to improve the scalability of a database and support more users and transactions.

While Read Committed Snapshot Isolation (RCSI) offers several advantages, it also has some potential disadvantages to consider:

Increased storage requirements: RCSI uses row versioning to maintain a snapshot of the data, which can increase the storage requirements of the database.
Increased overhead: RCSI can also increase the overhead of managing and maintaining the snapshot, which may affect the overall performance of the database.
Increased complexity: RCSI can add complexity to the design and implementation of the database, particularly for applications that require complex transaction processing or handling of large amounts of data.
Increased risk of conflicts: RCSI may increase the risk of write conflicts, particularly in applications that have a high rate of write operations.
Potential for inconsistent data: While RCSI provides consistent and repeatable results for queries, it's possible for data to become inconsistent if multiple transactions modify the same data simultaneously. This can result in non-deterministic behavior, which may be difficult to diagnose and resolve.

Read Committed Snapshot Isolation (RCSI) can have some effects on the TempDB database in SQL Server.

When RCSI is enabled for a database, SQL Server uses versioning to maintain a snapshot of the data for each transaction, a version store which is stored in TempDB. This can increase the usage of TempDB and potentially impact its performance, particularly if the database has a high rate of read and write operations.

In particular, the version store in TempDB can grow very large if there are long-running transactions or if there is a high rate of updates or deletes on heavily used tables. This can lead to contention and performance issues, particularly if the TempDB database is not sized appropriately.

To mitigate these issues, it's important to monitor the size and usage of TempDB when using RCSI, and to ensure that the database is sized appropriately to handle the workload. Best practices for managing TempDB include:

Monitoring TempDB growth: Monitor the size and growth of TempDB, and proactively manage space allocation to avoid running out of disk space.
Sizing TempDB appropriately: Size TempDB appropriately based on the workload and usage patterns of the database.
Separating TempDB from user databases: Consider separating TempDB onto a separate disk or storage device to avoid contention with user databases.
Configuring TempDB for optimal performance: Configure TempDB for optimal performance by setting appropriate file growth settings, enabling trace flags, and using SSDs or other high-performance storage devices.

Here is a query to help you determine how much TempDB is utilized

-- Show space usage in tempdb
SELECT DB_NAME(vsu.database_id) AS DatabaseName,
    vsu.reserved_page_count, 
    vsu.reserved_space_kb, 
    tu.total_page_count as tempdb_pages, 
    vsu.reserved_page_count * 100. / tu.total_page_count AS [Snapshot %],
    tu.allocated_extent_page_count * 100. / tu.total_page_count AS [tempdb % used]
FROM sys.dm_tran_version_store_space_usage vsu
    CROSS JOIN tempdb.sys.dm_db_file_space_usage tu
WHERE vsu.database_id = DB_ID(DB_NAME());

Lets create an Orders Table with 10 sample rows using T-SQL:

Here's an example of how to create a "Sales" table with 8 sample rows using T-SQL.

CREATE TABLE Sales (
    order_id INT,
    part_name VARCHAR(50),
    date DATE,
    status VARCHAR(20),
    total_amount DECIMAL(10, 2)
);

INSERT INTO Sales VALUES 
(1, 'Engine', '2022-04-01', 'Shipped', 350.25),
(1, 'Wheels', '2022-04-01', 'Shipped', 150.00),
(2, 'Tail Assembly', '2022-04-02', 'Pending', 225.50),
(3, 'Landing Gear', '2022-04-03', 'Shipped', 500.00),
(3, 'Wings', '2022-04-03', 'Shipped', 750.00),
(4, 'Propeller', '2022-04-04', 'Pending', 125.50),
(5, 'Seats', '2022-04-05', 'Shipped', 175.00),
(6, 'Avionics', '2022-04-06', 'Shipped', 450.75),
(7, 'Cabinetry', '2022-04-07', 'Pending', 320.25),
(8, 'Fuel Tanks', '2022-04-08', 'Shipped', 800.00);

Execute Query #1 without the commit

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
 
SELECT Top 3 * FROM Sales
 
COMMIT

Execute Query #2 without the commit

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
 
SELECT Top 3 * FROM Sales
 
COMMIT

Both queries return

Through enabling RCSI, it's possible to run simultaneous queries that involve the same data with different isolation levels. Notably, when running two queries without committing either, the one with a SERIALIZABLE isolation level typically blocks other users from accessing the same table. However, with READ COMMITTED isolation level using snapshot scans, such blocks don't occur. This design is effective for systems that predominantly read but minimally write data. The extra perk is that it doesn't necessitate modifying existing queries.

Implement Snapshot isolation in sql server databases

Turn on Snapshot isolation in sql

ALTER DATABASE MyOrders SET ALLOW_SNAPSHOT_ISOLATION ON;

Query #1 Run with out committing

SET TANSACTION ISOLATION LEVEL SNAPSHOT;
SET LOCK_TIMEOUT 10000;
 
BEGIN TRAN;
    UPDATE Sales 
    SET  [Status]  = 1
    WHERE order_id = 1;
    WAITFOR DELAY '00:00:05';
COMMIT;

Query #2 Run

Two queries were executed simultaneously to change the order expedited flag, but with different values. To ensure both were active, they were run with a little delay and under isolation level snapshot. The first query was successful, but the result of the second one was quite different with an error message that indicated something went wrong.

Here is the full error message.

The delicate balance of updating data in SQL Server can oftentimes result in conflicts, ultimately leading to transaction termination. Recently, a transaction was aborted due to update conflict. This issue arose from two queries both trying to update the same row. Upon trying to commit the updated version of the row in the second query, SQL Server detected that the first transaction was also attempting an update to the same row. In such cases, it is not within the database engine's purview to decide which query should take precedence - that is a decision best left to business logic. Accordingly, SQL Server with update conflict error was forced to terminate one of the transactions, ultimately preventing the potential for data inaccuracies.