SQL Server TRANSACTION ISOLATION LEVEL
TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
- can read rows that have been modified by other transactions but not yet committed
- do not issue shared locks to prevent other transactions from modifying data read by the current transaction.
READ COMMITTED
- cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
- phantom data possible
REPEATABLE READ
- 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.
- Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes.
- Other transactions can insert new rows that match the search conditions of statements issued by the current transaction
- phantom data possible
SERIALIZABLE
- 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.
- Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction.
- 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.
- if any of the statements in a transaction are executed a second time, they will read the same set of rows. - phantom data not possible
-
• The Read Committed Isolation Model is SQL Server’s default behavior. In this model, the database does not allow transactions to read data written to a table by an uncommitted transaction. This model protects against dirty reads, but provides no protection against phantom reads or non-repeatable reads.
• The Read Uncommitted Isolation Model offers essentially no isolation between transactions. Any transaction can read data written by an uncommitted transaction. This leaves the transactions vulnerable to dirty reads, phantom reads and non-repeatable reads.
• The Repeatable Read Isolation Model goes a step further than the Read Committed model by preventing transactions from writing data that was read by another transaction until the reading transaction completes. This isolation model protect against both dirty reads and non-repeatable reads.
• The Serializable Isolation Model uses range locks to prevent transactions from inserting or deleting rows in a range being read by another transaction. The Serializable model protects against all three concurrency problems.
• The Snapshot Isolation Model also protects against all three concurrency problems, but does so in a different manner. It provides each transaction with a “snapshot” of the data it requests. The transaction may then access that snapshot for all future references, eliminating the need to return to the source table for potentially dirty data.
