Manage database concurrency
- 10/9/2017
Skill 3.2: Manage isolation levels
SQL Server uses isolation levels to manage conflict between two transactions attempting to use or change the same data at the same time. Furthermore, because the way in which you implement transactions impacts database performance, you need to understand the differences between isolation levels and be familiar with the scenarios with which each is best suited. Given a scenario in which an isolation level and a set of concurrent queries are specified, you should be able to predict the outcome of the queries. In addition, you should understand the types of locks that SQL Server acquires for each isolation level, if applicable, as well as the effect on other resources, such as tempdb, and the resulting potential performance impact of using a specific isolation level.
Identify differences between isolation levels
At one end of the spectrum, SQL Server can protect data completely to prevent one transaction from seeing the effects of another transaction, while at the other end of the spectrum, it can give all transactions full access to the data. It does this by using isolation levels to control whether a lock is acquired during a read, the type of lock, and the duration of the lock. Isolation levels also determine whether a read operation can access rows that have been changed by another transaction and whether it can access uncommitted rows. Additionally, isolation levels block transactions requiring access to a resource with an exclusive lock.
It is important to note that setting an isolation level does not change the way in which SQL Server acquires locks. If a transaction modifies data, SQL Server always acquires an exclusive (X) lock on the data to change, and holds the lock for the duration of the transaction. The purpose of the isolation levels is to specify how read operations should behave when other concurrent transactions are changing data.
If you lower the isolation level, you can increase the number of concurrent transactions that SQL Server processes, but you also increase the risk of dirty reads and other problems associated with concurrent processes as we described in Skill 3.1. If you raise the isolation level, you minimize these concurrency problems, but transactions are more likely to block one another and performance is more likely to suffer. Therefore, you must find the appropriate balance between protecting data and the effect of each isolation level.
SQL Server supports both pessimistic and optimistic isolation levels for concurrency management. Pessimistic isolation levels use blocking to avoid conflicts whereas optimistic isolation levels use snapshots of the data to enable higher concurrency. Pessimistic isolation levels rely on locks to prevent changes to data during read operations and to block read operations on data that is being changed by another operation. Optimistic isolation levels make a copy of data for read operations so that write operations can proceed unhindered. If SQL Server detects two write operations attempting to modify the same data at the same time, it returns a message to the application in which there should be appropriate logic for resolving this conflict.
Exam Tip
It is important to understand the differences between SQL Server isolation levels and scenarios for which each is appropriate.
Read Committed
READ COMMITTED is the default isolation level for SQL Server. It uses pessimistic locking to protect data. With this isolation level set, a transaction cannot read uncommitted data that is being added or changed by another transaction. A transaction attempting to read data that is currently being changed is blocked until the transaction changing the data releases the lock. A transaction running under this isolation level issues shared locks, but releases row or page locks after reading a row. If your query scans an index while another transactions changes the index key column of a row, that row could appear twice in the query results if that key change moved the row to a new position ahead of the scan. Another option is that it might not appear at all if the row moved to a position already read by the scan.
Read Uncommitted
The READ UNCOMMITTED isolation level is the least restrictive setting. It allows a transaction to read data that has not yet been committed by other transactions. SQL Server ignores any locks and reads data from memory. Furthermore, transactions running under this isolation level do not acquire shared (S) locks to prevent other transactions from changing the data being read. Last, if a transaction is reading rows using an allocation order scan when another transaction causes a page split, your query can miss rows. For these reasons, READ UNCOMMITTED is never a good choice for line of business applications where accuracy matters most, but might be acceptable for a reporting application where the performance benefit outweighs the need for a precise value.
Repeatable Read
When you set the REPEATABLE READ isolation level, you ensure that any data read by one transaction is not changed by another transaction. That way, the transaction can repeat a query and get identical results each time. In this case, the data is protected by shared (S) locks. It is important to note that the only data protected is the existing data that has been read. If another transaction inserts a new row, the first transaction’s repeat of its query could return this row as a phantom read.
Serializable
The most pessimistic isolation level is SERIALIZABLE, which uses range locks on the data to not only prevent changes but also insertions. Therefore, phantom reads are not possible when you set this isolation level. Each transaction is completely isolated from one another even when they execute in parallel or overlap.
Snapshot
The SNAPSHOT isolation level is optimistic and allows read and write operations to run concurrently without blocking one another. Unlike the other isolation levels, you must first configure the database to allow it, and then you can set the isolation level for a transaction. As long as a transaction is open, SQL Server preserves the state of committed data at the start of the transaction and stores any changes to the data by other transactions in tempdb. It increases concurrency by eliminating the need for locks for read operations.
Read Committed Snapshot
The READ_COMMITTED_SNAPSHOT isolation level is an optimistic alternative to READ COMMITTED. Like the SNAPSHOT isolation level, you must first enable it at the database level before setting it for a transaction. Unlike SNAPSHOT isolation, you can use the READ_COMMITTED_SNAPSHOT isolation level with distributed transactions. The key difference between the two isolation levels is the ability with READ_COMMITTED_SNAPSHOT for a transaction to repeatedly read data as it was at the start of the read statement rather than at the start of the transaction. When each statement executes within a transaction, SQL Server takes a new snapshot that remains consistent until the next statement executes.
You use this isolation level when your application executes a long-running, multi-statement query and requires the data to be consistent to the point in time that the query starts. You should also consider using this isolation level when enough read and write blocking occurs that the resource overhead of maintaining row snapshots is preferable and there is little likelihood of a transaction rolling back due to an update conflict.
Define results of concurrent queries based on isolation level
To better appreciate the effect of concurrent queries, let’s consider a scenario that involves two users that are operating on the same data. One user starts executing a query that results in a full table scan and normally takes several minutes to complete. Meanwhile, a minute after the read operation begins, the other user updates and commits row in the same table that has not yet been read by the first user’s query. The rows returned by the first user’s query depend on the isolation levels set for that user.
Before we look at each isolation level’s effect on this scenario, let’s create a table and add some data as shown in Listing 3-5.
LISTING 3-5 Create a test environment for testing isolation levels
CREATE TABLE Examples.IsolationLevels
(
RowId int NOT NULL
CONSTRAINT PKRowId PRIMARY KEY,
ColumnText varchar(100) NOT NULL
);
INSERT INTO Examples.IsolationLevels(RowId, ColumnText)
VALUES (1, 'Row 1'), (2, 'Row 2'), (3, 'Row 3'), (4, 'Row 4');
You use the SET TRANSACTION ISOLATION LEVEL statement when you want to override the default isolation level and thereby change the way a SELECT statement behaves with respect to other concurrent operations. It is important to know that this statement changes the isolation level for the user session. If you want to change the isolation level for a single statement only, use a table hint instead.
Read Committed
Because this isolation level only reads committed data, dirty reads are prevented. However, if query reads the same data multiple times, non-repeatable reads or phantom reads are possible.
Because the READ COMMITTED isolation level is the default, you do not need to explicitly set the isolation level. However, if you had previously changed the isolation level for the user session or the database, you can revert it to the default isolation level by executing the following statement:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
To test the behavior of the READ COMMITTED isolation level, execute the following statements:
BEGIN TRANSACTION;
UPDATE Examples.IsolationLevels
SET ColumnText = 'Row 1 Updated'
WHERE RowId = 1;
In a new session, read the table that you just updated:
SELECT RowId, ColumnText FROM Examples.IsolationLevels;
In this case, the update operation blocks the read operations. Return to the first session and restore the data by rolling back the transaction:
ROLLBACK TRANSACTION;
Now the second session’s read request completes successfully, and the results do not include the updated row because it was never committed.
RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4
Read Uncommitted
This isolation level allows dirty reads, non-repeatable reads, and phantom reads. On the other hand, a transaction set to this isolation level executes quickly because locks and validations are ignored.
Let’s observe this behavior by starting a transaction without committing it:
BEGIN TRANSACTION;
UPDATE Examples.IsolationLevels
SET ColumnText = 'Row 1 Updated'
WHERE RowId = 1;
Now open a new session, change the isolation level, and read the table that you just updated:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT RowId, ColumnText FROM Examples.IsolationLevels;
The results include the updated row:
RowId ColumnText ------- ------------ 1 Row 1 Updated 2 Row 2 3 Row 3 4 Row 4
Return to the first session and roll back the transaction:
ROLLBACK TRANSACTION;
Then in the second session, read the table again:
SELECT RowId, ColumnText FROM Examples.IsolationLevels;
Now the results show the data in its state prior to the update that rolled back:
RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4
Rather than change the isolation level at the session level, you can force the read uncommitted isolation level by using the NOLOCK hint. Repeat the previous example by using two new sessions to revert to the default isolation level and replacing the statements in the second session with the following statement:
SELECT RowId, ColumnText FROM Examples.IsolationLevels WITH (NOLOCK);
Repeatable Read
The behavior of the REPEATABLE READ isolation level is much like that of READ COMMITTED, except that it ensures that multiple reads of the same data within a transaction is consistent. Dirty reads and non-repeatable reads are prevented, although phantom reads are a possible side effect because range locks are not used.
We can see the effects of using REPEATABLE READ by running statements in separate sessions. Start by adding the following statements in one new session:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
WAITFOR DELAY '00:00:15';
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
ROLLBACK TRANSACTION;
In the second session, add the following statements and then with both sessions visible, execute both sessions:
UPDATE Examples.IsolationLevels
SET ColumnText = 'Row 1 Updated'
WHERE RowId = 1;
In this case, the first read operations blocks the update operation, which executes when the first read’s locks are released, the update commits the data change, but the second query returns the same rows as the first query due to the isolation level of the transaction:
RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4
If you check the table values again, you can see that the updated row appears in the query results:
RowId ColumnText ------- ------------ 1 Row 1 Updated 2 Row 2 3 Row 3 4 Row 4
Serializable
The SERIALIZABLE isolation level behaves like REPEATABLE READ, but goes one step further by ensuring new rows added after the start of the transaction are not visible to the transaction’s statement. Therefore, dirty reads, non-repeatable reads, and phantom reads are prevented.
Before we see how the SERIALIZABLE isolation level works, let’s look at an example that produces a phantom read. In one new session, add the following statements:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
WAITFOR DELAY '00:00:15';
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
ROLLBACK TRANSACTION;
As in the previous examples, start a new session to insert a row into the same table, and execute both sessions:
INSERT INTO Examples.IsolationLevels(RowId, ColumnText) VALUES (5, 'Row 5');
In this case, the transaction starts with a read operation and returns four rows, but does not block the insert operation. The REPEATABLE READ isolation level only prevents changes to data that has been read, but does not prevent the transaction from seeing the new row, which is returned by the second query as shown here:
RowId ColumnText ------- ------------ 1 Row 1 Updated 2 Row 2 3 Row 3 4 Row 4 RowId ColumnText ------- ------------ 1 Row 1 Updated 2 Row 2 3 Row 3 4 Row 4 5 Row 5
Replace the isolation level statement in the first session with this statement to change the isolation level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Then create a new session to insert another row:
INSERT INTO Examples.IsolationLevels(RowId, ColumnText) VALUES (6, 'Row 6');
This time because the INSERT operation is blocked by the transaction, both queries return the same results without the new row.
RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 5 Row 5 RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 5 Row 5
After the transaction ends, any subsequent queries to the table return six rows. The trade-off for this consistency during the transaction is the blocking of write operations.
Snapshot
Snapshot Isolation gives you the same data for the duration of the transaction. This level of protection prevents dirty reads, non-repeatable reads, and phantom reads. As other transactions update or delete rows, a copy of the modified row is inserted into tempdb. This row also includes a transaction sequence number so that SQL Server can determine which version to use for a new transaction’s snapshot. When the new transaction executes a read request, SQL Server scans the version chain to find the latest committed row having a transaction sequence number lower than the current transaction. Periodically, SQL Server deletes row versions for transactions that are no longer open.
To use the SNAPSHOT isolation level, you must first enable it at the database level by using the following statement:
ALTER DATABASE ExamBook762Ch3 SET ALLOW_SNAPSHOT_ISOLATION ON;
Now set the isolation level for the session and start a transaction:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
WAITFOR DELAY '00:00:15';
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
ROLLBACK TRANSACTION;
Then set up a write operation in a new second session:
INSERT INTO Examples.IsolationLevels(RowId, ColumnText) VALUES (7, 'Row 7');
The write operation runs immediately because it is no longer blocked by the read operations, yet the query results return only the six rows that existed prior to the insertion.
If you have a transaction that reads from a database that is enabled for SNAPSHOT isolation and another database that is not enabled, the transaction fails. To execute successfully, the transaction must include a table hint for the database without SNAPSHOT isolation level enabled.
Let’s set up another database and a new table as shown in Listing 3-6.
LISTING 3-6 Create a separate for testing isolation levels
CREATE DATABASE ExamBook762Ch3_IsolationTest;
GO
USE ExamBook762Ch3_IsolationTest;
GO
CREATE SCHEMA Examples;
GO
CREATE TABLE Examples.IsolationLevelsTest
(RowId INT NOT NULL
CONSTRAINT PKRowId PRIMARY KEY,
ColumnText varchar(100) NOT NULL
);
INSERT INTO Examples.IsolationLevelsTest(RowId, ColumnText)
VALUES (1, 'Row 1'), (2, 'Row 2'), (3, 'Row 3'), (4, 'Row 4');
Now try to execute the following transaction that joins the data from the snapshot-enabled database with data from the other database:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT t1.RowId, t2.ColumnText
FROM Examples.IsolationLevels AS t1
INNER JOIN ExamBook762Ch3_IsolationTest.Examples.IsolationLevelsTest AS t2
ON t1.RowId = t2.RowId;
END TRANSACTION;
SQL Server returns the following error:
Msg 3952, Level 16, State 1, Line 5 Snapshot isolation transaction failed accessing database 'ExamBook762Ch3_IsolationTest' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
You might not always have the option to alter the other database to enable Snapshot isolation. Instead, you can change the isolation level of the transaction’s statement to READ COMMITTED, which allows the transaction to execute successfully:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT t1.RowId, t2.ColumnText
FROM Examples.IsolationLevels AS t1
INNER JOIN ExamBook762Ch3_IsolationTest.Examples.IsolationLevelsTest AS t2
WITH (READCOMMITTED)
ON t1.RowId = t2.RowId;
END TRANSACTION;
Another problem that you might encounter when using this isolation level is an update conflict, which causes the transaction to terminate and roll back. This situation can occur when one transaction using the SNAPSHOT isolation level reads data that another transaction modifies and then the first transaction attempts to update the same data. (This situation does not occur when a transaction runs using the READ_COMMITTED_SNAPSHOT isolation level.)
A problem can also arise when the state of the database changes during the transaction. As one example, a transaction set to SNAPSHOT isolation fails when the database is changed to read-only after the transaction starts, but before it accesses the database. Likewise, a failure occurs if a database recovery occurred in that same interval. A database recovery can be caused when the database is set to OFFLINE and then to ONLINE, when it auto-closes and re-opens, or when an operation detaches and attaches the database.
It is important to know that row versioning applies only to data and not to system metadata. If a statement changes metadata of an object while a transaction using the SNAPSHOT isolation level is open and the transaction subsequently references the modified object, the transaction fails. Be aware that BULK INSERT operations can change a table’s metadata and cause transaction failures as a result. (This behavior does not occur when using the READ_COMMITTED_SNAPSHOT isolation level.)
One way to see this behavior is to change an index on a table while a transaction is open. Let’s first add an index to a table:
CREATE INDEX Ix_RowId ON Examples.IsolationLevels (RowId);
Next set up a new transaction:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
WAITFOR DELAY '00:00:15';
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
ROLLBACK TRANSACTION;
Then set up a second session to change the index by using the following statement and execute both sessions:
ALTER INDEX Ix_RowId
ON Examples.IsolationLevels REBUILD;
SQL Server returns the following error due to the metadata change:
Msg 3961, Level 16, State 1, Line 6 Snapshot isolation transaction failed in database 'ExamBook762Ch3' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is disallowed because the metadata is not versioned. A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation.
Be sure to disable snapshot isolation after completing the examples in this section:
ALTER DATABASE ExamBook762Ch3 SET ALLOW_SNAPSHOT_ISOLATION OFF;
Read Committed snapshot
To use the READ_COMMITTED_SNAPSHOT isolation level, you need only enable it at the database level by using the following statement:
ALTER DATABASE ExamBook762Ch3 SET READ_COMMITTED_SNAPSHOT ON;
With this setting enabled, all queries that normally execute using the READ COMMITTED isolation level switch to using the READ_COMMITTED_SNAPSHOT isolation level without requiring you to change the query code. SQL Server creates a snapshot of committed data when each statement starts. Consequently, read operations at different points in a transaction might return different results.
During the transaction, SQL Server copies rows modified by other transactions into a collection of pages in tempdb known as the version store. When a row is updated multiple times, a copy of each change is in the version store. This set of row versions is called a version chain.
Let’s see how this isolation level differs from the SNAPSHOT isolation level by setting up a new session:
BEGIN TRANSACTION;
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
WAITFOR DELAY '00:00:15';
SELECT RowId, ColumnText
FROM Examples.IsolationLevels;
ROLLBACK TRANSACTION;
Next, set up a write operation in a new second session, and then execute both sessions:
INSERT INTO Examples.IsolationLevels(RowId, ColumnText) VALUES (8, 'Row 8');
Just as with the SNAPSHOT isolation level, the write operation runs immediately because read operations are not blocking it. However, each query returns different results because the statements read different versions of the data.
RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 5 Row 5 6 Row 6 7 Row 7 RowId ColumnText ------- ------------ 1 Row 1 2 Row 2 3 Row 3 4 Row 4 5 Row 5 6 Row 6 7 Row 7 8 Row 8
Last, disable the READ_COMMITTED_SNAPSHOT isolation level after completing this example:
ALTER DATABASE Examples SET READ_COMMITTED_SNAPSHOT OFF;
Identify the resource and performance impact of given isolation levels
The goal of isolation levels is to ensure that queries return complete and consistent results while other concurrent processes are running. To avoid locking contention and improve overall performance, you should keep each transaction short and concise so it can execute quickly while holding the fewest and smallest possible locks.
Read Committed
With this isolation level, SQL Server holds two types of locks. A shared (S) lock is acquired for read operations and is held only for the duration of that single operation. On the other hand, an exclusive (X) lock is acquired for a write operation. Any changes to the data are not visible to other operations for the duration of the write operation’s transaction.
Read Uncommitted
SQL Server ignores existing locks and reads both committed and uncommitted data. Furthermore, it does not acquire shared locks for read operations. However, schema modification locks can still block reads.
Repeatable Read
SQL Server places Shared (S) locks on the data (and up the lock hierarchy) for the duration of the transaction. Therefore, reads block write operations in other transactions. Consequently, SQL Server cannot manage as many concurrent processes and performance can be adversely impacted as deadlocks can become more frequent.
Serializable
SQL Server locks data for a read operation and also uses key-range locks to prevent any other transactions from inserting or modifying the data for the duration of a transaction. This high level of locking reduces concurrency and potentially slows performance due to locking contention.
Snapshot
No locks are acquired for this isolation level. Consequently, deadlocks and lock escalations occur less frequently, performance is faster, and concurrency is higher. Read operations are not blocked by write operations, and write operations are not blocked by read operations.
On the other hand, these benefits come with an overhead cost. More space is required in tempdb for row version storage and more CPU and memory is required by SQL Server to manage row versioning. Update operations might run slower as a result of the extra steps required to manage row versions. Furthermore, long running read operations can run slower if many updates or deletes are occurring and increasing the length of the version chains that SQL Server must scan. You can improve performance by placing tempdb on a dedicated, high-performance disk drive.
Read Committed Snapshot
When a new transaction using the READ_COMMITTED_SNAPSHOT isolation level requests locked data, SQL Server provides a copy of the data. It does not acquire shared page or row locks. As a consequence, reads do not block write operations and writes do not block read operations, although writes do require exclusive locks and continue to block other writes until the end of the transaction. However, because SQL Server removes row versions from tempdb when a transaction is over, it is possible to experience some concurrency side effects.