Mysql(Innodb)如何避免幻讀
幻讀Phantom Rows
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
幻讀問題是指一個事務的兩次不同時間的相同查詢傳回了不同的的結果集。例如:一個 select 語句執行了兩次，但是在第二次傳回了第一次沒有傳回的行,那麼這些行就是“phantom” row.
read view(或者說 MVCC)實作了一緻性不鎖定讀(Consistent Nonlocking Reads)，進而避免了(非目前讀下)幻讀
實驗1:
開兩個視窗設定
set session tx_isolation='REPEATABLE-READ';
select @@session.autocommit;select @@global.tx_isolation,@@session.tx_isolation;
create table read_view(text varchar(50));
insert into read_view values('init');
兩個會話開始事務
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A執行一個查詢，這個查詢可以通路任何表，這個查詢的目的是建立一個目前時間點的快照
START TRANSACTION WITH CONSISTENT SNAPSHOT;也可以達到同樣的效果
SESSION_A>select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)
SESSION_B 插入一條記錄并送出
SESSION_B>insert into read_view values('after session A select');
Query OK, 1 row affected (0.01 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A
SESSION_A>select * from read_view;
+------+
| text |
+------+
| init |
+------+
1 row in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+------------------------+
| text |
+------------------------+
| init |
| after session A select |
+------------------------+
2 rows in set (0.00 sec)
由于 SESSION_A 第一次的查詢開始于 SESSION_B 插入資料前，是以建立了一個以SELECT操作的時間為基準點的 read view,避免了幻讀的産生
是以在 SESSION_A 的事務結束前,無法看到 SESSION_B 對表 read_view 做出的任何更改 (insert,delete,update)
實驗2
兩個會話開始事務
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B 在 SESSION_A 建立read view 前插入資料
SESSION_B>insert into read_view values('before Session_A select');
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_A>commit
-> ;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
由于 SESSION_A 第一次查詢開始于 SESSION_B 對表做出更改并送出後,是以這次的 read view 包含了 SESSION_B 所做出的更改
在官方文檔中寫道
http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.
一緻性讀是通過 MVCC 為查詢提供了一個基于時間的點的快照。這個查詢隻能看到在自己之前送出的資料，而在查詢開始之後送出的資料是不可以看到的。一個特例是,這個查詢可以看到于自己開始之後的同一個事務産生的變化。這個特例會産生一些反常的現象
If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.
在預設隔離級别REPEATABLE READ下，同一事務的所有一緻性讀隻會讀取第一次查詢時建立的快照
實驗3
兩個會話開始事務
SESSION_A開始事務并建立快照
SESSION_A>START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_B>insert into read_view values('anomaly'),('anomaly');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
SESSION_B>update read_view set text='INIT' where text='init';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
+-------------------------+
3 rows in set (0.00 sec)
SESSION_A更新了它并沒有"看"到的行
SESSION_A>update read_view set text='anomaly!' where text='anomaly';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| init |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
觀察實驗步驟可以發現，在倒數第二次查詢中，出現了一個并不存在的狀态
the anomaly means that you might see the table in a state that never existed in the database
這裡A的前後兩次讀，均為快照讀，而且是在同一個事務中。但是B先插入直接送出，此時A再update，update屬于目前讀，是以可以作用于新插入的行，并且将修改行的目前版本号設為A的事務号，是以第二次的快照讀，是可以讀取到的，因為同僚務号。這種情況符合MVCC的規則，如果要稱為一種幻讀也非不可，算為一個特殊情況來看待吧。
With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.
在 read commit 隔離級别下，同一事務的每個一緻性讀sets and reads its own fresh snapshot.
實驗4
修改事務隔離級别
set session tx_isolation='READ-COMMITTED'
兩個會話開始事務
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
+-------------------------+
5 rows in set (0.00 sec)
SESSION_B>insert into read_view values('hehe');
Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from read_view;
+-------------------------+
| text |
+-------------------------+
| INIT |
| after session A select |
| before Session_A select |
| anomaly! |
| anomaly! |
| hehe |
+-------------------------+
6 rows in set (0.00 sec)
read commit 每次讀取都是新的快照
InnoDB通過Nextkey lock解決了目前讀時的幻讀問題
Innodb行鎖分為:
類型說明
Record Lock:
在索引上對單行記錄加鎖.
Gap Lock:
鎖定一個範圍的記錄,但不包括記錄本身.鎖加在未使用的空閑空間上,可能是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引之後的空間.
Next-Key Lock:
行鎖與間隙鎖組合起來用就叫做Next-Key Lock。鎖定一個範圍，并且鎖定記錄本身。對于行的查詢，都是采用該方法，主要目的是解決幻讀的問題。
實驗5
建立表
([email protected]) [fandb]> create table t5(id int,key(id));
Query OK, 0 rows affected (0.02 sec)
SESSION_A>insert into t5 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
開始實驗
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>select * from t5 where id=7 for update;
+------+
| id |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>insert into t5 values(2);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(12);
Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t5 values(5); --被阻塞^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
^[[AERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(7); --被阻塞^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>insert into t5 values(9); --被阻塞^CCtrl-C -- sending "KILL QUERY 93" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 4 |
| 7 |
| 10 |
+------+
4 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t5;
+------+
| id |
+------+
| 1 |
| 2 |
| 4 |
| 7 |
| 10 |
| 12 |
+------+
6 rows in set (0.00 sec)
當以目前讀模式select * from t5 where id=7 for update;擷取 id=7的資料時,産生了 Next-Key Lock,鎖住了4-10範圍和 id=7單個record
進而阻塞了 SESSION_B在這個範圍内插入資料，而在除此之外的範圍内是可以插入資料的。
在倒數第二個查詢中,因為 read view 的存在，避免了我們看到 2和12兩條資料，避免了幻讀
同時因為 Next-Key Lock 的存在,阻塞了其他回話插入資料，是以目前模式讀不會産生幻讀(select for update 是以目前讀模式擷取資料)
###盡量使用唯一索引,因為唯一索引會把Next-Key Lock降級為Record Lock
實驗6
建立表
(m[email protected]) [fandb]> create table t6(id int primary key);
Query OK, 0 rows affected (0.02 sec)
SESSION_A>insert into t6 values(1),(4),(7),(10);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
開始實驗
SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)
SESSION_A>select * from t6 where id=7 for update;+----+
| id |
+----+
| 7 |
+----+
1 row in set (0.00 sec)
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)
SESSION_B>insert into t6 values(5); --插入成功沒有阻塞Query OK, 1 row affected (0.00 sec)
SESSION_B>insert into t6 values(8); --插入成功沒有阻塞Query OK, 1 row affected (0.00 sec)
SESSION_B>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)
SESSION_A>commit;
Query OK, 0 rows affected (0.00 sec)
SESSION_A>select * from t6;
+----+
| id |
+----+
| 1 |
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
+----+
6 rows in set (0.00 sec)
當 id 列有唯一索引,Next-Key Lock 會降級為 Records Lock