1.mysql回表查詢

在這裡提起主要是用於說明mysql資料和索引的結構，有助於理解後續加鎖過程中的一些問題。

mysql索引結構和表資料結構是相互獨立的，根據索引查詢，只能找到索引列和主鍵聚簇索引。如果select語句中不包含索引列，mysql會根據主鍵聚簇索引二次回表查詢所需要的資料，查詢出來的資料是根據聚簇索引排序的。如果select中只包含索引列和聚簇索引，那麼mysql不會再根據聚簇索引回查表。而且查出來的資料根據查詢索引列是排序的。

需要注意的是，mysql即使沒有宣告主鍵，mysql也會預設根據一個非空列，生成聚簇索引。

我們有以下的表，其中name和age為索引列，id為主鍵：

CREATE TABLE `test_user_info` (
  `id` bigint(20) NOT NULL COMMENT '主鍵ID',
  `name` varchar(255) DEFAULT NULL COMMENT '使用者姓名',
  `age` int(11) DEFAULT NULL COMMENT '年齡',
  `salary` decimal(15,2) DEFAULT '100.00' COMMENT '獎金',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資訊表'

預設資料如下：

覆蓋查詢：如果執行SELECT NAME,id FROM my_test.test_user_info WHERE NAME IN ('zhangsan','lisi'),mysql是不會回表查詢的，因為索引資料結構上已經包含所需要查詢的所有值，且查詢出來的值是有序的。

回表查詢：如果執行SELECT NAME,id,age FROM dongfeng_batch.test_user_info WHERE NAME IN ('zhangsan','lisi'), 由於索引查詢列上無age欄位，mysql在根據name索引找到聚簇索引後，還要再根據聚簇索引回表查詢對應的age值，最後資料預設是根據主鍵id排序的。

查詢過程如下：

2.mysql innodb事務隔離級別

查詢mysql事務隔離級別：

SHOW VARIABLES LIKE 'transaction_isolation';

REPEATABLE-READ為mysql預設的事務隔離級別。

mysql InnoDB引擎下有以下4種隔離級別：

隔離級別	髒讀	不可重複讀	幻讀
讀未提交(READ UNCOMMITTED)	出現	出現	出現
讀已提交(READ COMMITTED)	不出現	出現	出現
可重複讀(REPEATABLE READ)	不出現	不出現	可能出現
序列化(SERIALIZABLE)	不出現	不出現	不出現

按照隔離級別水平從低到高排序，隔離級別越高，效能越差。

髒讀	一個事務存取到了另一個事務未提交的資料	事務A更新了id=1的資料的age為90但還未提交，事務B這時候去讀id=1的資料，發現age由25變為90。如果事務A回滾了，事務B讀取到的資料就是無效的。
不可重複讀	一個事務讀取同一條資料2次，得到得結果不一致	事務A讀取id=1的資料，age=25；這時候事務B更新id=1的資料，將age修改為90並提交。這時候事務A再去讀取，發現age=90，同一事務中對同一條資料讀取的不一致。
幻讀	一個事務讀取2次，得到的資料行數不一致	事務A讀取name=張三的資料，一次讀取出兩條；這時候事務B插入一條name=張三，age=18的資料並提交；事務A再次讀取name=張三的資料，這時候讀取出三條。

3.mysql鎖分類

3.1按鎖粒度分類

(1)表(Table Lock)級鎖：表級鎖是對整張表進行鎖定，它是mysql最基本的鎖策略，所有儲存引擎都支援。表鎖開銷最小，粒度最大，發生鎖衝突的概率最高，所以並行度最低，可以很好的避免死鎖問題。

(2)行級鎖：也稱為記錄鎖。行級鎖是最細粒度的鎖，它只鎖定資料表中的某一行資料。InnoDB引擎才支援行級鎖。行級鎖開銷大，粒度最小，發生鎖衝突概率低，所以並行度最高，容易產生死鎖。

(3)頁級鎖：頁級鎖是最粗粒度的鎖，它鎖定整個資料頁面，即資料庫中儲存資料的最小單位。在使用頁級鎖時，如果有多個使用者想要存取同一個頁面，則只有一個使用者能夠獲得鎖定，其他使用者要麼等待，要麼被阻塞。頁級鎖也會產生死鎖。BDB引擎支援頁級鎖。

三種級別的鎖分別對應儲存引擎關係如下：

引擎	行鎖	表鎖	頁鎖
MyISAM		支援
InnoDB	支援	支援
BDB		支援	支援

3.1.1表級鎖分類

表級鎖細分為以下幾種：

表讀鎖（共用鎖-S鎖）及表寫鎖（排他鎖-X鎖）

後設資料鎖（meta data lock)

自增鎖(AUTO-INC Locks)

意向鎖（Intention lock），意向鎖又分為意向共用鎖（intention shared lock,IS）：事務有意向對錶中的某些行加共用鎖（S鎖），意向排他鎖（intention exclusive lock,IX）：事務有意向對錶中的某些行加排他鎖（X鎖）。

3.1.1.1表讀鎖（共用鎖-S鎖）及表寫鎖（排他鎖-X鎖）

一般在MyISAM引擎中會用到，MyISAM引擎不支援行鎖。InnoDB也支援，但是一般需要手動獲取。

在對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，InnoDB儲存引擎是不會為這個表新增表級別的S鎖或者X鎖的。在對某個表執行一些諸如ALTER TABLE、DROP TABLE這類的DDL語句時，其他事務對這個表並行執行諸如SELECT、INSERT、DELETE、UPDATE的語句會發生阻塞。同理，某個事務中對某個表執行SELECT、INSERT、DELETE、UPDATE語句時，在其他對談中對這個表執行DDL語句會發生阻塞。這個過程其實是通過在server層使用一種稱之為後設資料鎖（英文名：Metadata Locks，簡稱MDL）結構來實現的。

需要注意的是，在SERIALIZABLE，任何查詢都是會加讀鎖的。

MyISAM引擎在對錶做查詢的時候不會顯式地加鎖，當一個查詢需要修改表中的資料（寫操作）時，會加上寫鎖，這會阻塞其他的寫操作和讀操作，直到該寫操作完成。

一般情況下，不會使用InnoDB儲存引擎提供的表級別的S鎖和X鎖。只會在一些特殊情況下，比方說崩潰恢復過程中用到。比如，在系統變數autocommit=0，innodb_table_locks=1時，手動獲取InnoDB儲存引擎提供的表t的S鎖或者X鎖可以這麼寫：
LOCK TABLES t READ：InnoDB儲存引擎會對錶t加表級別的S鎖。
LOCK TABLES t WRITE：InnoDB儲存引擎會對錶t加表級別的X鎖。

3.1.1.2意向鎖（Intention lock）

InnoDB支援，MyISAM不支援

上面說到，InnoDB儲存引擎在做SELECT、INSERT、DELETE、UPDATE操作的時候，不會為表加上S鎖或者X鎖的，但是會使用到意向鎖這種表級別鎖。MyISAM引擎是不支援意向鎖的。

意向鎖又分為意向共用鎖（intention shared lock,IS）：事務有意向對錶中的某些行加共用鎖（S鎖）；意向排他鎖（intention exclusive lock,IX）：事務有意向對錶中的某些行加排他鎖（X鎖）。事務在給一個資料行加共用鎖前必須取得該表的IS鎖；事務在給一個資料行加排他鎖前必須取得該表的IX鎖。意向鎖的引入主要是為了在進行行級鎖或頁級鎖時，提供一種機制來表示事務可能會對錶中的某些行或頁面進行鎖定操作的意向，從而提高並行控制的效率。

需要注意的是，意向鎖是MySQL內部自動管理的，通常不需要顯式地進行操作。在實際應用中，開發人員只需瞭解意向鎖的概念，而無需直接干預它們的使用。 MySQL會在需要時自動處理意向鎖，以確保事務的並行操作能夠正確進行

InnoDB支援多粒度鎖（multiple granularity locking），它允許行級鎖與表級鎖共存，而意向鎖就是其中的一種表鎖。
1、意向鎖的存在是為了協調行鎖和表鎖的關係，支援多粒度（表鎖與行鎖）的鎖並存。
2、意向鎖是一種不與行級鎖衝突表級鎖，這一點非常重要。
3、表明「某個事務正在某些行持有了鎖或該事務準備去持有鎖」。

如果沒有意向寫鎖，mysql在加行鎖之前，需要回圈掃描表，判斷表是否有行鎖。如果有了意向寫鎖，這樣，mysql在加行寫鎖時，如果判斷表上沒有意向寫鎖，可以直接加行寫鎖，無需掃描。

事務要獲取某些行的S鎖，必須先獲得表的IS鎖
SELECT column FROM table ... LOCK IN SHARE MODE;

事務要獲取某些行的X鎖，必須先獲得表的IX鎖。
SELECT column FROM table ... FOR UPDATE;

3.1.1.3自增鎖（AUTO-INC鎖）

InnoDB支援，MyISAM不支援

自增鎖通常是指對自增主鍵列的並行控制，這就要求我們設計表的時候，新增了一列做為自增主鍵才會使用到。在使用MyISAM儲存引擎時，對於自增主鍵列的並行控制並不是通過鎖來實現的，而是通過維護一個全域性計數器來實現的。MyISAM儲存引擎中的自增欄位是通過維護一個計數器來生成新的自增值，而不是通過加鎖的方式來保證唯一性。這也意味著在高並行的情況下，可能會出現自增值的重複或者不連續的情況。相比之下，InnoDB儲存引擎支援的自增欄位會通過鎖機制來保證並行插入時自增值的唯一性和連續性，從而避免了在高並行情況下可能出現的問題。

在資料庫中，插入資料的方式可以總結為三種：

簡單插入（Simple Inserts）：可以預先確定要插入的行數（當語句被初始處理時）的語句。包括沒有巢狀子查詢的單行和多行INSERT ... VALUES()和REPLACE語句。。
批次插入（Bulk Inserts）：事先不知道要插入的行數（和所需自動遞增值的數量）的語句。比如INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句，但不包括純INSERT。InnoDB在每處理一行，為AUTO_INCREMENT列分配一個新值。
混合模式插入（Mixed-mode Inserts）：這些是「Simple inserts」語句但是指定部分新行的自動遞增值。例如INSERT INTO teacher (id,name) VALUES (1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');只是指定了部分id的值。另一種型別的「混合模式插入」是INSERT ... ON DUPLICATE KEY UPDATE。

對於上面資料插入的案例，MySQL中採用了自增鎖的方式來實現，AUTO-INC鎖是當向使用含有AUTO_INCREMENT列的表中插入資料時需要獲取的一種特殊的表級鎖，在執行插入語句時就在表級別加一個AUTO-INC鎖，然後為每條待插入記錄的AUTO_INCREMENT修飾的列分配遞增的值，在該語句執行結束後，再把AUTO-INC鎖釋放掉。一個事務在持有AUTO-INC鎖的過程中，其他事務的插入語句都要被阻塞，可以保證一個語句中分配的遞增值是連續的。也正因為此，其並行性顯然並不高，當我們向一個由AUTO_INCREMENT關鍵字的主鍵插入值的時候，每條語句都要對這個表鎖進行競爭，這樣的並行潛力其實是很低下的，所以innodb通過innodb_autoinc_lock_mode的不同取值來提供不同的鎖定機制，來顯著提高SQL語句的可伸縮性和效能。

我們可以使用**SHOW VARIABLES LIKE '%innodb_autoinc_lock_mode%'**命令查詢自增鎖的鎖定模式。

innodb_autoinc_lock_mode有三種取值，分別對應與不同鎖定模式：
（1）innodb_autoinc_lock_mode = 0（「傳統」鎖定模式）
在此鎖定模式下，所有型別的insert語句都會獲得一個特殊的表級AUTO-INC鎖，用於插入具有AUTO_INCREMENT列的表。這種模式其實就如我們上面的例子，即每當執行insert的時候，都會得到一個表級鎖（AUTO-INC鎖），使得語句中生成的auto_increment為順序，且在binlog中重放的時候，可以保證master與slave中資料的auto_increment是相同的。因為表級鎖，當在同一時間多個事務中執行insert的時候，對於AUTO-INC鎖的爭奪會限制並行能力。

（2）innodb_autoinc_lock_mode = 1（「連續」鎖定模式）
在MySQL8.0之前，連續鎖定模式是預設的。
在這個模式下，「bulk inserts」仍然使用AUTO-INC表級鎖，並保持語句結束。這適用於所有INSERT … SELECT，REPLACE … SELECT和LOAD DATA語句。同一時刻只有一個語句可以持有AUTO-INC鎖。
對於「Simple inserts」（要插入的行數事先已知），則通過mutex（輕量鎖）的控制下獲得所需數量的自動遞增值來避免表級AUTO-INC鎖，它只在分配過程的持續時間內保持，而不是直到語句完成。不使用表級AUTO-INC鎖，除非AUTO-INC鎖由另一個事務保持。如果另一個事務保持AUTO-INC鎖，則「Simple inserts」等待AUTO-INC鎖。

（3）innodb_autoinc_lock_mode = 2（「交錯」鎖定模式）
從MySQL8.0開始，交錯模式是預設設定。
在這種鎖定模式下，所有類INSERT語句都不會使用表級AUTO-INC鎖，而是使用較為輕量的mutex鎖，並且可以同時執行多個語句。這是最快和最可延伸的鎖定模式。副作用就是單個Insert的自增值有可能是不連續的，因為AUTO_INCREMENT的值會在多個INSERT語句中來回交叉執行。

優點：效率高；

缺點：在並行情況下無法保持資料一致性。我們知道mysql通過Binlog主從同步有三種模式：statement，row，mixed；如果採用的是statement模式同步的資料，並且採用了交錯鎖定模式，資料會有不一致問題。

3.1.1.4後設資料鎖（meta data lock)

innodb支援，MyISAM不支援

MySQL5.5引入了meta data lock，簡稱MDL鎖，屬於表鎖範疇。MDL的作用是，保證讀寫的正確性。比如，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，增加了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。
因此，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對錶做結構變更操作的時候，加MDL寫鎖。
讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性，解決了DML和DDL操作之間的一致性問題。不需要顯式使用，在存取一個表的時候會被自動加上。

思考：我們在對錶做Alter操作的時候，是否能立即執行？

答案是不一定，如果此時還有事務在進行增刪改查操作，Alter操作會阻塞，必須等待所有事務執行完畢才能執行。

需要注意的是，我們在對大表做DDL的時候，有可能會造成資料庫崩潰。所以要求我們儘量在業務不繁忙的時候執行DDL，或者是使用第三方工具，如pt-online-schema-change等來安全的執行表的DDL操作。

3.1.1.5表級鎖相容性

	IS	IX	AUTO-INC	S	X
IS	相容	相容	相容	相容	不相容
IX	相容	相容	相容	不相容	不相容
AUTO-INC	相容	相容	不相容	不相容	不相容
S	相容	不相容	不相容	相容	不相容
X	不相容	不相容	不相容	不相容	不相容

3.1.2行級鎖分類

InnoDB引擎支援行級鎖，行級鎖只在儲存引擎層實現。

優點：鎖粒度小，發生鎖衝突概率低，並行度高；

缺點：鎖開銷較大，加鎖較慢，容易出現死鎖。

3.1.2.1記錄鎖（Record Locks）

在 MySQL 中，記錄鎖可以分為共用鎖（S鎖）和排他鎖（X鎖）。共用鎖允許多個事務同時對同一行進行讀取操作，但是不允許任何事務對該行進行寫入操作；而排他鎖則會阻止其他事務對該行進行讀取或寫入操作，直到持有排他鎖的事務釋放鎖定。記錄鎖始終鎖定索引記錄，即使表沒有定義索引。對於這種情況，InnoDB會建立一個隱藏的聚簇索引並使用這個索引進行記錄鎖定。

當事務需要修改資料時，會根據需要獲取相應的鎖，以確保並行修改操作的正確性和一致性。這些鎖會在事務提交或回滾時自動釋放。

記錄鎖也就是僅僅一條記錄鎖上，官方的型別名稱為：LOCK_REC_NOT GAP

當一個事務獲取了一條記錄的S型鎖後，其他事務也可以繼續獲取該記錄的S型記錄鎖，但不可以繼續獲取X型記錄鎖；
當一個事務獲取了一條記錄的X型記錄鎖後，其他事務即不可以繼續獲取該記錄的S型記錄鎖，也不可以繼續獲取X型記錄鎖。

3.1.2.2間隙鎖（Gap Locks）

間隙鎖（Gap Lock）是InnoDB引擎RR隔離級別下特有的鎖機制，用於防止幻讀（Phantom Reads），但不能完全避免幻讀。間隙鎖鎖定的是索引記錄之間的間隙，或者在第一個索引記錄之前或最後一個索引記錄之後的間隙。間隙鎖只會阻塞insert操作。

當多個事務執行範圍查詢並且涉及相同的間隙時，它們可以共用同一個間隙鎖。這意味著這些事務不會互相阻塞，它們可以同時持有同一個間隙鎖，並且可以讀取該間隙範圍內的資料。這種情況下的共用間隙鎖可以提高並行效能。此外，即使多個事務可以共用同一個間隙鎖，它們之間仍然可能存在衝突。例如，如果一個事務持有了間隙鎖並嘗試插入新的鍵值，而另一個事務持有了間隙鎖並嘗試在同一間隙內插入另一個新的鍵值，那麼它們之間仍然會發生衝突，其中一個事務將被阻塞。

然而，多個事務無法同時持有相互重疊的間隙鎖。這可能會導致一些事務被阻塞，直到其他事務釋放了鎖。

3.1.2.3臨鍵鎖（Next-Key Locks）

MySQL的臨鍵鎖（next-key lock）是一種加強版的間隙鎖，用於解決間隙鎖無法完全避免的幻讀問題。臨鍵鎖不僅會鎖定範圍內的間隙，還會鎖定範圍內的已有記錄。所以InnoDB就提出了一種稱之為Next-Key Locks的鎖，官方的型別名稱為：LOCK_ORDINARY，我們也可以簡稱為net-key鎖。Next-Key Locks是儲存引擎innodb、事務級別在RR(可重複讀)的情況下使用的資料庫鎖，innodb預設的鎖就是Next-Key locks

類似是一個記錄鎖和一個gap鎖的合體。gap鎖是允許多個事務持有相同間隙的，但臨鍵鎖是不允許多個事務共同持有的。臨鍵鎖鎖住的間隙是不允許其他事務操作的。它即能保護該條記錄，又能阻止別的事務將新紀錄插入被保護紀錄的間隙。

臨建鎖遵循左開右閉的原則。

需要注意的是，臨建鎖會退化。

退化規則如下：如果是RC隔離級別，是沒有間隙鎖的，只會有行鎖。

查詢描述	查詢值是否存在	加鎖情況
非唯一索引等值查詢	查詢值存在	加next-key Lock和間隙鎖
非唯一索引等值查詢	查詢值不存在	加next-key Lock，後退化為間隙鎖
非唯一索引範圍查詢	不管值是否存在	加next-key Lock，可能資料行加記錄鎖
唯一索引等值查詢	查詢值存在	加next-key Lock，但會退化為記錄鎖
唯一索引等值查詢	查詢值不存在	加next-key Lock，但會退化為間隙鎖
唯一索引範圍查詢	不管值是否存在	加next-key Lock，可能會退化為間隙鎖/記錄鎖
非索引列查詢	不管值是否存在	全表主鍵範圍內加臨鍵鎖

臨建鎖加鎖過程是一個比較複雜的過程，後面在mysql加鎖分析中舉例論證。

3.1.2.4插入意向鎖（Insert Intention Locks）

插入意向鎖是一種在 INSERT 操作之前設定的一種間隙鎖，插入意向鎖表示了一種插入意圖，即當多個不同的事務，同時往同一個索引的同一個間隙中插入資料的時候，它們互相之間無需等待，即不會阻塞（要是單純按照之前間隙鎖的理論，必須要等一個間隙鎖釋放了，下一個事務才可以往相同的間隙處插入資料）。假設有值為 4 和 7 的索引記錄，現在有兩個事務，分別嘗試插入值為 5 和 6 的記錄，在獲得插入行的排他鎖之前，每個事務使用插入意向鎖鎖定 4 和 7 之間的間隙，但是這兩個事務不會相互阻塞，因為行是不衝突的。

這就是插入意向鎖。插入意向鎖和臨建鎖和間隙鎖是互斥的。

3.1.2.5空間索引謂詞鎖(Predicate Locks for Spatial Indexes)

MySQL的空間索參照於處理地理空間資料，允許對儲存地理位置資訊的列進行高效的空間查詢。通過空間索引，可以加快對地理空間資料的搜尋、分析和操作。

在MySQL中，空間索引通常用於儲存地理位置座標、多邊形區域等地理空間資料。它可以支援多種空間函數，如計算兩點之間的距離、查詢某一點所在的區域等操作。

要建立空間索引，需要使用特定的索引型別，例如SPATIAL索引。下面是一個建立空間索引的範例：

CREATE TABLE spatial_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    location POINT,
    SPATIAL INDEX(location)
);

在這個範例中，我們建立了一個名為spatial_table的表，其中包含一個用於儲存地理位置的location列。通過使用SPATIAL INDEX關鍵字，我們為location列建立了一個空間索引。

一旦建立了空間索引，就可以使用MySQL的空間函數來執行各種地理空間資料的查詢和分析操作。例如，可以使用ST_Distance函數計算兩個地理位置之間的距離，使用ST_Contains函數檢查一個區域是否包含另一個區域，等等。

總之，MySQL的空間索引為處理地理空間資料提供了便利和高效性，使得開發地理資訊系統和地圖應用變得更加容易和高效。

空間索引的謂詞鎖實際上是一種行級鎖。當使用空間索引進行查詢時，InnoDB會在涉及到的行上設定謂詞鎖，以確保其他事務不能插入或修改與查詢條件匹配的行。這樣可以保證空間資料的一致性和完整性。因此，謂詞鎖可以看作是對行級操作的一種鎖定機制。

4.mysql加鎖分析

文章結合mysql8.0+版本(8.0.35)的performance_schema.data_locks分析。

當前mysql5.7.30的information_schema.INNODB_LOCKS表不夠直觀，不能很輕鬆的看出加鎖情況。

mysql8.0+和mysql5.7.30在加鎖情況上沒有較大差異。後續所有操作都是基於mysql8.0.35。

備註：

mysql5.7.30要開啟information_schema.INNODB_LOCKS的功能，需開啟以下設定：

#開啟innodb鎖功能
SET GLOBAL innodb_status_output_locks = ON;

#查詢是否開啟
SHOW GLOBAL VARIABLES LIKE 'innodb_status_output_locks';

正常查詢不涉及行鎖，這裡不分析查詢的情況。資料庫預設使用RR隔離級別。

RR隔離級別下，對如下資料分析，name和age分別為普通索引，id為主鍵

資料說明

注意插入的順序，我這裡打亂了id=68和id=59的順序。mysql查詢鎖紀錄檔預設是按資料庫插入順序排序的，但是並不代表加鎖的順序。

insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('12','lisi','12','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('35','wangwu','49','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('47','zhangsan','13','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('68','zhaojiu','25','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('59','qianliu','43','100.00');

performance_schema.data_locks表說明：

如果 LOCK_MODE 為 X, REC_NOT_GAP，說明是記錄鎖；
如果 LOCK_MODE 為 X, GAP，說明是間隙鎖；
如果LOCK_MODE 為 X，說明是 next-key 鎖；

總結：（這是非常重要的結論，否則沒法直觀的通過performance_schema.data_locks表分析出加鎖情況）

1.LOCK_MODE=X,GAP型別，LOCK_DATA是間隙鎖的結束範圍，從該行資料向上掃描，鄰近的一行則是間隙鎖的開始範圍；

2.LOCK_MODE=X型別，LOCK_DATA是臨鍵鎖的尾，從該行向上掃描，鄰近的一行則是臨鍵鎖的開始範圍；如果表中沒有臨近的資料行，則是無窮小

3.LOCK_DATA=supremum pseudo-record，這個是無窮大的意思

4.非唯一索引記錄上加臨鍵鎖，會在對應資料行上加記錄鎖

5.不管是臨鍵鎖還是間隙鎖，會根據查詢條件向上向下延伸，找到不滿足條件的臨近的行；

下面的情況在分析的時候，結合總結來分析。

場景一:唯一索引等值查詢，查詢資料不存在

結論：加間隙鎖

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where id = 3

查詢資料不存在，查詢加鎖情況：

注意：以下返回值的順序不代表加鎖順序

我們發現：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X,GAP，INDEX_NAME=PRIMARY，說明在主鍵上被加了間隙鎖。

間隙鎖範圍id為(-∞,12)。為什麼是(-∞,12)。因為加鎖的時候，為了防止幻讀，鎖住對應的間隙，臨界範圍會根據查詢條件在資料庫中找不滿足查詢條件的相鄰的值。如果向上向下找不到不滿足條件的值，則是-∞和+∞。

mysql innodb引擎在RR級別下預設加的是臨鍵鎖。在當前場景下，臨鍵鎖會退化為間隙鎖。

3.如果其他事務SessionB想在id < 12的範圍內插入資料的話，事務會被阻塞。只有等待SessionA提交後才能執行。

場景二:唯一索引等值查詢，查詢資料存在

結論：加行鎖

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where id = 12

查詢資料存在，檢視加鎖情況：

我們發現：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，說明在資料行上被加了記錄鎖。

記錄鎖鎖定id=12的記錄。mysql innodb引擎在RR級別下預設加的是臨鍵鎖，在這種場景下，臨鍵鎖會退化為記錄鎖。

這時SessionB立刻插入id=5的資料，是不會被阻塞的。

場景三：唯一索引>=範圍查詢，等值查詢資料存在

結論：加行鎖和間隙鎖；

SessionA執行以下sql，不提交事務:

update test_user_info set salary = salary + 100 where id >= 59

id=59的資料存在，我們查詢加鎖情況：

此時加鎖情況已經較為複雜，我們發現：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，說明在資料行id=59上被加了記錄鎖。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=supremum pseudo-record，這個是無窮大的意思。說明id範圍在(68, +無窮大]的資料上被加了臨鍵鎖。

4.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=68。說明在(59, 68]的資料上被加了臨鍵鎖。

臨鍵鎖遵循左開右閉的原則。當查詢資料範圍為id>=59的時候，mysql掃描到id=59行的資料，資料存在，加行鎖。然後再掃描id > 59的情況，掃描到id=68的資料，所以在(59,68]上加臨鍵鎖。然後繼續掃描id>68的情況，此時表裡面沒有資料，所以在(68, +無窮大]的資料上被加了臨鍵鎖。

我們講過，臨建鎖類似記錄鎖+間隙鎖的組合，所以這時候，如果我們SessionB執行：

update test_user_info set salary = salary + 100 where id = 68，此時68的資料行上有臨鍵鎖，事務會被阻塞。

我們想在id >=59的範圍內插入資料，也是會被阻塞的。

場景四：唯一索引>=範圍查詢，等值查詢資料不存在，這種就類似於>範圍查詢

結論：加臨鍵鎖

SessionA執行以下sql，不提交事務:

update test_user_info set salary = salary + 100 where id >= 58

id=58的資料不存在，這種類似於>58的查詢，查詢加鎖情況。

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=supremum pseudo-record，這個是無窮大的意思。說明id範圍在(68, +無窮大]的資料上被加了臨鍵鎖。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=68。說明在(59, 68]的資料上被加了臨鍵鎖。

4.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=59。說明在(47, 59]的資料上被加了臨鍵鎖。為什麼這裡是47，因為加鎖的時候，為了防止幻讀，鎖住對應間隙，臨界條件會根據查詢條件在資料庫中找不滿足查詢條件的相鄰的值。我們這裡id >= 58, 相鄰的不滿足條件的值為id=47。

總結就是：唯一索引上的範圍查詢會存取到不滿足條件的第一個值為止。

如果我們SessionB執行：

insert into my_test.test_user_info values (48, 'pengwu', 30, 120)會被阻塞；

但是如果執行的插入資料id=46，則

可以執行，不被阻塞。

場景五：唯一索引<=範圍查詢，等值查詢資料存在

結論：只加臨鍵鎖；

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where id <= 35

id=35的資料存在，我們查詢加鎖情況：

![image-20231114113644445(https://pic.imgdb.cn/item/6556f082c458853aef358304.png)

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=12。說明id範圍在(-∞, 12]的資料上被加了臨鍵鎖。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=35。說明在(12, 35]的資料上被加了臨鍵鎖。

這裡和>=範圍查詢，等值資料存在場景不一樣的是，等值查詢id=35的資料不會加行鎖。這是因為臨鍵鎖左開右閉的原則。(12, 35]的臨鍵鎖範圍已經包含了資料id=35的行。

如果此時SessionB想在id範圍為：id<=35的行上做任何操作，insert，update，delete，哪怕where條件是其他列，但操作的行資料id<=35, 均被阻塞。

SessionB更新：update my_test.test_user_info set salary = salary + 100 where name = 'lisi'

資料id=12，被阻塞。

場景六：唯一索引<=範圍查詢，等值查詢資料不存在，這種就類似於<範圍查詢

結論：滿足條件的範圍加臨鍵鎖，不滿足條件的加間隙鎖；

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where id < 35

我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=12。說明id範圍在(-∞, 12]的資料上被加了臨鍵鎖。

3.LOCK_TYPE=RECORD，LOCK_MODE=X,GAP，INDEX_NAME=PRIMARY，LOCK_DATA=35。說明在(12, 35）的資料上被加了間隙鎖。這裡為什麼變成了間隙鎖，因為加鎖的時候，為了防止幻讀，鎖住對應的間隙，臨界範圍會根據查詢條件在資料庫中找不滿足查詢條件的相鄰的值。id=35則是向下不滿足條件的相鄰的值。mysql開始在

id為(12,35]的行上加臨鍵鎖。但是id=35的資料在範圍外，退化成了間隙鎖。為什麼這裡這麼特殊，我想的是，id=35的資料不在範圍內，沒必要加上行鎖，所以這裡就退化成間隙鎖。保證id=35行的資料可以被操作。這個和>=範圍的完全不同。

總結：<=的範圍查詢，如果查詢條件不在臨界值，臨鍵範圍是會退化成間隙鎖的。

SessionB此時是可以操作id=35的行資料的。

場景七：唯一索引>= and <=範圍查詢

這種基本是場景三，四，五，六的組合，沒有什麼特殊，即使等值查詢資料存在。

SessionA執行以下sql，不提交事務:

update test_user_info set salary = salary + 100 where id >= 12 and id <= 38

我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=12。說明id=12的資料行加了記錄鎖。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=PRIMARY，LOCK_DATA=35，說明id範圍為(12,35]的資料行上加了臨鍵鎖。

4.LOCK_TYPE=RECORD，LOCK_MODE=X,GAP，INDEX_NAME=PRIMARY，LOCK_DATA=47，說明id範圍為(35,47)的行上加了間隙鎖。

場景八：非唯一索引等值查詢，查詢資料不存在

結論：加間隙鎖；

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age = 15

我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X,GAP，INDEX_NAME=index_age，LOCK_DATA=25, 68。(25, 68)對應age=25，id=68的索引記錄。注意，mysql索引是有序的。

說明在(age,id)範圍為:(13,47)到(25,68)的索引資料上加了間隙鎖。那麼對於age=13或age=25的資料，能不能插入進去呢？

這還要根據id範圍確定。實際上，間隙鎖是加在了(13,47)和(25，68)的範圍。

如果我們此時要插入age=13，id=46的資料，是可以插入的。

但是我們要插入age=13, id=48的資料，就會被阻塞。

場景九：非唯一索引等值查詢，查詢資料存在

結論：非唯一索引加臨鍵鎖，間隙鎖；資料行加記錄鎖

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age = 13

我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(13,47)。說明

臨鍵鎖加在了age和id範圍在(12,12)到[13,47]之間。此時如果SessionB插入(age,id)=(12,11)的資料，是不會阻塞的。

3.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=47，說明id=47的資料行上加了記錄鎖。

4.LOCK_TYPE=RECORD，LOCK_MODE=X,GAP，INDEX_NAME=index_age，LOCK_DATA=(25,68)，說明(age,id)範圍在(13,47)到(25,68)範圍內被加上了間隙鎖。

場景十：非唯一索引>=範圍查詢，等值查詢資料存在

結論：非唯一索引記錄加臨鍵鎖，資料行加行鎖。

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age >= 43

age=43的記錄存在，我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(supremum pseudo-record)。說明(age,id)範圍在(49,35)到[age無窮大，id無窮大]的索引記錄上加了臨鍵鎖；SessionB在比(49,35)

範圍大的記錄上，是無法執行插入操作的。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(49,35)。說明(age,id)範圍在(43,59)到[49,35]的索引記錄上加了臨鍵鎖；

4.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(43,59)。說明(age,id)範圍在(25,68)到[43,59]的索引記錄上加了臨鍵鎖；

5.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=35。說明

id=35的行被加了記錄鎖；

6.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=59。說明

id=59的行被加了記錄鎖；

場景十一：非唯一索引>=範圍查詢，等值查詢資料不存在，這種就類似於>範圍查詢

非常類似場景十

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age > 43

age=43的記錄不存在，我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

範圍大的記錄上，是無法執行插入操作的。

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(49,35)。說明(age,id)範圍在(43,59)到[49,35]的索引記錄上加了臨鍵鎖；

4.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=35。說明

id=35的行被加了記錄鎖；

場景十二：非唯一索引<=範圍查詢，等值查詢資料存在

結論：非唯一索引加臨鍵鎖，資料行加行鎖

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age <= 13

age=13的記錄存在，我們查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(12,12)。說明(age,id)範圍在(age無窮小,id無窮小)到[12，12]的索引記錄上加了臨鍵鎖；

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(13,47)。說明(age,id)範圍在(12,12)到[13,47]的索引記錄上加了臨鍵鎖；

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(25,68]。說明(age,id)範圍在(13,47)到[25,68]的索引記錄上加了臨鍵鎖；

4.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=12。說明

id=12的行被加了記錄鎖；

5.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=47。說明

id=47的行被加了記錄鎖；

6.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=68。說明

id=68的行被加了記錄鎖；((25,68]的索引被加了臨鍵鎖，id對應的資料行需要加記錄鎖)

場景十三：非唯一索引<=範圍查詢，等值查詢資料不存在，這種就類似於<範圍查詢

結論：非唯一索引加臨鍵鎖，資料行加行鎖

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age < 14

age=14不存在，我們查詢加鎖情況，加鎖情況完全同場景十二：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(12,12)。說明(age,id)範圍在(age無窮小,id無窮小)到[12，12]的索引記錄上加了臨鍵鎖；

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(13,47)。說明(age,id)範圍在(12,12)到[13,47]的索引記錄上加了臨鍵鎖；

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(25,68]。說明(age,id)範圍在(13,47)到[25,68]的索引記錄上加了臨鍵鎖；

4.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=12。說明

id=12的行被加了記錄鎖；

5.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=47。說明

id=47的行被加了記錄鎖；

6.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=68。說明

id=68的行被加了記錄鎖；(這個不明白為什麼要在id=68的行資料上加記錄，可能是bug)

場景十四：非唯一索引>= and <=範圍查詢

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where age >=13 and age <= 44

查詢加鎖情況：

1.LOCK_TYPE=TABLE，LOCK_MODE=IX，說明在表上被加了IX意向鎖;

2.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(49,35)。說明(age,id)範圍在(43,59)到[49,35]的索引記錄上加了臨鍵鎖；

3.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(13,47)。說明(age,id)範圍在(12,12)到[13,47]的索引記錄上加了臨鍵鎖；

4.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(25,68)。說明(age,id)範圍在(13,47)到[25,68]的索引記錄上加了臨鍵鎖；

5.LOCK_TYPE=RECORD，LOCK_MODE=X，INDEX_NAME=index_age，LOCK_DATA=(43,59)。說明(age,id)範圍在(25,68)到[43,59]的索引記錄上加了臨鍵鎖；

6.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=35。說明

id=35的行被加了記錄鎖；

7.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=47。說明

id=47的行被加了記錄鎖；

8.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=68。說明

id=68的行被加了記錄鎖；

9.LOCK_TYPE=RECORD，LOCK_MODE=X,REC_NOT_GAP，INDEX_NAME=PRIMARY，LOCK_DATA=59。說明

id=59的行被加了記錄鎖；

場景十五：非索引列查詢

SessionA執行以下sql，不提交事務:

update my_test.test_user_info set salary = salary + 100 where salary = 100

檢視加鎖情況：

全表掃描，所有資料行id間的範圍都加上了臨鍵鎖。

(-∞,12],(12,35],(35,47],(47,59],(59,68],(68,+∞], 相當於給表加了X鎖。任何insert，update，delete操作均會被阻塞。

如果是多條件列，mysql會根據每個條件掃描到到的行，來判斷加鎖情況。

6.InnoDB死鎖

在mysql中，死鎖是不會發生在MyISAM引擎中。這是因為主要使用的是表鎖，而InnoDB中重點使用的是行鎖。

死鎖產生的四個條件：

1.互斥條件：一個資源每次只能被一個程序使用；

2.請求與保持條件：一個程序因請求資源而阻塞時，對已獲得的資源保持不放；

3.不可剝奪條件：程序已獲得的資源，在沒使用完成之前，不能強行剝奪；

4.迴圈等待條件：多個程序之間形成了一種相互迴圈等待資源的關係；

結合mysql，我們如何避免死鎖呢？

1.加鎖順序一致，主要打破迴圈等待條件；

2.儘量基於主鍵或唯一索引更新資料；

5.1表級死鎖產生場景

使用者A開啟事務，首先使用非索引列更新表t1，由於表t1沒有使用索引，導致t1相當於被加上表鎖；

使用者B開啟事務，首先使用非索引列更新表t2，由於表t2沒有使用索引，導致t2相當於被加上表鎖；

這時使用者A去更新表t2，使用者B去更新表t1。使用者A需要等待使用者B釋放表t2的表鎖，使用者B需要使用者A

釋放表t1的表鎖。

這種死鎖場景不常見，我們只要在程式中，保證表的更新順序即可。

5.2行級鎖死鎖產生場景

mysql因為行級鎖產生的死鎖情況較多，下面簡單列舉幾種情況。

場景1：id主鍵

sessionA:

update test_user_info set age =10 where id = 1；(執行時序1)

update test_user_info set age =10 where id = 12；(執行時序3)

sessionB：

update test_user_info set age =20 where id = 12；(執行時序2)

update test_user_info set age =20 where id= 1；(執行時序4)

這種場景容易產生死鎖，我們在同一個事務中，要儘可能做到同表的某些資料一次性更新；

按照id進行排序，然後按序處理；

場景2：假設一個表有以下資料：id主鍵，name索引，age索引

CREATE TABLE `test_user_info` (
 `id` bigint(20) NOT NULL COMMENT '主鍵ID',
 `name` varchar(255) DEFAULT NULL COMMENT '使用者姓名',
 `age` int(11) DEFAULT NULL COMMENT '年齡',
 `salary` decimal(15,2) DEFAULT '100.00' COMMENT '獎金',
 PRIMARY KEY (`id`),
 KEY `index_name` (`name`),
 KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='使用者資訊表';

insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('1','zhangsan','15','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('12','lisi','12','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('35','wangwu','49','200.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('47','zhangsan','13','100.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('59','qianliu','43','200.00');
insert into `test_user_info` (`id`, `name`, `age`, `salary`) values('68','zhaojiu','25','200.00');

sessionA執行以下sql未提交：update test_user_info set salary=salary+100 where name = 'zhangsan';

主表記錄鎖加鎖順序按id排序如下：1,47

sessionB執行以下sql未提交：update test_user_info set salary=salary+120 where age > 12 and age < 25;

主表記錄鎖加鎖順序按id排序如下：47,1

主表記錄鎖順序不一致，產生死鎖。但這個死鎖還有一個極端條件，加鎖過程中，sessionA先對id=1的資料行加鎖，

sessionB再對id=47的資料加鎖；此時不管是sessionA想對id=47的行加鎖，或是sessionB想對id=1的資料行加鎖，

均會阻塞，併產生死鎖。

死鎖的關鍵在於：兩個(或以上)的Session加鎖的順序不一致，mysql是逐行加鎖的。

解決方案：根據索引回查主鍵id並排序，根據主鍵id更新。

查詢最近一次死鎖紀錄檔：SHOW ENGINE INNODB STATUS;

死鎖紀錄檔如下：

0: len 8; hex 8000000000000001; asc ;;------事務更新的主鍵索引-----第一個位元組是 "80"，轉換為十進位制為 128。後面的 7 個位元組 "000000000000002f" 代表一個 64 位的整數，轉為十進位制為 1
1: len 6; hex 000000000879; asc y;;-----事務id2169
2: len 7; hex 020000016b0151; asc k Q;;---事務回滾指標
3: len 8; hex 7a68616e6773616e; asc zhangsan;;----代表資料值-----十六進位制轉化後為：zhangsan
4: len 4; hex 8000000f; asc ;;------代表資料值------六進位制轉化後為：15
5: len 7; hex 8000000000d200; asc ;;------代表資料值salary

記錄鎖（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
間隙鎖（LOCK_GAP）: lock_mode X locks gap before rec
Next-key鎖（LOCK_ORNIDARY）: lock_mode X / lock mode S
插入意向鎖（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention


=====================================
2023-11-15 13:45:36 0x70bc INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 247 srv_active, 0 srv_shutdown, 95073 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 928
OS WAIT ARRAY INFO: signal count 816
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-11-15 13:40:30 0x14de8
*** (1) TRANSACTION:
TRANSACTION 2171, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 61, OS thread handle 77680, query id 6470 localhost 127.0.0.1 root updating
UPDATE test_user_info  SET salary = salary + 1  
 
 WHERE (age > 12 AND age < 25)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `my_test`.`test_user_info` trx id 2171 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 800000000000002f; asc        /;;-----事務更新的主鍵索引-----第一個位元組是 "80"，轉換為十進位制為 128。後面的 7 個位元組 "000000000000002f" 代表一個 64 位的整數，轉為十進位制為 47
 1: len 6; hex 00000000087b; asc      {;;
 2: len 7; hex 01000001720151; asc     r Q;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 4; hex 8000000d; asc     ;;
 5: len 7; hex 8000000000d200; asc        ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `my_test`.`test_user_info` trx id 2171 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;-----事務更新的主鍵索引------第一個位元組是 "80"，轉換為十進位制為 128。後面的 7 個位元組 "000000000000002f" 代表一個 64 位的整數，轉為十進位制為 1
 1: len 6; hex 000000000879; asc      y;;
 2: len 7; hex 020000016b0151; asc     k Q;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 4; hex 8000000f; asc     ;;
 5: len 7; hex 8000000000d200; asc        ;;


*** (2) TRANSACTION:
TRANSACTION 2169, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1
MySQL thread id 60, OS thread handle 12696, query id 6468 localhost 127.0.0.1 root updating
UPDATE test_user_info  SET salary = salary + 1  
 
 WHERE (name = 'zhangsan')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `my_test`.`test_user_info` trx id 2169 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;------事務更新的主鍵索引-----第一個位元組是 "80"，轉換為十進位制為 128。後面的 7 個位元組 "000000000000002f" 代表一個 64 位的整數，轉為十進位制為 1
 1: len 6; hex 000000000879; asc      y;;-----事務id2169
 2: len 7; hex 020000016b0151; asc     k Q;;---事務回滾指標
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;----代表資料值-----十六進位制轉化後為：zhangsan
 4: len 4; hex 8000000f; asc     ;;------代表資料值------六進位制轉化後為：15
 5: len 7; hex 8000000000d200; asc        ;;------代表資料值salary


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 80 index PRIMARY of table `my_test`.`test_user_info` trx id 2169 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 800000000000002f; asc        /;;-------事務更新的主鍵索引----第一個位元組是 "80"，轉換為十進位制為 128。後面的 7 個位元組 "000000000000002f" 代表一個 64 位的整數，轉為十進位制為 47
 1: len 6; hex 00000000087b; asc      {;;
 2: len 7; hex 01000001720151; asc     r Q;;
 3: len 8; hex 7a68616e6773616e; asc zhangsan;;
 4: len 4; hex 8000000d; asc     ;;
 5: len 7; hex 8000000000d200; asc        ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 2362
Purge done for trx's n:o < 2362 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283442874948104, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283442874947328, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283442874946552, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 283442874945776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio ((null))
I/O thread 1 state: wait Windows aio (insert buffer thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
955 OS file reads, 5540 OS file writes, 3533 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          20808942
Log buffer assigned up to    20808942
Log buffer completed up to   20808942
Log written up to            20808942
Log flushed up to            20808942
Added dirty pages up to      20808942
Pages flushed up to          20808942
Last checkpoint at           20808942
Log minimum file id is       6
Log maximum file id is       6
1753 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 622315
Buffer pool size   8191
Free buffers       6881
Database pages     1293
Old database pages 457
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 816, not young 4439
0.00 youngs/s, 0.00 non-youngs/s
Pages read 933, created 367, written 2566
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1293, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=81884, Main thread ID=79028 , state=sleeping
Number of rows inserted 50, updated 535, deleted 22, read 1335
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 470, updated 362, deleted 168, read 21717
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

5.3後設資料'鎖表'場景(DDL)

MySQL 內部對於 DDL 的 ALGORITHM 有兩種選擇：INPLACE 和 COPY（8.0 新增了 INSTANT，但是使用範圍較小）。

在mysql5.6.71之前，都是使用COPY的方式。

COPY的原理

1、鎖表，期間DML不可並行執行
2、生成臨時表以及臨時表檔案（.frm.ibd）
3、拷貝原表資料到臨時表
4、重新命名臨時表以及檔案
5、刪除原表以及檔案
6、提交事務、釋放鎖

但是在之後的版本，官方開始支援INPLACE的 ALTER TABLE 操作來避免資料拷貝，同時支援了線上上 DDL 的過程中不阻塞 DML 操作，真正意義上的實現了 Online DDL。然而並不是所有的 DDL 操作都支援線上操作。

語句如下：

ALTER TABLE table_name ADD INDEX index_name (column_name), ALGORITHM=INPLACE, LOCK=NONE;

我們常說的 Online DDL，其實是從 DML 操作的角度描述的，如果 DDL 操作不阻塞 DML 操作，那麼這個 DDL 就是 Online 的。當前非 Online 的 DDL 其實已經比較少了，主要有：

新增全文索引
新增空間索引
刪除主鍵
修改列資料型別
指定表字元集
修改表字元集

在執行 DDL 操作時，MySQL 內部對於 ALGORITHM 的選擇策略是：如果使用者顯式指定了 ALGORITHM，那麼使用使用者指定的選項；如果使用者未指定，那麼如果該操作支援 INPLACE 則優先選擇 INPLACE，否則選擇 COPY；

COPY 演演算法執行的 DDL 肯定不是 Online 的；
INPLACE 演演算法執行的 DDL 不一定是 Online 的；

INPLACE 原理

prepare階段：嘗試獲取MDL排他鎖，禁止其他執行緒讀寫；
ddl執行階段：降級成MDL共用鎖，允許其他執行緒讀取；
commit階段：升級成MDL排他鎖，禁止其他執行緒讀寫；
finish階段：釋放MDL鎖；

下面演示兩種常見的操作，新增索引和新增欄位

5.3.1新增索引

下面我們演示新增索引(支援online-ddl)的過程：

SessionA執行:

select * from test_user_info where name = 'lishi'

不提交；

SessionB執行:

alter table test_user_info
add index name_age_index (name, age)

不提交；

SessionC執行:

select * from test_user_info where name = 'lishi'

不提交；

我們發現，SessionC的查詢也阻塞了。

這時，我們提交SessionA, 我們發現SessionC可以自動執行了，但是SessionB的新增索引還是阻塞。

現象1: SessionA的查詢未提交，阻塞SessionB的執行；SessionB被阻塞後，阻塞SessionC的查詢；

分析: SessionA的查詢未提交，表加後設資料讀鎖；SessionB執行，獲取到表的後設資料寫鎖，因為後設資料讀寫鎖互斥，SessionB被阻塞；SessionC執行，獲取表的後設資料讀鎖，因為後設資料讀寫鎖互斥，SessionC被阻塞。

問題: 原本可以並行執行的操作被阻塞了。

現象2: SessionA提交事務，此時SessionC優先執行，SessionB繼續被阻塞；SessionC提交事務後，SessionB才可以執行。

結論：當DDL新增索引因為其他DML操作阻塞時，需要等待其他所有DML操作都提交後才能執行，即使在阻塞後有其他DML操作進來，其他DML操作也優先於DDL新增索引執行。

這會造成什麼問題？

假如一個表的並行存取很高，DDL操作可能會超時。如果資料庫有慢查詢，比如如下查詢，查詢返回資料需要30秒：

SELECT SLEEP(30)  FROM my_test.test_user_info WHERE NAME = 'lisi'

因為DDL執行時，會阻塞其之後執行的DML操作，可能導致之後的DML操作積壓，也會造成單個DML操作返回時間過長。可能造成業務系統資料庫連線被打滿，介面超時，即使不超時也會因為返回時間過長影響使用者體驗。這種現象看來，就彷彿表被鎖住了一樣，雖然不是真正意義上的鎖表。

所以我們在執行ALTER操作的時候，儘量在業務不繁忙的階段執行。

5.3.2新增欄位

SessionA執行：

SELECT *  FROM my_test.test_user_info WHERE NAME = 'lisi'

SessionB執行：

alter table test_user_info
add column  hometown varchar(255) default null

SessionC執行：

update test_user_info set salary = salary+1 where age = 12

SessionD執行：

delete from test_user_info where id = 100

均不自動提交事務

現象1: SessionA的查詢未提交，阻塞SessionB的執行；SessionB被阻塞後，阻塞SessionC的執行；SessionB被阻塞後，阻塞SessionD的執行；

分析: SessionA的查詢未提交，表加後設資料讀鎖；SessionB執行，獲取到表的後設資料寫鎖，因為後設資料讀寫鎖互斥，SessionB被阻塞；SessionC執行，獲取表的後設資料讀鎖，因為後設資料讀寫鎖互斥，SessionC被阻塞；SessionD執行，獲取表的後設資料讀鎖，因為後設資料讀寫鎖互斥，SessionD被阻塞。

現象2: SessionA提交事務，此時SessionB，SessionC，SessionD全部自動執行。

結論：當DDL新增欄位因為其他DML操作阻塞時，需要等待其他所有DML操作都提交後才能執行，當阻塞DDL新增欄位的DML執行後，DDL新增欄位和後續的DML均可同步執行。這點和新增索引完全不同。

5.4.pt-online-schema-change

pt-online-schema-change 是一個用於線上更改 MySQL 表結構的工具，它是 Percona Toolkit 的一部分。它的原理是通過線上複製表資料，同時在新表上應用修改，從而避免了直接修改原始表結構導致的鎖表和效能下降問題。

pt-online-schema-change 的原理：

建立一張新表，表結構與舊錶相同；
Alter 新表；
在原表上建立 insert、update、delete 三種型別的觸發器；
將舊錶的資料拷貝到新表中，同時通過觸發器將舊錶中的操作對映到新表；
如果原表有外來鍵約束，處理外來鍵；
原表重新命名為 old 表，new 表重新命名為原表，整個過程為原子操作；
刪除 old 表（預設）；

pt-online-schema-change 在以下場景中特別有用：

修改大型表的結構：對於包含數百萬甚至數十億行的大型表，直接修改表結構可能導致長時間的鎖表和效能下降。pt-online-schema-change 通過線上方式避免了這些問題。
避免業務中斷：在需要修改生產環境資料庫表結構時，pt-online-schema-change 可以在不影響業務正常執行的情況下進行表結構更改。
相容各種儲存引擎：pt-online-schema-change 支援各種 MySQL 儲存引擎，如 InnoDB 和 MyISAM。

7.建議

mysql在非唯一索引上的操作，會加間隙鎖或臨鍵鎖；根據資料的分散情況，可能產生大量的鎖，可能導致大量的行或間隙被鎖定。阻塞其他事務的insert，update，delete等操作，有產生死鎖的風險，對資料庫效能影響較大。對mysql的insert，update，delete或select for update等操作，建議都在唯一索引上進行。實際業務中，我們要根據二級索引更新資料前，可以先將資料的主鍵查詢出來，再根據主鍵批次操作，這樣雖然增加了查詢操作，但是隻會在唯一索引上加記錄鎖，鎖定行數較少，對其他事務的操作影響較小，提高了資料庫的寫的效能。

對於表的DDL操作，建議在業務不繁忙的時間段執行，或依賴第三方工具，如pt-online-schema-change來處理。

8.參考檔案

MySql8.0官方檔案：

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

MySQL8.0優化 - 鎖 - 從資料操作的粒度劃分：

https://blog.csdn.net/ChinaYangJu/article/details/127939056

詳細剖析MySQL臨鍵鎖：

https://blog.csdn.net/Bb15070047748/article/details/131766686

mysql隔離級別RR下的行鎖、臨鍵鎖、間隙鎖詳解及運用：

https://blog.csdn.net/qq_35572020/article/details/127629464

pt-online-schema-change

https://blog.csdn.net/weixin_44352521/article/details/108558026

超詳細的Mysql鎖 實戰分析，你想知道的都在這裡~

1.mysql回表查詢

2.mysql innodb事務隔離級別

3.mysql鎖分類

3.1按鎖粒度分類

3.1.1表級鎖分類

3.1.1.1表讀鎖（共用鎖-S鎖）及表寫鎖（排他鎖-X鎖）

3.1.1.2意向鎖（Intention lock）

3.1.1.3自增鎖（AUTO-INC鎖）

3.1.1.4後設資料鎖（meta data lock)

3.1.1.5表級鎖相容性

3.1.2行級鎖分類

3.1.2.1記錄鎖（Record Locks）

3.1.2.2間隙鎖（Gap Locks）

3.1.2.3臨鍵鎖（Next-Key Locks）

3.1.2.4插入意向鎖（Insert Intention Locks）

3.1.2.5空間索引謂詞鎖(Predicate Locks for Spatial Indexes)

4.mysql加鎖分析

資料說明

場景一:唯一索引等值查詢，查詢資料不存在

場景二:唯一索引等值查詢，查詢資料存在

場景三：唯一索引>=範圍查詢，等值查詢資料存在

場景四：唯一索引>=範圍查詢，等值查詢資料不存在，這種就類似於>範圍查詢

場景五：唯一索引<=範圍查詢，等值查詢資料存在

場景六：唯一索引<=範圍查詢，等值查詢資料不存在，這種就類似於<範圍查詢

場景七：唯一索引>= and <=範圍查詢

場景八：非唯一索引等值查詢，查詢資料不存在

場景九：非唯一索引等值查詢，查詢資料存在

場景十：非唯一索引>=範圍查詢，等值查詢資料存在

場景十一：非唯一索引>=範圍查詢，等值查詢資料不存在，這種就類似於>範圍查詢

場景十二：非唯一索引<=範圍查詢，等值查詢資料存在

場景十三：非唯一索引<=範圍查詢，等值查詢資料不存在，這種就類似於<範圍查詢

場景十四：非唯一索引>= and <=範圍查詢

場景十五：非索引列查詢

6.InnoDB死鎖

5.1表級死鎖產生場景

5.2行級鎖死鎖產生場景

5.3後設資料'鎖表'場景(DDL)

5.3.1新增索引

5.3.2新增欄位

5.4.pt-online-schema-change

7.建議

8.參考檔案

超詳細的Mysql鎖實戰分析，你想知道的都在這裡~