SQL Server Isolation Levels

SQL Server Isolation Levels Notes (PASS 2013)

Atomicity - Everything succeeds or nothing succeeds. This is the idea with transactions.
Consitency - Data cannot be left in an inconsistent state.
Isolation - No transaction can interfere with any other transactions.

ANSI Isolation Levels

Level 0 : Read Uncommitted (the least isolated) - WITH NO LOCK query hint uses this. This is a dirty read. Has been modified but not committed.
Level 1: Read Committed - default behavior for SQL Server. Only committed data is readable. Locks are released as data is read. Page level lock.
Level 2: Repeatable Read - holds shared locks on the pages until the transaction is committed. Doesn't guarantee new data won't enter your set during the transaction. Causes many locks on the records.
Level 3: Serializable (the most isolated) - locks the entire range of keys to guarantee the data is accurate. Often uses a table lock and should only be used in rare circumstances. Can possibly control with partitions and filters.

Versioning

Snapshot Isolation - the committed version that it last knew about
Readers don't block writers
Doesn't block
No shared block,
Types - ALTER DATABASE SET READ_COMMITTED_SNAPSHOT WITH ROLLBACK IMMEDIATE. This should be the default and turns on versioning. Default will still use locking. This is the alternative to NO LOCK.
version store is in TempDB