Thursday, September 18, 2008

Isolation levels in SQL Server 2005

Read Uncommitted:
  • Least restrictive isolation level.
  • Transactions only ensure that they do not read physically corrupt data.
    Dirty reads, Nonrepeatable reads, Phantom reads are possible.

Read Committed with snapshot:
New to SQL Server 2005
When a transaction references rows that have been updated by another transaction, read operations retrieve the version of the row that existed when the snapshot transaction started.
Dirty reads are prevented.
Nonrepeatable reads, Phantom reads are possible.

Read Committed with locking:
Default isolation level used by SQL Server 2005.
Read operations cannot access data that other transactions are currently modifying.
Dirty reads are prevented.
Nonrepeatable reads, Phantom reads are possible.

Repeatable Read:
The first transaction locks all the rows that its reading so that other transactions cannot update the data.
Dirty reads, Nonrepeatable reads are prevented.
Phantom reads are possible.

Snapshot:
New to SQL Server 2005
Read operations retrieve the version of the row that existed when the snapshot transaction started as it does not acquire locks.
Dirty reads, Nonrepeatable, Phantom reads are prevented.

Serializable:

Most restrictive isolation level.
Transactions are completely isolated from each other. This is implemented by Database engines frequently by locking large numbers of rows or entire tables.
Dirty reads, Nonrepeatable, Phantom reads are prevented.


No comments:

 
Google