鎖定讀、UPDATE 或 DELETE 通常會給在SQL陳述句處理程序掃描到的每個索引記錄上設定記錄鎖，陳述句中是否存在排除該行的WHERE條件并不重要，InnoDB不記得確切的WHERE條件，但只知道哪些索引范圍被掃描了，鎖通常是next-key鎖，它也阻止插入到緊挨著記錄之前的“間隙”中，然而，間隙鎖定可以顯式禁用，這會導致next-key鎖無法使用，事務隔離級別也會影響到鎖的設定，
如果在搜索中使用了二級索引，并且要設定的索引記錄鎖是互斥的，InnoDB也會檢索相應的聚集索引記錄并對它們設定鎖，
如果沒有適合陳述句的索引，MySQL必須掃描整個表來處理該陳述句，那么表的每一行都將被鎖定，從而阻止其他用戶對表的所有插入，創建良好的索引非常重要，這樣查詢就不會掃描不必要的行，
InnoDB設定的鎖的具體型別如下：
1、SELECT ... FROM 是一個一致讀，讀取資料庫的快照并且不設定鎖，除非將事務隔離級別設定為SERIALIZABLE，
2、唯一索引的 SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 陳述句為掃描的行獲取鎖，并釋放不符合包含在結果集中條件的行（例如，如果它們不滿足WHERE子句中給定的條件）的鎖，然而，在某些情況下，行可能不會立即被解鎖，因為在查詢執行期間，結果行與其原始源之間的關系丟失了，例如，在UNION中，從表中掃描(和鎖定)的行可能會在評估它們是否符合結果集之前插入臨時表，在這種情況下，臨時表中的行與原始表中的行之間的關系將丟失，而后者的行直到查詢執行結束才被解鎖，
3、對于鎖定讀（帶有 FOR UPDATE 或 FOR SHARE 的 SELECT）、UPDATE 和 DELETE 陳述句，所采用的鎖取決于陳述句是使用具有唯一搜索條件的唯一索引，還是使用范圍型別的搜索條件，
- 對于具有唯一搜索條件的唯一索引，InnoDB 只鎖定找到的索引記錄，而不鎖定它之前的間隙，
- 對于其他搜索條件和非唯一索引，InnoDB鎖定所掃描的索引范圍，使用間隙鎖或next-key鎖阻止其他會話對該范圍覆寫的間隙進行插入，?
4、對于搜索遇到的索引記錄，SELECT ... FOR UPDATE 阻止其他會話執行 SELECT ... FOR SHARE 或從某些事務隔離級別讀取，一致讀忽略在讀視圖中存在的記錄上設定的任何鎖，
5、UPDATE ... WHERE ... 在搜索遇到的每條記錄上設定一個排它的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，只需要一個索引記錄鎖，
6、DELETE FROM ... WHERE ... 在搜索遇到的每條記錄上設定一個排它的next-key鎖，但是，對于使用唯一索引來搜索唯一行的陳述句，只需要一個索引記錄鎖，
7、當 UPDATE 修改聚集索引記錄時，將對受影響的二級索引記錄進行隱式鎖定，在插入新的二級索引記錄之前執行重復檢查掃描，以及在插入新的二級索引記錄時，UPDATE操作還會在受影響的二級索引記錄上使用共享鎖，
8、INSERT 在插入的行上設定排它鎖，這個鎖是索引記錄鎖，不是next-key鎖（即沒有間隙鎖），并且不會阻止其他會話插入到插入行之前的間隙中，
9、在插入行之前，設定了一種稱為插入意向間隙鎖的間隙鎖，該鎖表示要以這樣一種方式插入，即插入到同一索引間隙的多個事務如果不在間隙內的同一位置插入，則不需要相互等待，假設有值為4和7的索引記錄，嘗試插入值5和6的獨立事務在獲得插入行上的排它鎖之前，每個事務都用插入意向鎖鎖定4和7之間的間隙，但不會阻塞彼此，因為行是不沖突的，
10、INSERT ... ON DUPLICATE KEY UPDATE 與簡單的 INSERT 不同之處在于，當發生重復鍵錯誤時，將在要更新的行上放置排他鎖而不是共享鎖，對重復的主鍵值采用排它索引記錄鎖，對重復的唯一鍵值采用排它的next-key鎖，
11、INSERT INTO T SELECT ... FROM S WHERE ... 在插入到 T 的每一行上設定一個排它索引記錄鎖（沒有間隙鎖），如果事務隔離級別為 READ COMMITTED，InnoDB 將 S 上的搜索作為一致 讀（無鎖）， 否則，InnoDB 在 S 的行上設定共享的 next-key 鎖，
12、InnoDB在初始化表上先前指定的AUTO_INCREMENT列時，會在與AUTO_INCREMENT列相關的索引的末尾設定一個排它鎖，
13、如果在表上定義了一個FOREIGN KEY約束，那么任何需要檢查約束條件的插入、更新或洗掉都會在它查看的記錄上設定共享記錄級別的鎖，以檢查約束，InnoDB會在約束失敗的情況下也設定這些鎖，
14、LOCK TABLES設定表鎖，但是設定這些鎖的是InnoDB層之上的MySQL層，如果innodb_table_locks = 1(默認值)和autocommit = 0, InnoDB就知道表鎖，而且InnoDB上面的MySQL層知道行級鎖，
https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
給表現加一個意向排它鎖，然后給行id=4的行加排它的記錄鎖，以及正無窮
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500867.html
標籤：MySQL
上一篇：MVCC多版本并發控制的理解