Row Versioning-based Isolation Levels

Following on from my previous post on SQL Transaction Isolation Levels, we get to the juicier part of Row Versioning-based Isolation Levels. Read Committed Snapshot and Snapshot isolation levels allow for the use of row versioning.

By using the row versioning-based isolation levels, it will improve read concurrency by eliminating locks for read operations.

If you are creating an application to interact with the database, it is recommended that you use read committed isolation using row versioning over snapshot isolation.

Benefits of read committed isolation using row versioning

  • Consumes less tempdb space
  • Works with distributed transactions
  • Works with most existing applications. The behaviour of read committed, whether to use row versioning or not, is determined by the database option setting, and this can be changed without affecting the application
  • Snapshot isolation is vulnerable to update conflicts, whereas this is not applicable to read committed isolation using row versioning. When a transaction running under snapshot isolation reads data that is then modified by another transaction, an update by the snapshot transaction to the same data causes an update conflict and the transaction terminates and rolls back

Benefits of Row Versioning-based Isolation Levels

  • Read operations retrieve a consistent snapshot of the database
  • SELECT statements do not lock data during a read operation (readers do not block writers, and vice versa)
  • SELECT statements can access the last committed value of the row, while other transactions are updating the row without getting blocked
  • The number of deadlocks is reduced
  • The number of locks required by a transaction is reduced, which reduces the system overhead required to manage locks
  • Fewer lock escalations take place

Costs of Row Versioning-based Isolation Levels
With anything we decide to implement in a database we need to look at the costs and determine whether the benefits outweigh the costs. With this we weigh the cost of the concurrency benefit of minimizing locking against increased resource usage needed to maintain and read the row versions.

Below is the list of costs:

  • The biggest thing to be aware of is that the tempdb needs to have enough disk space. All versions generated by update transactions during the time the transaction is running is kept in the tempdb. If the tempdb runs out of space, update operations do not fail, but read operations using row versioning might fail
  • Read performance can be affected when versions needed by queries become old and long version chains must be scanned
  • Row versioning increases resource usage during data modification as row versions are maintained in¬†tempdb
  • When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level.
  • Row versioning information requires 14 bytes added to the database row
  • Update performance can be slower due to the work involved in maintaining row versions.
  • The performance cost of versioned updates could be higher when larger amounts of data change during update operations
  • Data readers face the extra cost of traversing the version link list. The older the snapshot, the slower the process of accessing it in a snapshot isolation transaction
  • Some update transactions using snapshot isolation might have to be rolled back because of mandatory conflict detection for update operations

Now the question comes in on when to use which isolation level for you to get the most benefit out of it after considering the cost that comes with using the isolation levels.

Use Read Committed Isolation Using Row Versioning
A new data snapshot is taken is taken as each statement within the transaction executes, this remains consistent for each statement until the statement finishes execution. This provides statement-level read consistency.

Enable read committed isolation using row versioning when:

  • The concurrency benefits of reader/writer blocking occurrences outweigh the increased overhead of creating and managing row versions
  • You require data values to be consistent to the point in time that a query starts
  • The application requires absolute accuracy for long-running aggregations

Snapshot Isolation
A new data snapshot is taken when the snapshot transaction starts, this remains consistent for the duration of the transaction. This provides transaction-level read consistency.

Enable snapshot isolation when:

  • Optimistic concurrency control is desired.
  • Probability is low that a transaction would have to be rolled back because of an update conflict.
  • An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency.

Limitations of Transactions Using Row Versioning-based Isolation Levels

  • READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master
  • Global temp tables are stored in tempdb. When accessing global temp tables inside a snapshot transaction, one of the following must happen:
    • Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb
    • Use an isolation hint to change the isolation level for the statement.
  • Snapshot transactions fail when:
    • A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database
    • If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.
  • Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.
  • SQL Server does not keep multiple versions of system metadata. Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

Differences between snapshot isolation and read committed isolation using row versioning

Hope this has been as interesting for you as it was for me!


SQL Transaction Isolation Levels

Isolation levels can be quite useful when retrieving data and inserting data to maintain data integrity. Isolation levels control the locking and row versioning behaviour between SQL statements issued by a connection to SQL server. It defines the degree to which one transaction must be isolated from resource or other modification made by other transactions.

There are 5 types of isolation levels

  • Read uncommitted
  • Read committed
  • Repeatable Read
  • Serializable
  • Snapshot

Read Uncommitted
This allows you to read rows that have been modified by other transaction but not yet committed. This is the lowest isolation level. Read Uncommitted does not use shared locks and is not blocked by exclusive locks. This option has the same effect as setting a NOLOCK on all tables in a SELECT statement in a transaction.

Read Committed
This allows you to only read records that have been modified and committed by other transactions. This prevents dirty reads from happening. The SQL server default will still allow non-repeatable reads and phantom reads; this is where the data ban be changed by other transactions between individual statements within the current transaction.

Repeatable Read
Statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read and are held until the transaction completes. Therefore, no transactions can modify any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. Phantom reads will occur if the current statement tries to read the new rows.

Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

This is exactly what it sounds like, this takes a snapshot of the data at time of execution. Any modification made to the data after the start of the transaction is not visible to the current transaction.

When a database is being recovered, snapshot transactions do not request lock transactions when reading data and they do not block other transactions from writing data.

Considering Isolation which level to use
When considering which isolation level to use, you need to think in terms of concurrency side effects, such as dirty reads, phantom reads or non-repeatable reads. You also need to consider the balancing of data integrity against the overhead of the isolation level.

The below table shows the concurrency side effects allowed by the different isolation levels.
Transaction isolation levels control the following:

  • Whether locks are taken when data is read, and what type of locks are requested.
  • How long the read locks are held.
  • Whether a read operation referencing rows modified by another transaction:
    • Block until the exclusive lock on the row is freed.
    • Retrieve the committed version of the row that existed at the time the statement or transaction started.
    • Read the uncommitted data modification.

Regardless of any isolation level set on a transaction, the transaction will always get an exclusive lock on any data it modifies and holds the lock until the transaction completes. If you choose to use a lower isolation level such as read uncommitted to increase the amount of possible users that can access the data at the same time, this shall also increase the number of concurrency effects such as dirty reads or lost updates that users may encounter.

A higher isolation level such as serializable will reduce the amount on concurrency effects, however it shall require more system resources and increases the possibility that one transaction will block another.

FILESTREAM-enabled databases support the following transaction isolation levels.
Look out for my next post on using row versioning-based isolation levels!