Isolation Level

Isolation Level

SQL-99 标准，定义了isolation levels的4个层级：

1. Read uncommitted (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read)
2. Read committed (SQLServer default level)
3. Repeatable read (MySQL default level)
4. Serializable (the highest level, where transactions are completely isolated from one another)

而并发中可能发生的3中不讨人喜欢的事情–并发边界效应：

• 1： Dirty read–读脏数据。也就是说，比如事务A的未提交（还依然缓存）的数据被事务B读走，如果事务A失败回滚，会导致事务B所读取的的数据是错误的。
• 2： non-repeatable read–数据不可重复读。比如事务A中两处读取数据-total-的值。在第一读的时候，total是100，然后事务B就把total的数据改成200，事务A再读一次，结果就发现，total竟然就变成200了，造成事务A数据混乱。
• 3： phantom read–幻象读数据，这个和non-repeatable reads相似，也是同一个事务中多次读不一致的问题。但是non-repeatable reads的不一致是因为他所要取的数据集被改变了（比如total的数据），但是phantom reads所要读的数据的不一致却不是他所要读的数据集改变，而是他的条件数据集改变。比如Select account.id where account.name="ppgogo*",第一次读去了6个符合条件的id，第二次读取的时候，由于事务b把一个帐号的名字由"dd"改成"ppgogo1"，结果取出来了7个数据。

下表显示了“并发边界效应(concurrency side effects)”在不同隔离级别的允许情况：

ISOLATION LEVEL	DIRTY READ	NONREPEATABLE READ	PHANTOM
Read uncommitted	Yes	Yes	Yes
Read committed	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshot(SQLServer独有)	No	No	No
Serializable	No	No	No

完整的文字论述，参见：http://technet.microsoft.com/en-us/library/ms173763(v=sql.90).aspx

===============================================================================
以下事例解释，转自：http://www.tangxiaojuan.info/4080/isolation-level/

Isolation就是ACID(Atomicity, Consistency, Isolation, Durability )中的I，isolation一般分为四个level: read uncommited, read commited, repeatable read, serializable read。

假设我们有一个user表：

id	name	age
1	John	18
2	Ann	30

Read Uncommited

T1	T2
start transaction; update user set age = 20 where id = 1;   rollback;	select * from user where id = 1;

T1先开始事务，更新John的年龄为20，然后T2查看John的年龄为20，而这时T1回滚事务，John的年龄依然为18，所以T2查看到的年龄有可能是并没有提交的数据。

为了避免这样的问题，我们有了read committed。

Read Committed

T1	T2
start transaction; update user set age = 20 where id = 1; commit;	start transaction;     select * from user where id = 1;

同上T1先开始事务，更新John的年龄为20，然后T2想查看John的年龄，但发现T1没有提交，所以直到等到T1回滚或者提交之后方能查看。T1提交或者回滚后，T2查看到John的年龄与数据库的数据一致。read committed同read uncommitted不同的就是加了write lock，只有当write的事务完成后(提交或者回滚)，才释放排他锁(exclusive lock)，另外的事务才能进行select或update。

但问题又来了，我们考虑以下的情况：

T1	T2
start transaction;   update user set age = 20 where id = 1; commit;	start transaction; select * from user where id =1;     select * from user where id = 1; commit;

如果在T1更新数据之前和之后，T2分别查看John的年龄，这时发现两次查看到的数据不同，分别为18和20。

为了避免这个问题，我们有了repeatable read。

Repeatable Read

T1	T2
start transaction;   update user set age = 20 where id = 1; commit;	start transaction; select * from user where id =1;     select * from user where id = 1; commit;

repeatable read同read committed不同之处就在于，read committed在T2读完某一行数据之后就释放排他锁，读完这行数据之后，T1就可以更新这行数据; 而repeatable read是在T2完成提交后才释放排他锁，这期间T1不能都不能进行其他操作。所以T1两次查询的数据是一样的，等T2提交之后T1就能更新了。

那么看上去repeatable read已经万无一失了，可是它只能应付”where id = xx”的情况，并不能对付”where id between xx and yy”的情况。所以我们有了serializable read

Serializable Read

T1	T2
start transaction;   insert into user values (3, ‘Jim’, 30); commit;	start transaction; select * from user where age between 20 and 40;     select * from user where age between 20 and 40; commit;

如果是repeatable read level, T2两次的查询不一致，因为T1这时插入了一条数据。而serializable read就加入了range lock, 这样就使得T2的两次查询一致了。

我们来总结下四个不同level的区别：

	write lock	read lock	range lock
read uncommitted	X	X	X
read committed	Y	X	X
repeatable read	Y	Y	X
serializable read	Y	Y	Y

另外MySQL默认支持的isolation level都是repeatable read。可以通过

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

来查询global和当前session的isolation level