MySQL- 鎖(2)

標籤：

InnoDB行鎖實現方式

InnoDB行鎖是通過給索引上的索引項目加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在資料區塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發效能。下面通過一些實際例子來加以說明。

（1）在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。

在如表20-9所示的例子中，開始tab_no_index表沒有索引：

1

表20-9   InnoDB儲存引擎的表在不使用索引時使用表鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_no_index where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_no_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_no_index where id = 2 for update; 等待

在如表20 -9所示的例子中，看起來session_1隻給一行加了獨佔鎖定，但session_2在請求其他行的獨佔鎖定時，卻出現了鎖等待！原因就是在沒有索引的情況下，InnoDB只能使用表鎖。當我們給其增加一個索引後，InnoDB就只鎖定了合格行，如表20-10所示。

建立tab_with_index表，id欄位有普通索引：

1

表20-10   InnoDB儲存引擎的表在使用索引時使用行鎖例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from tab_with_index where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from tab_with_index where id = 2 for update; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)

（2）由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。應用設計的時候要注意這一點。

在如表20-11所示的例子中，表tab_with_index的id欄位有索引，name欄位沒有索引：

1

表20-11 InnoDB儲存引擎使用相同索引鍵的阻塞例子       

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 and name = ‘1‘ for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | +------+------+ 1 row in set (0.00 sec)
	雖然session_2訪問的是和session_1不同的記錄，但是因為使用了相同的索引，所以需要等待鎖： mysql> select * from tab_with_index where id = 1 and name = ‘4‘ for update; 等待

（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對資料加鎖。

在如表20-12所示的例子中，表tab_with_index的id欄位有主鍵索引，name欄位有普通索引：

1

表20-12  InnoDB儲存引擎的表使用不同索引的阻塞例子

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tab_with_index where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 1    | | 1    | 4    | +------+------+ 2 rows in set (0.00 sec)
	Session_2使用name的索引訪問記錄，因為記錄沒有被索引，所以可以獲得鎖： mysql> select * from tab_with_index where name = ‘2‘ for update; +------+------+ | id   | name | +------+------+ | 2    | 2    | +------+------+ 1 row in set (0.00 sec)
	由於訪問的記錄已經被session_1鎖定，所以等待獲得鎖。： mysql> select * from tab_with_index where name = ‘4‘ for update;

（4）即便在條件中使用了索引欄位，但是否使用索引來檢索資料是由MySQL通過判斷不同執行計畫的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計畫，以確認是否真正使用了索引。關於MySQL在什麼情況下不使用索引的詳細討論，參見本章“索引問題”一節的介紹。

在下面的例子中，檢索值的資料類型與索引欄位不同，雖然MySQL能夠進行資料類型轉換，但卻不會使用索引，從而導致InnoDB使用表鎖。通過用explain檢查兩條SQL的執行計畫，我們可以清楚地看到了這一點。

例子中tab_with_index表的name欄位有索引，但是name欄位是varchar類型的，如果where條件中不是和varchar類型進行比較，則會對name進行類型轉換，而執行的全表掃描。

1

間隙鎖（Next-Key鎖）

當我們用範圍條件而不是相等條件檢索資料，並請求共用或獨佔鎖定時，InnoDB會給合格已有資料記錄的索引項目加鎖；對於索引值在條件範圍內但並不存在的記錄，叫做“間隙（GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101，下面的SQL：

1

是一個範圍條件的檢索，InnoDB不僅會對合格empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖。

InnoDB使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離等級的要求，對於上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大於100的任何記錄，那麼本事務如果再次執行上述語句，就會發生幻讀；另外一方面，是為了滿足其恢複和複製的需要。有關其恢複和複製對鎖機制的影響，以及不同隔離等級下InnoDB使用間隙鎖的情況，在後續的章節中會做進一步介紹。

很顯然，在使用範圍條件檢索並鎖定記錄時，InnoDB這種加鎖機制會阻塞符合條件範圍內索引值的並發插入，這往往會造成嚴重的鎖等待。因此，在實際應用開發中，尤其是並發插入比較多的應用，我們要盡量最佳化商務邏輯，盡量使用相等條件來訪問更新資料，避免使用範圍條件。

還要特別說明的是，InnoDB除了通過範圍條件加鎖時使用間隙鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用間隙鎖！

在如表20-13所示的例子中，假如emp表中只有101條記錄，其empid的值分別是1,2,......,100,101。

表20-13                InnoDB儲存引擎的間隙鎖阻塞例子

session_1	session_2
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
當前session對不存在的記錄加for update的鎖： mysql> select * from emp where empid = 102 for update; Empty set (0.00 sec)
	這時，如果其他session插入empid為201的記錄（注意：這條記錄並不存在），也會出現鎖等待： mysql>insert into emp(empid,...) values(201,...); 阻塞等待
Session_1 執行rollback： mysql> rollback; Query OK, 0 rows affected (13.04 sec)
	由於其他session_1回退後釋放了Next-Key鎖，當前session可以獲得鎖並成功插入記錄： mysql>insert into emp(empid,...) values(201,...); Query OK, 1 row affected (13.35 sec)

恢複和複製的需要，對InnoDB鎖機制的影響

MySQL通過BINLOG錄執行成功的INSERT、UPDATE、DELETE等更新資料的SQL語句，並由此實現MySQL資料庫的恢複和主從複製（可以參見本書“管理篇”的介紹）。MySQL的恢複機制（複製其實就是在Slave Mysql不斷做基於BINLOG的恢複）有以下特點。

l  一是MySQL的恢複是SQL語句級的，也就是重新執行BINLOG中的SQL語句。這與Oracle資料庫不同，Oracle是基於資料庫檔案塊的。

l  二是MySQL的Binlog是按照事務提交的先後順序記錄的，恢複也是按這個順序進行的。這點也與Oralce不同，Oracle是按照系統更新號（System Change Number，SCN）來恢複資料的，每個事務開始時，Oracle都會分配一個全域唯一的SCN，SCN的順序與事務開始的時間順序是一致的。

從上面兩點可知，MySQL的恢複機制要求：在一個事務未提交前，其他並發事務不能插入滿足其鎖定條件的任何記錄，也就是不允許出現幻讀，這已經超過了ISO/ANSI SQL92“可重複讀”隔離等級的要求，實際上是要求事務要序列化。這也是許多情況下，InnoDB要用到間隙鎖的原因，比如在用範圍條件更新記錄時，無論在Read Commited或是Repeatable Read隔離等級下，InnoDB都要使用間隙鎖，但這並不是隔離等級要求的，有關InnoDB在不同隔離等級下加鎖的差異在下一小節還會介紹。

另外，對於“insert  into target_tab select * from source_tab where ...”和“create  table new_tab ...select ... From  source_tab where ...(CTAS)”這種SQL語句，使用者並沒有對source_tab做任何更新操作，但MySQL對這種SQL語句做了特別處理。先來看如表20-14的例子。

表20-14                   CTAS操作給原表加鎖例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = ‘1‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 1    |  1 | |  5 | 1    |  1 | |  6 | 1    |  1 | |  7 | 1    |  1 | |  8 | 1    |  1 | +----+------+----+ 5 rows in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = ‘1‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 1    |  1 | |  5 | 1    |  1 | |  6 | 1    |  1 | |  7 | 1    |  1 | |  8 | 1    |  1 | +----+------+----+ 5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = ‘1‘; Query OK, 5 rows affected (0.00 sec) Records: 5  Duplicates: 0  Warnings: 0
	mysql> update source_tab set name = ‘1‘ where name = ‘8‘; 等待
commit;
	返回結果 commit;

在上面的例子中，只是簡單地讀 source_tab表的資料，相當於執行一個普通的SELECT語句，用一致性讀就可以了。ORACLE正是這麼做的，它通過MVCC技術實現的多版本資料來實現一致性讀，不需要給source_tab加任何鎖。我們知道InnoDB也實現了多版本資料，對普通的SELECT一致性讀，也不需要加任何鎖；但這裡InnoDB卻給source_tab加了共用鎖定，並沒有使用多版本資料一致性讀技術！

MySQL為什麼要這麼做呢？其原因還是為了保證恢複和複製的正確性。因為不加鎖的話，如果在上述語句執行過程中，其他事務對source_tab做了更新操作，就可能導致資料恢複的結果錯誤。為了示範這一點，我們再重複一下前面的例子，不同的是在session_1執行事務前，先將系統變數 innodb_locks_unsafe_for_binlog的值設定為“on”（其預設值為off），具體結果如表20-15所示。

表20-15                   CTAS操作不給原表加鎖帶來的安全問題例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql>set innodb_locks_unsafe_for_binlog=‘on‘ Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = ‘1‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 1    |  1 | |  5 | 1    |  1 | |  6 | 1    |  1 | |  7 | 1    |  1 | |  8 | 1    |  1 | +----+------+----+ 5 rows in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from target_tab; Empty set (0.00 sec) mysql> select * from source_tab where name = ‘1‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 1    |  1 | |  5 | 1    |  1 | |  6 | 1    |  1 | |  7 | 1    |  1 | |  8 | 1    |  1 | +----+------+----+ 5 rows in set (0.00 sec)
mysql> insert into target_tab select d1,name from source_tab where name = ‘1‘; Query OK, 5 rows affected (0.00 sec) Records: 5  Duplicates: 0  Warnings: 0
	session_1未提交，可以對session_1的select的記錄進行更新操作。 mysql> update source_tab set name = ‘8‘ where name = ‘1‘; Query OK, 5 rows affected (0.00 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> select * from source_tab where name = ‘8‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 8    |  1 | |  5 | 8    |  1 | |  6 | 8    |  1 | |  7 | 8    |  1 | |  8 | 8    |  1 | +----+------+----+ 5 rows in set (0.00 sec)
	更新操作先提交 mysql> commit; Query OK, 0 rows affected (0.05 sec)
插入操作後提交 mysql> commit; Query OK, 0 rows affected (0.07 sec)
此時查看資料，target_tab中可以插入source_tab更新前的結果，這符合應用邏輯： mysql> select * from source_tab where name = ‘8‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 8    |  1 | |  5 | 8    |  1 | |  6 | 8    |  1 | |  7 | 8    |  1 | |  8 | 8    |  1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id   | name | +------+------+ | 4    | 1.00 | | 5    | 1.00 | | 6    | 1.00 | | 7    | 1.00 | | 8    | 1.00 | +------+------+ 5 rows in set (0.00 sec)	mysql> select * from tt1 where name = ‘1‘; Empty set (0.00 sec) mysql> select * from source_tab where name = ‘8‘; +----+------+----+ | d1 | name | d2 | +----+------+----+ |  4 | 8    |  1 | |  5 | 8    |  1 | |  6 | 8    |  1 | |  7 | 8    |  1 | |  8 | 8    |  1 | +----+------+----+ 5 rows in set (0.00 sec) mysql> select * from target_tab; +------+------+ | id   | name | +------+------+ | 4    | 1.00 | | 5    | 1.00 | | 6    | 1.00 | | 7    | 1.00 | | 8    | 1.00 | +------+------+ 5 rows in set (0.00 sec)

從上可見，設定系統變數innodb_locks_unsafe_for_binlog的值為“on”後，InnoDB不再對source_tab加鎖，結果也符合應用邏輯，但是如果分析BINLOG的內容：

12345678910111213141516171819202122

可以發現，在BINLOG中，更新操作的位置在INSERT...SELECT之前，如果使用這個BINLOG進行資料庫恢複，恢複的結果與實際的應用邏輯不符；如果進行複製，就會導致主從資料庫不一致！

通過上面的例子，我們就不難理解為什麼MySQL在處理“Insert  into target_tab select * from source_tab where ...”和“create  table new_tab ...select ... From  source_tab where ...”時要給source_tab加鎖，而不是使用對並發影響最小的多版本資料來實現一致性讀。還要特別說明的是，如果上述語句的SELECT是範圍條件，InnoDB還會給源表加間隙鎖（Next-Lock）。

因此，INSERT...SELECT...和 CREATE TABLE...SELECT...語句，可能會阻止對源表的並發更新，造成對源表鎖的等待。如果查詢比較複雜的話，會造成嚴重的效能問題，我們在應用中應盡量避免使用。實際上，MySQL將這種SQL叫作不確定（non-deterministic）的SQL，不推薦使用。

如果應用中一定要用這種SQL來實現商務邏輯，又不希望對源表的並發更新產生影響，可以採取以下兩種措施：

¡  一是採取上面樣本中的做法，將innodb_locks_unsafe_for_binlog的值設定為“on”，強制MySQL使用多版本資料一致性讀。但付出的代價是可能無法用binlog正確地恢複或複製資料，因此，不推薦使用這種方式。

¡  二是通過使用“select * from source_tab ... Into outfile”和“load data infile ...”語句組合來間接實現，採用這種方式MySQL不會給source_tab加鎖

MySQL- 鎖(2)