ISOLATION LEVEL

（英文描述来自70-461 TRAINNING EXAM, 解释不一定准确；"*"为个人理解说明，已测供参。Some descriptions are from 70-461 Training Exam and may not be accurate. "*" parts are my notes. All tested in SQL Server 2012.）

READ UNCOMMITTED
You use the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement when you want to allow statements to read rows altered by other transactions, but those transactions have not yet been committed.

* Writer does NOT block Reader。Reader may read uncommitted data by writer (dirty read).

READ COMMITTED
You use the SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement when you want to ensure that statements cannot read data altered by other transactions when those transactions have not yet been committed. When you set the READ COMMITTED transaction level, data can be altered by other transactions between individual statements within the current transaction.
* When READ COMMITTED SNAPSHOT OFF：Writer blocks Reader.
* When READ COMMITTED SNAPSHOT ON[LZ1] ：
    ** Writer does NOT block Reader，READER will read original data.
    **READER may read different data because writer may change it during current transaction.

 [LZ1] 

ALTER DATABASE Testdb1 SET READ_COMMITTED_SNAPSHOT ON;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SNAPSHOT
You use the SET TRANSACTION ISOLATION LEVEL SNAPSHOT [LZ2] statement when you want to ensure that data read by any statement in a transaction will be transactionally consistent with how that data existed at the start of the transaction. SNAPSHOT transactions do not block other transactions from writing data.

* Writer does NOT block Reader，READER will read original data.
* READER will read original data even write change it or insert new rows during current transaction. No phantom reading (consistent).

[LZ2] 

ALTER DATABASE Testdb1 SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

REPEATABLE READ
You use the SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement when you want to ensure that statements cannot read data altered by other transactions but not yet committed and that other transactions cannot modify data read by the current transaction until the current transaction commits. When the REPEATABLE READ isolation level is set, it is possible for other transactions to insert new rows that meet the search conditions of statements included in the current transaction.

* Writer blocks Reader.
* Writer can NOT modify rows read by Reader, but insert new rows. Result in phantom read.

SERIALIZABLE
You use the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement when you want to ensure that Transact-SQL statements cannot read data that has been altered but not committed, that other transactions cannot modify data read by the current transaction until the transaction commits, and that other transactions cannot insert new rows with key values that would fall into the range read by statements in the current transaction until that transaction completes.

* WRITER  and READER block each other
* During READER's transaction, WRITER can NOT do any modification. no matter if it ”fall into the range”.（the underline part is not accurate.）

总结：

·         READ UNCOMMITTED, SNAPSHOT(both types): WRITER doesn't block READER

·         During the following types of READER transaction, the WRITER can …

	UPDATE	INSERT	Result in current READER transaction
READ UNCOMMITTED	O	O	Dirty data
READ COMMITTED (both types)	O	O	Rows change (not consistent)
SNAPSHOT	O	O	No rows change (consistent)
REPEATABLE READ	X	O	No rows change, but phantom read
SERIALIZABLE	X	X	No rows change

Based on personal study. Please point out any mistake if you find it. Thanks! 个人理解，如若有误，请指出，谢谢！