|
MySQL InnoDB事務(wù)的隔離級別有四級,，默認(rèn)是“可重復(fù)讀”（REPEATABLE READ）,。 - 未提交讀（READ UNCOMMITTED）。另一個事務(wù)修改了數(shù)據(jù),，但尚未提交，而本事務(wù)中的SELECT會讀到這些未被提交的數(shù)據(jù)（臟讀）。
- 提交讀（READ COMMITTED）,。本事務(wù)讀取到的是最新的數(shù)據(jù)（其他事務(wù)提交后的）。問題是,，在同一個事務(wù)里,，前后兩次相同的SELECT會讀到不同的結(jié)果（不重復(fù)讀）。
- 可重復(fù)讀（REPEATABLE READ）,。在同一個事務(wù)里,，SELECT的結(jié)果是事務(wù)開始時時間點(diǎn)的狀態(tài)，因此,，同樣的SELECT操作讀到的結(jié)果會是一致的,。但是，會有幻讀現(xiàn)象（稍后解釋）,。
- 串行化（SERIALIZABLE）,。讀操作會隱式獲取共享鎖，可以保證不同事務(wù)間的互斥,。
四個級別逐漸增強(qiáng),，每個級別解決一個問題。 - 臟讀,，最容易理解,。另一個事務(wù)修改了數(shù)據(jù)，但尚未提交,，而本事務(wù)中的SELECT會讀到這些未被提交的數(shù)據(jù),。
- 不重復(fù)讀。解決了臟讀后,，會遇到,，同一個事務(wù)執(zhí)行過程中，另外一個事務(wù)提交了新數(shù)據(jù),，因此本事務(wù)先后兩次讀到的數(shù)據(jù)結(jié)果會不一致,。
- 幻讀。解決了不重復(fù)讀,，保證了同一個事務(wù)里,，查詢的結(jié)果都是事務(wù)開始時的狀態(tài)（一致性）。但是，如果另一個事務(wù)同時提交了新數(shù)據(jù),，本事務(wù)再更新時,，就會“驚奇的”發(fā)現(xiàn)了這些新數(shù)據(jù)，貌似之前讀到的數(shù)據(jù)是“鬼影”一樣的幻覺,。
借鑒并改造了一個搞笑的比喻： - 臟讀,。假如，中午去食堂打飯吃,，看到一個座位被同學(xué)小Q占上了,，就認(rèn)為這個座位被占去了，就轉(zhuǎn)身去找其他的座位,。不料,，這個同學(xué)小Q起身走了,。事實(shí)：該同學(xué)小Q只是臨時坐了一小下,，并未“提交”。
- 不重復(fù)讀,。假如，中午去食堂打飯吃,，看到一個座位是空的,，便屁顛屁顛的去打飯，回來后卻發(fā)現(xiàn)這個座位卻被同學(xué)小Q占去了,。
- 幻讀,。假如，中午去食堂打飯吃,，看到一個座位是空的,，便屁顛屁顛的去打飯，回來后,，發(fā)現(xiàn)這些座位都還是空的（重復(fù)讀）,，竊喜。走到跟前剛準(zhǔn)備坐下時,，卻驚現(xiàn)一個恐龍妹，嚴(yán)重影響食欲,。仿佛之前看到的空座位是“幻影”一樣,。
------ 一些文章寫到InnoDB的可重復(fù)讀避免了“幻讀”（phantom read），這個說法并不準(zhǔn)確,。 做個試驗(yàn)：(以下所有試驗(yàn)要注意存儲引擎和隔離級別) mysql> show create table t_bitfly\G;
CREATE TABLE `t_bitfly` (
`id` bigint(20) NOT NULL default '0',
`value` varchar(32) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk mysql> select @@global.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
試驗(yàn)一： t Session A Session B
|
| START TRANSACTION; START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
| INSERT INTO t_bitfly
| VALUES (1, 'a');
|
| SELECT * FROM t_bitfly;
| empty set
| COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit, 剛剛明明告訴我沒有這條記錄的)
如此就出現(xiàn)了幻讀,，以為表里沒有數(shù)據(jù)，其實(shí)數(shù)據(jù)已經(jīng)存在了,，傻乎乎的提交后,，才發(fā)現(xiàn)數(shù)據(jù)沖突了。 試驗(yàn)二： t Session A Session B
|
| START TRANSACTION; START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | a |
| +------+-------+
| INSERT INTO t_bitfly
| VALUES (2, 'b');
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | a |
| +------+-------+
| COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | a |
| +------+-------+
|
| UPDATE t_bitfly SET value='z';
| Rows matched: 2 Changed: 2 Warnings: 0
| (怎么多出來一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | z |
| | 2 | z |
| +------+-------+
|
v
本事務(wù)中第一次讀取出一行,，做了一次更新后,，另一個事務(wù)里提交的數(shù)據(jù)就出現(xiàn)了。也可以看做是一種幻讀,。 ------ 那么,，InnoDB指出的可以避免幻讀是怎么回事呢,？ http://dev./doc/refman/5.0/en/innodb-record-level-locks.html By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).
準(zhǔn)備的理解是,，當(dāng)隔離級別是可重復(fù)讀，且禁用innodb_locks_unsafe_for_binlog的情況下,，在搜索和掃描index的時候使用的next-key locks可以避免幻讀,。 關(guān)鍵點(diǎn)在于，是InnoDB默認(rèn)對一個普通的查詢也會加next-key locks,，還是說需要應(yīng)用自己來加鎖呢,？如果單看這一句，可能會以為InnoDB對普通的查詢也加了鎖,，如果是,，那和序列化（SERIALIZABLE）的區(qū)別又在哪里呢？ MySQL manual里還有一段： 13.2.8.5. Avoiding the Phantom Problem Using Next-Key Locking (http://dev./doc/refman/5.0/en/innodb-next-key-locking.html) To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking enables you to “lock” the nonexistence of something in your table.
我的理解是說,，InnoDB提供了next-key locks,，但需要應(yīng)用程序自己去加鎖。manual里提供一個例子： SELECT * FROM child WHERE id > 100 FOR UPDATE;
這樣,，InnoDB會給id大于100的行（假如child表里有一行id為102）,，以及100-102，102+的gap都加上鎖,。 可以使用show innodb status來查看是否給表加上了鎖,。 再看一個實(shí)驗(yàn)，要注意,，表t_bitfly里的id為主鍵字段,。實(shí)驗(yàn)三： t Session A Session B
|
| START TRANSACTION; START TRANSACTION;
|
| SELECT * FROM t_bitfly
| WHERE id<=1
| FOR UPDATE;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | a |
| +------+-------+
| INSERT INTO t_bitfly
| VALUES (2, 'b');
| Query OK, 1 row affected
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id | value |
| +------+-------+
| | 1 | a |
| +------+-------+
| INSERT INTO t_bitfly
| VALUES (0, '0');
| (waiting for lock ...
| then timeout)
|
|
