Introduction
In this article, I’ll explore the use of snapshot isolation using row versioning as an alternative to the classic ANSI SQL transaction levels.
Modern database systems excel at using the system resources available to them. Those include the CPU, RAM, Network and persistent storage of some sort. Busy systems can have thousands of active sessions, yet each session has the illusion of exclusive access. This is called concurrency and it is fundamental to the operation of most computing systems — even your smartphone!
In relational database systems, transactional isolation – an ANSI SQL standard – is often used to try to maximize concurrency while maintaining the ACID principle. Sometimes that is called pessimistic concurrency, since it assumes that something may go wrong and tries to prevent that from happening. Some database vendors have developed alternative approaches to managing concurrency and SQL Server is no exception. This article looks at snapshot isolation using row versioning as an example of just such an approach. Row versioning can be called optimistic concurrency since it assumes that things will usually go right.
Transactional Isolation
As a point of reference, recall that transactional isolation uses four isolation levels to try to keep things in order. Each involves a trade-off between concurrency and consistent results:
- READ UNCOMMITTED (high concurrency, low consistency)
- READ COMMITTED (somewhat lower concurrency, better consistency; the default isolation level)
- REPEATABLE READ (even lower concurrency, much better consistency)
- SERIALIZABLE (no current access to the same objects, optimal consistency)
These isolation levels are usually implemented through a series or hierarchy of locks. Locking can cause blocking – which is actually a normal process in a busy system – but can give rise to deadlocks – an error that requires careful preparation for building correct exception handling processes.
- For detailed information about transactional isolation, see Transaction Locking and Row Versioning Guide in the Microsoft documentation.
Snapshot Isolation
Snapshot isolation avoids most locking and blocking by using row versioning. When data is modified, the committed versions of affected rows are copied to tempdb and given version numbers. This operation is called copy on write and is used for all inserts, updates and deletes using this technique. When another session reads the same data, the committed version of the data as of the time the reading transaction began is returned.
By avoiding most locking, this approach can greatly increase concurrency at a lower cost than transactional isolation. Of course, “There ain’t no such thing as a free lunch!” and snapshot isolation has a hidden cost: increased usage of tempdb. For the moment, let’s look at the two types of snapshot isolation available in SQL Server:
- READ COMMITTED SNAPSHOT ISOLATION (RCSI)
- SNAPSHOT ISOLATION
Read Committed Snapshot Isolation (RCSI)
Both types involve settings at the database level. The first is set with this command:
|
1 |
ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON; |
For example, I have a little orders database I use for demos. I’m using Azure Data Studio in the screenshot below. I like ADS since it is lightweight and cross-platform.
The ALTER command enables RCSI for my database. You can easily see the status of RCSI for any database with the select query at the bottom:
|
1 2 3 4 5 6 |
SELECT DB_NAME(database_id), is_read_committed_snapshot_on, snapshot_isolation_state_desc FROM sys.databases WHERE database_id = DB_ID(); |
On my database it returns:
Note that there are two indicators of interest. snapshot_isolation_state_desc, in the third column and is_read_committed_snapshot_on in the second. You can see that RCSI is now enabled.
Read Committed Snapshot Isolation works by changing any query run under the Read Committed transactional isolation level to use snapshot scans instead and do not acquire locks other than the Sch-S (schema stability) lock. This gives automatic access to versioned rows even with queries that don’t ask for it. Consider these two queries:
Query 1:
|
1 2 3 4 5 6 |
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT * FROM Orders.Orders COMMIT |
Query 2:
|
1 2 3 4 5 6 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION SELECT * FROM Orders.Orders COMMIT |
Now, see what happens when I run them together, with RCSI enabled:
After enabling RCSI, I ran both queries without committing either one. Note that the first one uses the SERIALIZABLE isolation level. Normally, any other user of the same table would wait for that transaction to complete. However, the second query runs just fine! The READ COMMITTED isolation level was implemented using snapshot scans and was not blocked by the transaction running under SERIALIZABLE. This can be ideal for systems that write a little and read a lot. It is also convenient since you do not need to change any existing queries. Think of a data warehouse where ETL operations happen overnight and reports and ad-hoc queries run during office hours. You get the performance of the READ UNCOMMITTED isolation level without the risks. Also, if there is little-to-no concurrent updating of the data at the same time, tempdb use will be very low.
Explicit Snapshot Isolation
Now let’s look at the second option:
|
1 |
ALTER DATABASE MyOrders SET ALLOW_SNAPSHOT_ISOLATION ON; |
I’ll run that and show the status of the option in the database as before:
In this session, I’ve disabled RCSI and enabled Snapshot Isolation. Note that the actual command says ALLOW_SNAPSHOT_ISOLATION. That word “ALLOW” is important! By itself, this command doesn’t change how many queries run. It just sets up permission to do so. We have to explicitly enable it for a transaction using the command:
|
1 |
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; |
If you use this isolation level after changing the database setting, then the effect will be the same as RCSI for queries that read the database. You just have to be explicit.
Now, in case you think this is too wonderful for words and wonder why it is not the default for all SQL Server databases, let me show you what can happen on a system with lots of active writes:
Query 1:
|
1 2 3 4 5 6 7 8 9 |
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET LOCK_TIMEOUT 10000; BEGIN TRAN; UPDATE Orders.Orders SET OrderIsExpedited = 1 WHERE OrderID = 1; WAITFOR DELAY '00:00:05'; COMMIT; |
Query 2:
|
1 2 3 4 5 6 7 8 9 |
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET LOCK_TIMEOUT 10000; BEGIN TRAN; UPDATE Orders.Orders SET OrderIsExpedited = 0 WHERE OrderID = 1; WAITFOR DELAY '00:00:05'; COMMIT; |
Each query changes the order expedited flag, though with different values. I ran these two queries together under isolation level snapshot with a little delay built-in, to ensure that they are both active at some time. The first one completed normally, but look at what happened to the second one! The full text of the error message is:
Msg 3960, Level 16, State 5, Line 7
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Orders. Orders’ directly or indirectly in database ‘MyOrders’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement
The most important part is the first sentence. “Transaction aborted due to update conflict.” The problem is that both queries tried to update the same row. When trying to commit the version of the row in the second update, SQL Server noticed that the first transaction was also trying to update the same row. Since there is no way for the database engine to know who should win (that is actually a business decision) it had to kill one transaction.
This is similar in some ways to a deadlock that can occur under pessimistic, transactional isolation. With transactional isolation, a deadlock is detected when two or more transactions have a lock conflict since they want to acquire one or more incompatible locks. Using row versioning, you can get an update conflict, as I did. The difference is that a lock conflict happens before the transaction is committed, whereas an update conflict happens during the commit, as the database engine checks the rows in the version store against the committed rows and finds a mismatch.
Well, I can hear you asking, “How is this better than transactional isolation?!” Good question. The answer is that, when used appropriately, using row versioning can increase your system concurrency. The example I gave before of a data warehouse with separate periods of updating and reading comes to mind. If your system is read-heavy, at least periodically, consider using snapshot isolation. If it is a mix, or even update-heavy, you’ll need to experiment a bit to see which isolation mode gives you the best throughput with the least headaches.
Tracking tempdb usage
The version store uses tempdb when processing transactions using row-versioning. You need to keep an eye on that! Fortunately, SQL Server provides several DMVs to help you. One of the most helpful is
sys.dm_tran_version_store_space_usage
Here’s an example of it in action after completing the queries above:
|
1 2 3 4 5 6 7 8 9 10 |
-- 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()); |
Running that I obtained this result:
There is not very much in use by the version store, but then I only updated one row! You can use this query to keep track of your space usage. You might also want to explore using extended events to show when tempdb is filling up due to the version store.
For more in-depth information on configuring and monitoring tempdb, see these SQLShack articles:
Summary
Snapshot isolation is a great alternative to transactional isolation when you have read-heavy loads. If you want to use it with mixed read/write loads, prepare for update conflicts and be sure to keep an eye on tempdb performance and growth.
Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.
You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight
View all posts by Gerald Britton
- Snapshot Isolation in SQL Server - August 5, 2019
- Shrinking your database using DBCC SHRINKFILE - August 16, 2018
- Partial stored procedures in SQL Server - June 8, 2018