14.5.3 Locks Set by Different SQL Statements in InnoDB通過不同的SQL語句設置的鎖 在InnoDB中一個鎖定讀, 一個UPDATE 或者一個DELETE 通常設置record locks 在每個Index record 他不管是否有WHERE 條件在與距離 會排除記錄。InnoDB 不記住準確的WHERE 條件, 只是知道哪個index range 是被掃描。locks通常是 next-key locks 也堵塞inserts 到"gap" 在那個記錄前。然而,區間鎖可以顯示的關閉,這將導致 next-key locking不能被使用如果一個secondary index 是用于一個搜索,index record locks 會被設置為排他。InnoDB 也會檢索相應的clustered index records 在它們上面設置鎖如果你沒有合適的索引用于你的語句,MySQL 必須掃描整個表來處理語句,表的每行記錄都會被鎖定，從而阻止其他用于所有的插入到這個表。創建好的索引是重要的,這樣你的查詢不會掃描很多的記錄對于SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, 鎖是需要的對于掃描的記錄期望被釋放對于記錄不符合納入結果集的( 比如,如果你不滿足給定的WHERE 條件的標準).然而, 在一些例子中,記錄可能不會被立即鎖定 因為一個結果集和他的原始資料的關系是丟失了再查詢執行時。比如,在一個UNION 掃描的(被鎖定的)的記錄從一個表可能會被插入到一個臨時表 InnoDB 設置鎖的特定類型:1.SELECT ... FROM 是一個一致性讀, 從數據庫讀取快照 沒有設置鎖除非事務隔離是設置為SERIALIZABLE對于SERIALIZABLE level,搜索設置共享的next-key locks 在index records.然而,只有Index record lock 是需要的 用于語句 鎖定記錄使用一個unique index 來搜索唯一行2.SELECT ... FROM ... LOCK IN SHARE MODE 設置共享next-key locks 在所有的搜索遇到的index 記錄。然而, 只有一個index record lock 是需要的用于語句 鎖定記錄使用一個unique index 來搜索唯一的記錄3.SELECT ... FROM ... FOR UPDATE 設置一個排他的 next-key lock 在每條搜索遇到的記錄。然而, 只有一個Index record lock 是需要的用于語句 locks rows 使用一個唯一索引來搜搜唯一的記錄4.對于搜搜遇到的index records,SELECT ... FROM ... FOR UPDATE 堵塞其他會話 執行SELECT ... FROM ... LOCK IN SHARE MODE或者 在某些事物蛤蜊級別。一致性讀 會忽略任何鎖定5.UPDATE ... WHERE ... 設置一個排他的e next-key lock 在每個記錄。然而, 只有一個Index record 鎖是需要的用于語句當locks row 是使用一個唯一索引來進行唯一搜索6.當UPDATE 協議一個clustered index record, 隱式的鎖是被設置影響secondary index records.UPDATE 操作也會設置shared locks 在影響的secondary index records 當執行重復的檢查掃描7.DELETE FROM ... WHERE ... 設置一個排他的 next-key lock 在每個搜索遇到的記錄然而,只有一個index record lock 是需要的用于語句 lock rows 使用一個唯一索引來搜索一個唯一的記錄8.在插入行之前,一種區間鎖類型被稱為insert intention gap lock 被設置。lock 表明意向插入以這種方式多個事務插入到相同的index gap 不需要相互等待如果他們不是插入相同的位置。假設有index record 值為4和7,單獨的事務嘗試插入值5和6 每個lock 區間在4和7 使用插入意向鎖來得到排他鎖在插入的記錄,但是不會相互堵塞 因為記錄是不沖突的如果一個重復的key錯誤發生,一個共享鎖在重復的index上被設置。這個共享的鎖可以導致死鎖 有多個會話嘗試插入相同的記錄 如果另外的會話已經有一個排他鎖。這個可以發生如果另外的會話刪除記錄 ,假設一個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); --hangSession 3: START TRANSACTION; INSERT INTO t1 VALUES(1);Session 1: ROLLBACK;Session 1的第一個操作 需要的排他鎖用于記錄,session 2和session 3 都導致一個重復鍵錯誤 他們都請求一個共享鎖用于記錄。當session 1回滾, 它釋放他的排他鎖在記錄上 共享鎖請求對于session 2和3 是被授予。在這個時間點,sessions 2 and 3 deadlock: 兩者都不能獲得一個排他鎖用于記錄 因為共享鎖被其他持有類似的情況發生 如果表已經包含一個只為1 ,然后3個會話執行相同的操作:mysql> select * from t1; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec)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 和session 3 操作都導致一個重復鍵錯誤他們都請求記錄的排他鎖。當session 1提交, 他釋放他的排他鎖在記錄上 session 2和session 3請求的共享鎖被授權。在這個時間點,session 2和session 3死鎖兩者都不能獲得一個排他鎖對于記錄 因為共享鎖被另外一個人持有1.?INSERT ... ON DUPLICATE KEY UPDATE 不同一個簡單的插入 一個排他next-key lock 而不是一個共享鎖2.REPLACE 是像一個INSERT 如果沒有沖突在一個唯一鍵。否則,一個排他的next-key lock是放置在replaces 的記錄上3.INSERT INTO T SELECT ... FROM S WHERE ... 設置一個排他鎖 record lock(沒有間隙鎖) 在每個插入的記錄。如果事務隔離級別是READ COMMITTED,或者 innodb_locks_unsafe_for_binlog 是被啟用的 且事務隔離級別不是SERIALIZABLE,CREATE TABLE ... SELECT ... 執行SELECT YOU shared next-key locks或者作為一個一致性讀,和 INSERT ... SELECT. 當初始化一個先前指定的 AUTO_INCREMENT 列在一個表上,InnoDB 設置一個排他鎖在 索引相關自增列的尾部。

轉載于:https://www.cnblogs.com/zhaoyangjian724/p/6199050.html

總結

以上是生活随笔為你收集整理的14.5.3 Locks Set by Different SQL Statements in InnoDB的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。