Optimistic transaction isolation levels were introduced in SQL Server 2005 as a new way to deal with blocking problems and to address data consistency issues. As opposed to pessimistic transaction isolation levels, with optimistic transaction isolation levels, queries read “old” committed versions of rows while accessing the data modified by the other sessions, rather than being blocked by the incompatibility of shared (S) and exclusive (X) locks.
With optimistic transaction isolation levels, when updates occur, SQL Server stores the old versions of the rows in a special part of tempdb called version store. The original rows in the database are also updated with 14-byte pointers that reference the old versions of the rows. Depending on the situation, you can have more than one version of the records stored for the row. Figure 21-1 illustrates this behavior.
Figure 21-1. Version store
Now when readers (and sometimes writers) access the row that holds exclusive (X) lock, they get the old version from the version store rather than being blocked, as shown in Figure 21-2.
Figure 21-2. Readers and version store
As you can guess, while optimistic isolation levels help reduce blocking, there are some tradeoffs. Most significant among these is that they contribute to tempdb load. Using optimistic isolation levels on highly volatile systems can lead to very heavy tempdb activity and significantly increase tempdb size. We will look at this issue in greater detail later in this chapter.
There is overhead during data modification and retrieval. SQL Server needs to copy the data to tempdb as well as maintain a linked list of the version records. Similarly, it needs to traverse that list when reading data. This adds additional CPU and I/O load.
Finally, optimistic isolation levels contribute to index fragmentation. When a row is modified, SQL Server increases the row size by 14 bytes due to the version pointer. If a page is tightly packed and a new version of the row does not fit into the page, it will lead to a page split and further fragmentation. This is very similar to the insert/update pattern we discussed in Chapter 5, “Index Fragmentation.” Those 14 bytes will stay in the row even after records are removed from the version store until the index is rebuilt.
Tip If optimistic isolation levels are used, it is recommended that you reserve some space on the pages by using FILLFACTOR less than 100. It reduces page splits due to row size increases because of the version store pointers.
Optimistic Transaction Isolation Levels
There are two optimistic transaction isolation levels: READ COMMITTED SNAPSHOT and SNAPSHOT. To be precise, SNAPSHOT is a separate transaction isolation level while READ COMMITTED SNAPSHOT is a database option that changes the behavior of the readers in READ COMMITTED transaction isolation level.
Let’s examine these levels in depth.
READ COMMITTED SNAPSHOT Isolation Level
Both optimistic isolation levels need to be enabled on the database level. You can enable READ COMMITTED SNAPSHOT (RCSI) with the ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON statement.
Note Changing this option requires exclusive database access. It will be blocked if there are other users connected to the database. You can switch the database to single user mode or run an ALTER DATABASE SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK AFTER X SECONDS statement to avoid blocking. This will roll back all active transactions and terminate existing database connections, which allows changing of the database option.
As already mentioned, RCSI changes the behavior of the readers in READ COMMITTED mode. It does not affect behavior of the writers, however.
As you see in Figure 21-3, instead of acquiring shared (S) locks and being blocked by exclusive (X) locks held on the row, readers use the old version from the version store. Writers still acquire update (U) and exclusive (X) locks in the same way as in pessimistic isolation levels. Again, as you see, blocking between writers from different sessions still exists, although writers do not block readers in a similar manner to READ UNCOMMITTED mode.
Figure 21-3. Read Committed Snapshot isolation level behavior
There is a major difference between READ UNCOMMITTED and READ COMMITTED SNAPSHOT isolation levels, however. READ UNCOMMITTED removes the blocking at the expense of data consistency. Many consistency anomalies are possible, including reading uncommitted data, duplicated reads, and missed rows. On the other hand, the READ COMMITTED SNAPSHOT isolation level provides us with full statement-level consistency. Statements running in this isolation level do not access uncommitted data nor the data committed after the statement started.
Tip Switching a database to the READ COMMITTED SNAPSHOT isolation level can be a great emergency technique when the system is suffering from blocking issues. It removes writers/readers blocking without any code changes, assuming, of course, that readers are running in READ COMMITTED transaction isolation level. Obviously, this is only a temporary solution and, in the end, you need to detect and eliminate the root cause of the problem.
SNAPSHOT is a separate transaction isolation level, and it needs to be set explicitly in the code with a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement or by using a WITH (SNAPSHOT) table hint.
By default, using SNAPSHOT isolation level is prohibited. You must enable it with an ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON statement. This statement does not require exclusive database access, and it can be executed with other users connected to the database.
A SNAPSHOT isolation level provides transaction-level consistency. Transactions will see a snapshot of the data at the moment when transaction started regardless of how long the transaction is active and how many data changes were made in other transactions during that time.
In the example shown in Figure 21-4, we have a Session 1 that starts the transaction and reads the row at time T1. At time T2, we have a Session 2 that modifies the row in the implicit transaction. At this moment, the old (original) version of the row moved to the version store in tempdb.
Figure 21-4. Snapshot isolation level and readers behavior
In the next step, we have a Session 3 that starts another transaction and reads the same row at time T3. It sees the version of the row as modified and committed by Session 2 (at time T2). At time T4, we have a Session 4 that modifies the row in the implicit transaction again. At this time, we have two versions of the rows in the version store—one that existed between T2 and T4 and the original version that existed before T2. Now, if Session 3 runs the select again, it would use the version that existed between T2 and T4 because this version was committed at the time as the Session 3 transaction started. Similarly, Session 1 would use the original version of the row that existed before T2. At some point, after Session 1 and Session 3 are committed, the version store clean up task would remove both records from the version store, assuming, of course, that there are no other transactions that need them.
A SNAPSHOT isolation level provides similar data consistency as a SERIALIZABLE isolation level with no blocking involved, although it could generate an enormous amount of data in the tempdb. If you have a session that deletes millions of rows from the table, all of those rows need to be copied to the version store, even if the original delete statement is running in a non-snapshot isolation mode, just to preserve the state of the data for possible snapshot or RCSI transactions.
Now let’s examine the writer’s behavior. Let’s assume that Session 1 starts the transaction and updates one of the rows. That session holds an exclusive (X) lock there, as shown in Figure 21-5.
Figure 21-5. Snapshot isolation level and writers behavior (1)
Session 2 wants to update all rows where Cancelled = 1. It starts to scan the table, and when it needs to read the data for OrderId = 10, it reads the row from the version store; that is, the last committed version before the Session 2 transaction started. This version is the original (non-updated) version of the row and it has Cancelled = 0, so Session 2 does not need to update it. Session 2 continues scanning the rows without being blocked by update (U) and exclusive (X) locks incompatibility.
Similarly, Session 3 wants to update all rows with Amount = 29.95. When it reads the version of the row from the version store, it determines that the row needs to be updated. Again, it does not matter that Session 1 also changes the amount for the same row. At this point, a “new version” of the row has not been committed and it is invisible to the other sessions. Now Session 3 wants to update the row in the database, tries to acquire exclusive (X) lock, and is blocked because Session 1 already has exclusive (X) lock there.
There is another possibility, however. Let’s consider the following scenario, keeping in mind the transaction consistency that a snapshot isolation level guarantees.
In the example shown in Figure 21-6, Session 1 starts a transaction and updates one of the rows. In the next step, Session 2 starts another transaction. In fact, it does not really matter what session starts the transaction first, as long as a new version of the row with OrderId = 10 is not committed.
Figure 21-6. Snapshot isolation level and writers behavior (2)
In either case, Session 1 commits the transaction as the next step. At this point, the exclusive (X) lock on the row is released. If Session 2 tries to read that row, it would still use the version from the version store because it was the last committed version at the time that Session 2 transaction started. Nevertheless, if Session 2 tries to modify that row, it would generate the 3960 error and rollback the transaction, as shown in Figure 21-7.
Figure 21-7. Error 3960
Tip You can implement retry logic with TRY/CATCH statements to handle the 3960 error.
You need to keep this behavior in mind when you are updating the data in SNAPSHOT isolation level in a system with volatile data. If other sessions update the rows that you are modifying after the transaction is started, you would end up with this error even if you did not access those rows before the update. One of the possible workarounds is using READCOMMITTED or other non-optimistic isolation level table hints as part of the update statement, as shown in Listing 21-1.
Listing 21-1. Using READCOMMITTED hint to prevent 3960 error
set transaction isolation level snapshot
begin tran
select count(*) from Delivery.Drivers
update Delivery.Orders with (readcommitted)
set Cancelled = 1
where OrderId = 10
rollback
SNAPSHOT isolation levels can change the behavior of the system. Let’s assume there is a table dbo.Colors with two rows: Black and White. The code that creates the table is shown in Listing 21-2.
Listing 21-2. SNAPSHOT isolation level update behavior: Table creation
create table dbo.Colors
(
Id int not null,
Color char(5) not null
)
go
insert into dbo.Colors(Id, Color) values(1,'Black'),(2,'White')
Now let’s run two sessions simultaneously. In the first session, we run the update that sets the color to white for the rows where the color is currently black. The code is shown in Listing 21-3.
Listing 21-3. SNAPSHOT isolation level update behavior: Session 1 code
begin tran
update dbo.Colors
set Color = 'White'
where Color = 'Black'
commit
In the second session, let’s perform the opposite operation, as shown in Listing 21-4.
Listing 21-4. SNAPSHOT isolation level update behavior: Session 2 code
begin tran
update dbo.Colors
set Color = 'Black'
where Color = 'White'
commit
Let’s run both sessions simultaneously in READ COMMITTED or any other pessimistic transaction isolation level. In the first step, as shown in Figure 21-8, we have the race condition. One of the sessions places exclusive (X) locks on the row it updated while another session will be blocked trying to acquire an update (U) lock on the same row.
Figure 21-8. Pessimistic locking behavior: Step 1
When the first session commits the transaction, the exclusive (X) lock would be released. At this point, the row would have a Color value updated by the first session so that the second session updates two rows rather than one, as shown in Figure 21-9. In the end, both rows in the table will be either in Black or White depending on which session acquires the lock first.
Figure 21-9. Pessimistic locking behavior: Step 2
With snapshot isolation level, however, this works a bit differently, as shown in Figure 21-10. When the session updates the row, it moves the old version of the row to the version store. Another session will read the row from there, rather than being blocked and vice versa. As a result, the colors will be swapped.
Figure 21-10. Snapshot isolation level locking behavior
You need to be aware of RCSI and SNASPSHOT isolation level behavior especially if you have code that relies on the blocking. One example is trigger-based implementation of referential integrity. You can have an ON DELETE trigger on the referenced table where you are running a select statement to check to see if there are any rows in another table referencing deleted rows. With an optimistic isolation level, the trigger can skip the rows that were inserted after the transaction started. The solution here again is READCOMMITTED or other pessimistic isolation level table hints as part of the select in both triggers on both referenced and referencing tables.
Note SQL Server uses a READ COMMITTED isolation level when validating foreign key constraints. This means that you can still have blocking between writers and readers even with optimistic isolation levels, especially if there are no indexes on the referencing column that leads to a table scan of the referencing table.
Version Store Behavior
As already mentioned, you need to monitor how optimistic isolation levels affect your system. For example, let’s run the following statement that deletes all rows from Delivery.Orders table, as shown in Listing 21-5.
Listing 21-5. Deleting all orders from the table
set transaction isolation level read uncommitted
begin tran
delete from Delivery.Orders
commit
It is worth mentioning that a session runs in READ UNCOMMITTED mode. Even if there are no other transactions using optimistic isolation levels, there is still a possibility that they will start before the DELETE transaction commits. As a result, SQL Server needs to maintain the version store, regardless of whether or not there are any active transactions that use optimistic isolation levels.
Figure 21-11 shows tempdb free space and version store size. As you see, as soon as the deletion starts, version store grows and takes up all of the space from tempdb.
Figure 21-11. tempdb free space and version store size
In Figure 21-12, you can see the version store generation and cleanup rate. The generation rate remains more or less the same during execution, while the cleanup task cleaned the version store after the transaction is committed. By default, the cleanup task runs once per minute as well as before an auto-growth event, in case tempdb is full.
Figure 21-12. Version generation and cleanup rates
There are three other performance counters related to optimistic isolation levels:
- Snapshot Transactions. This shows the total number of active snapshot transactions.
- Update Conflict Ratio. This shows the ratio of the number of update conflicts to the total number of update snapshot transactions.
- Longest Transaction Running Time. This shows the duration in seconds of the oldest active transaction that is using row versioning.
There are a few Dynamic Management Views (DMVs)that can be useful in troubleshooting various issues related to the version store and transactions in general. Look at Transaction Related Dynamic Management Views and Functions section at: http://technet.microsoft.com/en-us/library/ms178621.aspx for further reading.
Summary
SQL Server uses a row-versioning model with optimistic isolation levels. Queries access “old” committed versions of rows rather than being blocked by the incompatibility of shared (S), update (U), and exclusive (X) locks. There are two optimistic transaction isolation levels available: READ COMMITTED SNAPSHOT and SNAPSHOT.
READ COMMITTED SNAPSHOT is a database option that changes the behavior of readers in READ COMMITTED mode. It does not change the behavior of writers—there is still blocking due to (U)/(U) and (U)/(X) locks incompatibility. READ COMMITTED SNAPSHOT does not require any code changes, and it can be used as an emergency technique when a system is experiencing blocking issues.
READ COMMITTED SNAPSHOT provides statement-level consistency; that is, the query reads a snapshot of the data at the time the query started.
SNAPSHOT isolation level is a separate transaction isolation level that needs to be explicitly specified in the code. This level provides transaction-level consistency; that is, the query accesses a snapshot of the data at the time the transaction started.
With SNAPSHOT isolation level, writers do not block each other, with the exception of the situation where both sessions are updating the same rows. That situation leads either to blocking or to a 3960 error.
While optimistic isolation levels reduce blocking, they can significantly increase tempdb load, especially in OLTP systems where data is constantly changing. You should consider the tradeoffs of using them at the implementation stage, perform tempdb optimization, and monitor the system to make sure that the version store is not abused.