SQL Server uses two methods to ensure transactional consistency and protects the data that is being accessed, these are Locks and Row-Versioning, these methods ensure that you manage your data concurrency effectively by specifying the level of access other transactions have to the data being processed, the game here is to balance either resources or data integrity against concurrency.

To get started, lets introduce a few simple definitions that we will be measuring the isolation levels effect against:

Dirty Reads: A statement reading uncommitted data.

Phantom Reads: Results between the same statements are different within one transaction.

Concurrency: The level of non-blocking introduced by an isolation level and how it interacts with other transactions. Higher concurrency means less blocking, but could mean lower transactional consistency.

Equivalent Table-level Lock: To provide finer control, transaction isolation levels can be set on the whole transaction, or on specific tables within a transaction (or statements) using table hints, here we will introduce the table hints that are equivalent to the transaction isolation level we are dealing with.

We will start by introducing the isolation levels that deals with transactional consistency through locking rather than row-versioning.

READ UNCOMMITTED – Lock Based

Uncommitted data is read and other transactions are allowed to modify the data being read.

• Locking: No lock is placed.
• Dirty Reads: Yes
• Phantom Reads: Yes
• Concurrency Control: Pessimistic
• Concurrency: Very High
• Equivalent Table-level Lock:
  • NOLOCK: Not allowed for UPDATE or DELETE operations, SQL Server will revert to a suitable lock for those operations.
  • READUNCOMMITTED: Not allowed for UPDATE or DELETE operations, SQL Server will revert to a suitable lock for those operations.

• Data Modification by Other Transactions within a Statement: Allowed
• Data Modification by Other Transactions between Statements: Allowed
• Data Insert by Other Transactions within Lock Range: Allowed

READ COMMITTED – Lock Based

READ COMMITTED is the default SQL Server behavior when it comes to transaction isolation.

• Locking: Shared locks
  • Row Locks: Released when next row is processed.
  • Page Locks: Released when next page is processed.
  • Table Locks: Released when the statement within the transaction is finished.
• Dirty Reads: No
• Phantom Reads: Yes
• Concurrency Control: Pessimistic
• Concurrency: High
• Equivalent Table-level Lock:
  • READCOMMITTED: Abides by READ_COMMITTED_SNAPSHOT database flag if set and uses row versioning instead of locks
  • READCOMMITTEDLOCK: Does not abide by the READ_COMMITTED_SNAPSHOT flag and always acquires a lock (rather than use row versioning).

• Data Modification by Other Transactions within a Statement: Not allowed
• Data Modification by Other Transactions between Statements: Allowed
• Data Insert by Other Transactions within Lock Range: Allowed

REPEATABLE READ – Lock Based

• Locking: Shared locks
  • Row Locks: Released when next row is processed.
  • Page Locks: Released when next page is processed.
  • Table Locks: Released when the statement within the transaction is finished.
• Dirty Reads: No
• Phantom Reads: Yes
• Concurrency Control: Pessimistic
• Concurrency: Medium
• Equivalent Table-level Lock: REPEATABLEREAD

• Data Modification by Other Transactions within a Statement: Not allowed
• Data Modification by Other Transactions between Statements: Not Allowed
• Data Insert by Other Transactions within Lock Range: Allowed

SERIALIZABLE – Lock Based

• Locking: Range locks which are kept until the end of the transaction.
• Dirty Reads: No
• Phantom Reads: No
• Concurrency Control: Pessimistic
• Concurrency: Low
• Equivalent Table-level Lock:
  • SERIALIZABLE
  • HOLDLOCK: Holds the lock for a table in a statement until the transaction is complete.

• Data Modification by Other Transactions within a Statement: Not allowed
• Data Modification by Other Transactions between Statements: Not Allowed
• Data Insert by Other Transactions within Lock Range: Not Allowed

SNAPSHOT – Row Versioning Based

the SNAPSHOT isolation level uses row-versioning rather than locking in order to maintain transactional consistency and protect data. The ALLOW_SNAPSHOT_ISOLATION database flag needs to be set before using this isolation level.

• Locking: No locks, uses row-versioning instead,
• Dirty Reads: No.
• Phantom Reads: No.
• Concurrency Control: Optimistic.
• Equivalent Table-level Lock:
  • READCOMMITTED with READ_COMMITTED_SNAPSHOT ON: This will produce a similar row-versioning way of protecting data, rather than using locks.

• Data Modification by Other Transactions within a Statement: Not allowed
• Data Modification by Other Transactions between Statements: Not allowed
• Data Insert by Other Transactions within Lock Range: Not allowed

Snapshot isolation transaction aborted due to update conflict…

READ COMMITTED with READ_COMMITTED_SNAPSHOT ON – Row Versioning Based

When the READ_COMMITTED_SNAPSHOT flag is set to ON in a particular database, this changes the default behavior of the READ COMMITTED isolation level to use Row-Versioning rather than Locking in order to establish transactional consistency. Since READ COMMITTED is the default behavior of SQL Server, this means that any transaction that is not using any isolation level hint will start using Row-Versioning when this flag is set (given the defaults have not been tampered with), without having to specify any additional flags.

With using Row-Versioning, users can take advantage of non-blocking reads, but do not escape the issues associated with Phantom Reads that are experienced with the READ COMMITTED transaction isolation level.

• Locking: No locks, uses row-versioning instead,
• Dirty Reads: No.
• Phantom Reads: Yes.
• Concurrency Control: Optimistic.
• Equivalent Table-level Lock:
  • READCOMMITTED with READ_COMMITTED_SNAPSHOT ON: This will produce a similar row-versioning way of protecting data, rather than using locks.

• Data Modification by Other Transactions within a Statement: Not allowed
• Data Modification by Other Transactions between Statements: Allowed
• Data Insert by Other Transactions within Lock Range: Allowed

Resources:

• SET TRANSACTION ISOLATION LEVEL on MSDN
• Snapshot Isolation in SQL Server on MSDN
• Transaction Isolation and the New Snapshot Isolation Level by Paul Alcon on SQLTeam

SQL Server Locking Control and Transaction Isolation Levelsrated 3 out of 5 by 1 readers