Isolation levels and consistency

You can control the degree to which the operations in one transaction are visible to the operations in other concurrent transactions by setting the isolation level.

You do this using the isolation_level database option. The isolation levels of individual tables in a query are controlled with corresponding table hints.

The following isolation levels are provided:

This isolation level...	Has these characteristics...
0 - read uncommitted	Read permitted on row with or without write lock No read locks are applied No guarantee that concurrent transaction will not modify row or roll back changes to row Corresponds to table hints NOLOCK and READUNCOMMITTED Allow dirty reads, non-repeatable reads, and phantom rows
1 - read committed	Read only permitted on row with no write lock Read lock acquired and held for read on current row only, but released when cursor moves off the row No guarantee that data will not change during transaction Corresponds to table hint READCOMMITTED Prevent dirty reads Allow non-repeatable reads and phantom rows
2 - repeatable read	Read only permitted on row with no write lock Read lock acquired as each row in the result set is read, and held until transaction ends Corresponds to table hint REPEATABLEREAD Prevent dirty reads and non-repeatable reads Allow phantom rows
3 - serializable	Read only permitted on rows in result without write lock Read locks acquired when cursor is opened and held until transaction ends Corresponds to table hints HOLDLOCK and SERIALIZABLE Prevent dirty reads, non-repeatable reads, and phantom rows
snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read or updated by the transaction
statement-snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read by the statement
readonly-statement-snapshot¹	No read locks are applied Read permitted on any row Database snapshot of committed data is taken when the first row is read by a read-only statement Uses the isolation level (0, 1, 2, or 3) specified by the updatable_statement_isolation option for an updatable statement

¹ Snapshot isolation must be enabled for the database by setting the allow_snapshot_isolation option to On for the database.

The default isolation level is 0, except for Open Client, jConnect, and TDS connections, which have a default isolation level of 1.

Lock-based isolation levels prevent some or all interference. Level 3 provides the highest level of isolation. Lower levels allow more inconsistencies, but typically have better performance. Level 0 (read uncommitted) is the default setting.

The snapshot isolation levels prevent all interference between reads and writes. However, writes can still interfere with each other. Few inconsistencies are possible and contention performance is the same as isolation level 0. Performance not related to contention is worse because of the need to save and use row versions.

In general, each isolation level is characterized by the types of locks needed and by how locks held by other transactions are treated. At isolation level 0, the database server needs only write locks. It makes use of these locks to ensure that no two transactions make modifications that conflict. For example, a level 0 transaction acquires a write lock on a row before it updates or deletes it, and inserts any new rows with a write lock already in place.

Level 0 transactions perform no checks on the rows they are reading. For example, when a level 0 transaction reads a row, it does not check what locks may or may not have been acquired on that row by other transactions. Since no checks are needed, level 0 transactions are fast. This speed comes at the expense of consistency. Whenever transactions read a row that is write locked by another transaction, they risk returning dirty data. At level 1, transactions check for write locks before they read a row. Although one more operation is required, these transactions are assured that all the data they read is committed.

Note

All isolation levels guarantee that each transaction executes completely or not at all, and no updates are lost.

The isolation is between transactions only: multiple cursors within the same transaction cannot interfere with each other.