Transactions - Isolation Levels

Always wanted to have these documented somewhere:

• Read Uncommitted - Lowest setting (best performer). Allow you to read a table that is currently being updated by another application. YOu run the risk of seeing data that has not been completely updated. Dirty Read
  
• Read Committed - Wait until any write locks are released. Slightly slower performer since the app must wait on other applications to release their locks.
  
• Repeatable Read - Prevents other apps from establishing write locks. Other apps will be able to establish a read lock, but not a write lock (??)
  
• Serializable - Establishes a read or write lock over the entire range of rows affected by the transaction. ex: "select * from foo" - write lock will be established on all rows in foo.
  

COM+ transaction that runs agains MS SQL Server applies isolation level of serializable. (Be aware - can usually lead to table-level locks. COM+ isolation level on Windows 2000 not configurable. Eluding to the fact that maybe it will be in configurable in .NET Server 2002 ?)

ACID

• Atomic - unit of work that completely succeeds or completely fails
  
• Consistent - start and end with data in a consistent state. No temporary commits, etc.
  
• Isolation - transactions occurring simultaneously must see each other's work. (usually leads to pretty "hefty" locking on rows, pages, or tables)
  
• Durable - once a transaction has been committed, its data must be stored in a permanent state that can survive a power outage
  

Posted 7:59 AM    |    0 comments    |    Permalink