鎖定讀（locking read）、更新（UPDATE）或洗掉（DELETE）通常會在SQL陳述句處理程序中掃描的每個索引記錄上設定記錄鎖，陳述句中是否存在排除行的WHERE條件并不重要，InnoDB不記得確切的WHERE條件，而只知道哪個索引范圍被掃描，這些鎖通常是next-key鎖，它還會阻止在記錄之前插入“間隙”，然而，間隙鎖（gap lock）可以被顯式禁用，這會導致不使用next-key鎖，
如果在檢索中使用了二級索引，并且要設定的索引記錄鎖是排它的，則InnoDB也會檢索相應的聚集索引記錄并在它們上設定鎖，
執行SQL陳述句時，如果沒有找到可用的索引，MySQL必須掃描整個表來處理該陳述句，這樣的話表的每一行都會被鎖定，從而阻塞其他用戶對表的所有插入，因此，創建良好的索引非常重要，這樣可以避免掃描許多不必要的行，
InnoDB設定特定型別的鎖，如下所示：
-
SELECT ... FROM 是一致讀，讀取資料庫快照，除非將事務隔離級別設定為SERIALIZABLE，否則不設定鎖，對于SERIALIZABLE級別，檢索會在遇到的索引記錄上設定共享的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，只需要一個索引記錄鎖，
-
對于 SELECT ... FOR UPDATE 或者 SELECT ... LOCK IN SHARE MODE ，對掃描的行加鎖，并對不符合結果集中包含條件的行（例如，如果它們不滿足WHERE子句中給出的條件）釋放鎖，
-
SELECT ... LOCK IN SHARE MODE 在所有遇到的索引記錄上設定共享的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，只需要一個索引記錄鎖，
-
SELECT ... FOR UPDATE 在搜索遇到的每個記錄上設定排它的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，僅需要索引記錄鎖定，
-
UPDATE ... WHERE ... 在搜索遇到的每個記錄上設定排它的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，僅需要索引記錄鎖定，
-
DELETE FROM ... WHERE ... 在搜索遇到的每個記錄上設定排它的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，僅需要索引記錄鎖定，
-
INSERT 在插入的行上設定排他鎖，該鎖是索引記錄鎖，不是next-key鎖（即沒有間隙鎖），并且不會阻止其他會話插入到插入行之前的間隙中，
舉個例子，假設有一張表t1，結構如下：
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
再假設，有三個會話操作順序如下：
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;
首先Session1獲得i=1這一行的排它鎖，接下來Session2和Session3由于主鍵重復只能請求獲取該行的共享鎖，由于行上已經有排它鎖，因此Session2和Session3請求的共享鎖不能被立即授予，再接著，Session1回滾，行上的排它鎖被釋放，于是Session2和Session3在該行上都持有共享鎖，此時，死鎖發生了，由于對方持有的共享鎖，任何一方都不能獲得該行的排它鎖，
下面這組操作也是類似：
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;
和前面的情況類似，Session1提交以后，Session2和Session3請求該行上的共享鎖被立即授予，此時它們再請求獲取排它鎖時就出現死鎖了，因為共享鎖被另一個事務持有，
補充： 聚集索引與輔助索引
clustered index （譯：聚集索引、聚簇索引）
secondary index （譯：二級索引、輔助索引）
每個InnoDB表都有一個特殊的索引，稱為聚集索引，用于存盤行資料，通常，聚集索引與主鍵是同義詞，為了從查詢、插入和其他資料庫操作中獲得最佳性能，必須了解InnoDB如何使用聚集索引來優化每個表的最常見的查找和DML操作，
Typically, the clustered index is synonymous with the primary key.
通常，“clustered index” 和 “primary key” 是一個意思，
-
當你在表上定義一個PRIMARY KEY時，InnoDB將它用作聚集索引，為創建的每個表定義一個主鍵，如果沒有邏輯唯一的非空列或列集，請添加一個新的自動遞增（auto-increment）列，其值將自動填充，
-
如果你沒有為你的表定義一個PRIMARY KEY，則MySQL會在所有鍵列都不為NULL的情況下找到第一個唯一索引，并且InnoDB使用它作為聚集索引，
-
如果表沒有主鍵或合適的唯一索引，InnoDB會在包含行ID值的合成列上內部生成一個名為GEN_CLUST_INDEX的隱藏聚集索引
通過聚集索引訪問行非常快，因為索引搜索直接指向包含所有行資料的頁，如果表很大，聚集索引體系結構通常節省磁盤I/O操作，
除了聚集索引之外的所有索引都稱為二級索引，在InnoDB中，二級索引中的每條記錄都包含該行的主鍵列，以及為二級索引指定的列，InnoDB使用此主鍵值在聚集索引中搜索行，
如果主鍵很長，則輔助索引將使用更多空間，因此具有主鍵較短是比較有利的，
With the exception of spatial indexes, InnoDB indexes are B-tree data structures. Index records are stored in the leaf pages of their B-tree or R-tree data structure. The default size of an index page is 16KB. Supported sizes are 64KB, 32KB, 16KB (default), 8KB, and 4KB.
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/253945.html
標籤：其他
上一篇：MySql下載及簡介
下一篇：nodejs連接資料庫