14.3.3 Locks Set by Different SQL Statements in InnoDB 不同的SQL語句在InnoDB裡的鎖設定

14.3.3 Locks Set by Different SQL Statements in InnoDB  不同的SQL語句在InnoDB裡的鎖設定

locking read, 一個UPDATE,或者一個DELETE 通常設定record locks 在每個index record


它不關心 是否語句裡的WHERE條件會排除記錄


InnoDB 不記準确的WHERE 條件,隻是知道哪個index ranges 會被掃描。


鎖通常是next-key locks  會堵塞插入的區間。然而, gap locking


如果一個secondary index 是用于搜尋,index record locks 設定為排它鎖


InnoDB 也檢索相應的clustered index records 在它們上面加鎖

 shared and exclusive locks  的差別 在14.3.1, “InnoDB Locking”.有描述


如果沒有合适的索引對于你的語句,MySQL必須掃描整個表來處理語句,


表的每行記錄都會被鎖住,進而阻止所有行的插入。有必要建立合适的索引,是以你的查詢不必要掃描很多的

行。


對于

SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE,locks對于掃描的行是需要的,


期望被釋放對于這些記錄,不符合包含在結果集内的(比如,如果它們不符合WHERE條件的标準)


然而, 在有些情況下,記錄不會馬上解鎖 因為結果行和它的源頭之間的關系在查詢執行間丢失了,


比如, 在一個UNION裡,掃描的記錄(或者被鎖定的)記錄可能被插入到一個臨時表 



InnoDB 設定指定的鎖類型如下:

SELECT ... FROM 是一個一緻性讀,讀取一個資料快照,不加鎖 除非事務隔離級别設定為SERIALIZABLE.


對于SERIALIZABLE 級别,


SELECT ... FROM ... LOCK IN SHARE MODE  設定shared next-key locks 在所有的搜尋的記錄上。



對于搜尋遇到的index recods,SELECT ... FROM ... FOR UPDATE  堵塞其他會話進行

SELECT ... FROM ... LOCK IN SHARE MODE 


一緻性讀忽略任何的locks 設定在records上



UPDATE ... WHERE ... 設定一個排它的next-key lock 在搜尋遇到的每條記錄:




插入設定一個排它鎖 在插入的行, lock是一個index-record 鎖,


不是一個 next-key lock(that is, there is no gap lock) ,不阻止其他會話插入到這個區間(
在插入行前的區間)




Session 1:

mysql> mysql> show index from t5;
+-------+------------+----------+--------------+-------------+-----------+-------------

+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | 

Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------

+----------+--------+------+------------+---------+---------------+
| t5    |          0 | PRIMARY  |            1 | sn          | A         |          10 |     

NULL | NULL   |      | BTREE      |         |               |
| t5    |          0 | t5_idx1  |            1 | id          | A         |          10 |     

NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------

+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into t5(id,info) values(50,'a50');
Query OK, 1 row affected (0.00 sec)


Session 2:



mysql>  insert into t5(id,info) values(50,'a50'); --Hang




在插入該行之前, gap鎖類型被稱為一個插入間隙鎖 被設定。


這種鎖發出信号意向插入以這樣的方式, 多個事務插入到相同的index gap 


不需要互相等待 如果它們不是插入相同的gap位置。


假設 這裡有index records 值為4和7,

單獨的事務嘗試插入值5和6 每個都lock 4到7的區間 使用insert 意向鎖來得到排它鎖在插入的行,

但是不會互相堵塞 因為記錄是不沖突的


如果一個重複鍵錯誤發生, 一個共享鎖在重複index record 是被設定。

使用一個共享鎖 可以導緻死鎖 有多個會話嘗試插入相同的記錄如果其他的會話已經有一個排它鎖。


這個可能發生在如果session 删除了記錄,假設InnoDB 表t1 有下面的結構；




CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;



夾着 3個會話按順序執行下面的操作:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:

ROLLBACK;


Session 1的操作需要一個記錄的排它鎖,  Session 2和Session 3的操作都會導緻重複鍵錯誤,

都需要一個記錄的共享鎖。



當Session 1復原,它釋放它的記錄上排它鎖,排隊的共享鎖請求對于session 2和session 3被授權。


在這點上,session 2和3 死鎖:沒有一個能獲得排它鎖 因為被其他會話獲得了共享鎖


類似的情況發生 如果表已經包含 鍵值為1的記錄, 3個會話按順序執行下面的操作:

Session 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:

COMMIT;


session 1的第一個操作需要一個記錄的排它鎖, session 2和3的操作都會導緻一個重複鍵錯誤,

它們都請求一個記錄的共享鎖。當Session 1送出時,它釋放太的排它鎖

等待共享鎖請求的對于session 2和3是被授權,在這個時間點,session 2和3死鎖，


沒有一個能獲得排它鎖 因為共享鎖被其他會話占有


1.INSERT ... ON DUPLICATE KEY UPDATE  不同于簡單的插入 

一個排它 next-key lock 而不是一個共享鎖 被放置在記錄上


2.REPLACE 是像一個INSERT 如果沒有沖突在一個唯一鍵上,


否則,一個排它的next-key lock 是被放置在記錄上: