MySQL查看事務(wù)與鎖的操作示例小結(jié)

更新時(shí)間：2025年12月29日 11:50:58 作者：ZePingPingZe

本文介紹了MySQL中事務(wù)和鎖的基本概念以及不同隔離級(jí)別下的鎖類型,并通過(guò)實(shí)際測(cè)試驗(yàn)證了這些鎖的使用情況,本文給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧

1. MySQL如何查看事務(wù)與鎖??

-- 1. 查看當(dāng)前運(yùn)行的事務(wù)
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 2. 查看鎖信息
-- 2.1 MySQL 5.7
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 2.2 MySQL 8.0+
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

從【MySQL-InnoDB鎖、事務(wù)與MVCC】文章可以看出來(lái)，鎖有下面幾種分類：

按鎖模式分為：共享鎖（S鎖）、排它鎖（X鎖）。
按鎖粒度分為：表鎖、行鎖。
表鎖分為：表級(jí)S鎖、表級(jí)X鎖、表級(jí)IS鎖（意向共享鎖）、表級(jí)IX鎖（意向排它鎖）。
- IS鎖和IX鎖的目的：只是為了后續(xù) 在加表級(jí)別的S鎖和X鎖時(shí) 判斷表中是否有已經(jīng)被加鎖的記錄，以避免用遍歷的方式來(lái)查看表中有沒(méi)有上鎖的記錄。
行鎖分為：記錄鎖（Record Lock）、間隙鎖（Gap Lock）、臨鍵鎖（Next-key Lock，Gap鎖 + 記錄鎖）、隱式鎖。

對(duì)于MySQL的InnoDB存儲(chǔ)引擎來(lái)說(shuō)，不同事務(wù)隔離級(jí)別下使用的鎖不同：

READ COMMITTED（RC，讀已提交）：只有記錄鎖（Record Lock），沒(méi)有間隙鎖（Gap Lock）或臨鍵鎖（Next-Key Lock）。
REPEATABLE READ（RR，可重復(fù)讀）：默認(rèn)使用 Next-Key Lock（記錄鎖+間隙鎖），注意這里說(shuō)的是【默認(rèn)】，并不意味著RR級(jí)別下使用行級(jí)鎖時(shí)全都用的是 Next-Key。

2. 不同事務(wù)隔離級(jí)別下的鎖類型驗(yàn)證

以下測(cè)試基于MySQL_8.0.30

用來(lái)驗(yàn)證的cus_info表結(jié)構(gòu)：

-- test.cus_info definition
CREATE TABLE `cus_info` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `cus_id` varchar(21) NOT NULL COMMENT '客戶編號(hào)',
  `phone_num` varchar(11) NOT NULL COMMENT '手機(jī)號(hào)',
  `nick_name` varchar(20) NOT NULL COMMENT '昵稱',
  `sex` varchar(1) NOT NULL COMMENT '性別,0-女,1-男',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ci` (`cus_id`) USING BTREE, -- 二級(jí)索引：唯一索引
  UNIQUE KEY `uk_pn` (`phone_num`) USING BTREE -- 唯一索引
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='客戶信息表';
-- 插入語(yǔ)句
INSERT INTO cus_info (cus_id, phone_num, nick_name, sex) VALUES('200606011708203560002', '10000000002', '蓋世無(wú)雙', '0');
-- 更新語(yǔ)句
UPDATE cus_info SET cus_id='200606011708203560002', phone_num='10000000002', nick_name='蓋世無(wú)雙', sex='0' WHERE id=2;
-- 查詢語(yǔ)句
SELECT id, cus_id, phone_num, nick_name, sex FROM cus_info WHERE id=2;

表數(shù)據(jù)如下：id（1, 2, 3, 5, 7, 8）

2.1 RC級(jí)別下的驗(yàn)證

先查看一下事務(wù)隔離級(jí)別：

-- 切換到test庫(kù)
mysql> use test;
-- 查詢事務(wù)隔離級(jí)別
mysql> show variables like '%isolation%';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)

2.1.1 共享鎖（S鎖）

普通的select語(yǔ)句，InnoDB存儲(chǔ)引擎不會(huì)加任何鎖。

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 普通的select語(yǔ)句，InnoDB存儲(chǔ)引擎不會(huì)加任何鎖?？梢酝ㄟ^(guò) performance_schema.data_locks表查看。
mysql> select * from cus_info where id > 2 and id < 7;
-- 查詢結(jié)果展示省略
-- 加 共享鎖（S鎖）
mysql> select * from cus_info where id > 2 and id < 7 for share;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  3 | 200606011708203560003 | 10000000003 | 默默      | 0   |
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)

查詢鎖信息：performance_schema.data_locks

select `engine`, object_schema, object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;

可以看到：

① 給表cus_info加了表級(jí)別的IS鎖（意向共享鎖）。

② 給id（主鍵）為3和5的兩條記錄加了 Record Lock（記錄鎖）、S鎖（共享鎖），并且不是Gap鎖。

-- 測(cè)試完后提交或回滾事務(wù)
mysql> rollback; -- 或者 commit
Query OK, 0 rows affected (0.00 sec)

2.1.2 排它鎖（X鎖??）

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

開(kāi)啟事務(wù)后，我們先來(lái)看一下 information_schema庫(kù)的INNODB_TRX視圖信息，可以看到?jīng)]有任何數(shù)據(jù)，由此也可以看出來(lái) start transaction; 開(kāi)啟一個(gè)事務(wù)并沒(méi)有讓 MySQL真正開(kāi)始分配事務(wù)編號(hào)（trx_id）。

mysql> select * from information_schema.INNODB_TRX;
Empty set (0.00 sec)

再執(zhí)行一個(gè) 普通的select語(yǔ)句，再看看 INNODB_TRX 視圖信息：

trx_id（事務(wù)編號(hào)）、trx_state（事務(wù)狀態(tài)，running運(yùn)行中）、trx_started（事務(wù)開(kāi)始時(shí)間）、trx_isolation_level（事務(wù)隔離級(jí)別）。注意trx_id（事務(wù)編號(hào)）的值，等下看看會(huì)發(fā)生什么變化。此時(shí)還沒(méi)有加任何鎖，所以 performance_schema.data_locks表中沒(méi)有任何信息。

mysql> select * from cus_info where id > 2 and id < 7;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  3 | 200606011708203560003 | 10000000003 | 默默      | 0   |
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)
-- 這里只查看事務(wù)的部分信息（INNODB_TRX視圖列太多了，只展示現(xiàn)在需要的）
mysql> select trx_id, trx_state, trx_started, trx_isolation_level from information_schema.INNODB_TRX;
+-----------------+---------------------+-----------+---------------------+---------------------+
| trx_id          | trx_mysql_thread_id | trx_state | trx_started         | trx_isolation_level |
+-----------------+---------------------+-----------+---------------------+---------------------+
| 284035063683520 |                   8 | RUNNING   | 2025-12-28 17:28:27 | READ COMMITTED      |
+-----------------+---------------------+-----------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.data_locks;
Empty set (0.00 sec)

1. Update操作

mysql> update cus_info set sex=1 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 查看 information_schema.INNODB_TRX
mysql> select trx_id, trx_mysql_thread_id, trx_state,
    -> trx_started, trx_isolation_level from information_schema.INNODB_TRX;
+--------+---------------------+-----------+---------------------+---------------------+
| trx_id | trx_mysql_thread_id | trx_state | trx_started         | trx_isolation_level |
+--------+---------------------+-----------+---------------------+---------------------+
|  20301 |                   8 | RUNNING   | 2025-12-28 17:28:27 | READ COMMITTED      |
+--------+---------------------+-----------+---------------------+---------------------+
1 row in set (0.00 sec)

查看 information_schema.INNODB_TRX，發(fā)現(xiàn) trx_id 發(fā)生了變化。

查看鎖信息：performance_schema.data_locks，可以看到：

① 給表cus_info加了表級(jí)別的IX鎖（意向排他鎖）。

② 給id（主鍵）為5的記錄加了 Record Lock（記錄鎖）、X鎖（排他鎖），并且不是Gap鎖。

2. Delete操作

接著上面的繼續(xù)執(zhí)行

mysql> delete from cus_info where id=3;
Query OK, 1 row affected (0.00 sec)

查看鎖信息：performance_schema.data_locks

為了不影響下面的 select ... for update; 這里我們先回滾事務(wù)。

-- 為了不影響下面的 select ... for update; 這里我們先回滾事務(wù)
mysql> rollback; -- 或者 commit
Query OK, 0 rows affected (0.00 sec)

3.隱式鎖和RC級(jí)別沒(méi)有Gap鎖的驗(yàn)證（Select...for update與Insert）

1)、開(kāi)啟事務(wù)1，并執(zhí)行 select...for update 操作：

-- 開(kāi)啟事務(wù)1（spring框架中的事務(wù)管理器 DataSourceTransactionManager 就是這么開(kāi)啟一個(gè)事務(wù)的）
mysql> set autocommit = OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from cus_info where id > 3 and id < 8 for update;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)

查看鎖信息如下：只對(duì)（3, 8）這個(gè)區(qū)間里面 已存在的數(shù)據(jù)（id = 5 和 id = 7）進(jìn)行加鎖操作，不會(huì)對(duì)（3, 8）這個(gè)區(qū)間范圍本身加鎖。注意看這里加的不是Gap鎖，而是行級(jí)排它鎖。

這次，我們多查看一個(gè)列（engine_transaction_id，事務(wù)編號(hào)），當(dāng)前事務(wù)1的事務(wù)編號(hào)為20307

2)、開(kāi)啟事務(wù)2，執(zhí)行 insert 操作，可以看到 insert 成功了，說(shuō)明事務(wù)1確實(shí)沒(méi)有對(duì)（3, 8）這個(gè)區(qū)間范圍本身加鎖。由此可以說(shuō)明，RC事務(wù)隔離級(jí)別下對(duì)于表中的記錄沒(méi)有 Gap鎖（間隙鎖），只有記錄鎖（Record Lock）。

-- 1. 開(kāi)啟事務(wù)2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. 在insert前先select驗(yàn)證一下
mysql> select * from cus_info where id > 3 and id < 8;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)
-- 3. insert id = 6 的記錄成功了，說(shuō)明事務(wù)1確實(shí)沒(méi)有對(duì)（3, 8）這個(gè)區(qū)間范圍本身加鎖。
mysql> INSERT INTO cus_info (id, cus_id, phone_num, nick_name, sex) VALUES(6, '200606011708203560006', '10000000006', '阿文', '0');
Query OK, 1 row affected (0.00 sec)
-- 4. 再次select驗(yàn)證insert是否成功
mysql> select * from cus_info where id > 3 and id < 8;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  6 | 200606011708203560006 | 10000000006 | 阿文      | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
3 rows in set (0.00 sec)

我們?cè)俅蝸?lái)看 performance_schema.data_locks 鎖信息，可以看到事務(wù)2（事務(wù)編號(hào)為20313）只有【1條】表級(jí)別的 IX鎖（意向排它鎖），并沒(méi)有對(duì) id = 6 的記錄加任何鎖（實(shí)際上，在MySQL的內(nèi)存中，也就是在 Buffer Pool（緩沖池）的數(shù)據(jù)頁(yè)（頁(yè)是MySQL中內(nèi)存和磁盤(pán)進(jìn)行數(shù)據(jù)交互的基本單位，頁(yè)分為數(shù)據(jù)頁(yè)、undo log頁(yè)、redo log頁(yè)[block]等等）上有一條 id=6的記錄，并且該記錄有一個(gè)【隱藏列 trx_id（事務(wù)編號(hào)）】，取值為 20313）。

3)、再回到事務(wù)1，執(zhí)行普通select 與 update操作（這里我們就更新事務(wù)2插入的 id=6 的新紀(jì)錄）。

① 先執(zhí)行普通的 select語(yǔ)句，可以得出結(jié)論：事務(wù)1確實(shí)看不到事務(wù)2剛剛插入的id=6的新紀(jì)錄（因?yàn)槭聞?wù)還沒(méi)有commit，RC級(jí)別下，一個(gè)事務(wù)不會(huì)讀取到另一個(gè)事物未提交的數(shù)據(jù)）；

② 看是看不到（因?yàn)槭聞?wù)隔離級(jí)別是 RC，讀已提交），但是更新操作卻會(huì)阻塞，直到等待超時(shí)。

-- 1. 先執(zhí)行普通的 select語(yǔ)句，可以得出結(jié)論：事務(wù)1確實(shí)看不到事務(wù)2剛剛插入的id=6的新紀(jì)錄（因?yàn)槭聞?wù)還沒(méi)有commit）
mysql> select * from cus_info where id > 3 and id < 8;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)
-- 2. 看是看不到（因?yàn)槭聞?wù)隔離級(jí)別是 RC，讀已提交），但是更新操作卻會(huì)阻塞，直到等待超時(shí)。
mysql> update cus_info set sex=1 where id=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我們 在update語(yǔ)句報(bào)錯(cuò)（報(bào)出 Lock wait timeout）之前再來(lái)查看鎖信息，可以看到：

① 事務(wù)2給 id = 6 的記錄加了行級(jí)排它鎖（X鎖，不是Gap鎖）；

② 事務(wù)1的update語(yǔ)句因?yàn)橐@取事務(wù)2持有的鎖，所以進(jìn)入 waiting（等待狀態(tài)）。

之所以會(huì)這樣，是因?yàn)?insert語(yǔ)句一開(kāi)始給記錄加的是【隱式X鎖】，當(dāng)update執(zhí)行的時(shí)候，它會(huì)去表的主鍵索引對(duì)應(yīng)的 B+樹(shù) 去搜索，發(fā)現(xiàn) id=6 的記錄的隱藏列trx_id=20313（事務(wù)2的事務(wù)編號(hào)），并且事務(wù)編號(hào)為20313的事務(wù)處于活躍狀態(tài)（即，事務(wù)沒(méi)有提交，information_schema.INNODB_TRX表中 trx_id=20313 的 trx_state（事務(wù)狀態(tài)）為 running），這個(gè)時(shí)候就會(huì)給 performance_schema.data_locks 添加兩條記錄（即①、②）。

處于等待狀態(tài)的鎖：要么在等待超時(shí)后死亡；要么在超時(shí)前事務(wù)2提交成功后獲取到鎖。

一個(gè)事務(wù)對(duì)新插入的記錄可以不顯式的加鎖，但是由于【事務(wù)id】的存在，相當(dāng)于加了一個(gè)【隱式鎖】。其他事務(wù)對(duì)這條記錄加S鎖或者X鎖時(shí)，由于隱式鎖的存在，會(huì)先幫助當(dāng)前事務(wù)生成一把鎖，然后自己再生成一把鎖并進(jìn)入等待狀態(tài)。

2.2 RR級(jí)別下的驗(yàn)證

修改事務(wù)隔離級(jí)別：

-- 1. 設(shè)置事務(wù)隔離級(jí)別為：可重復(fù)讀
mysql> set transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)
-- 2. 查看設(shè)置是否成功
mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

表數(shù)據(jù)如下：id（1, 2, 3, 5, 7, 8）

2.2.1 共享鎖（S鎖）

-- 1. 開(kāi)啟事務(wù)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. 共享鎖，select...for share
mysql> select * from cus_info where id > 2 and id < 7 for share;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  3 | 200606011708203560003 | 10000000003 | 默默      | 0   |
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)

查看鎖信息：performance_schema.data_locks

可以看到：

① 給表cus_info加了表級(jí)別的IS鎖（意向共享鎖）。

② 加 Next-key Lock（臨鍵鎖，記錄鎖 + 間隙鎖），給區(qū)間（2, 7）和記錄7 加鎖。

-- 測(cè)試完后提交或回滾事務(wù)
mysql> rollback; -- 或者 commit
Query OK, 0 rows affected (0.00 sec)

2.2.2 排它鎖（X鎖??）

1. Update操作

-- 1. 開(kāi)啟事務(wù)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. 更新
mysql> update cus_info set sex=1 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看鎖信息：performance_schema.data_locks。

注意看，這里加的行級(jí)鎖為：X鎖（排它鎖）、not gap（不是gap鎖）。唯一索引的等值查詢（包括主鍵索引、唯一二級(jí)索引），Next-Key鎖會(huì)退化為記錄鎖。

-- 3. 根據(jù) phone_num（唯一索引）更新
mysql> update cus_info set sex=1 where phone_num='10000000005';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

再次查看鎖信息：performance_schema.data_locks。注意看 index_name：uk_pn、primary

-- 4. 更新區(qū)間 (3, 8)
mysql> update cus_info set sex=0 where id>3 and id<8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 2  Changed: 1  Warnings: 0

再次查看鎖信息：performance_schema.data_locks。這里又是對(duì) (3, 8] 這個(gè)區(qū)間加 Next-Key Lock（臨鍵鎖，記錄鎖 + 間隙鎖）。

2. Delete操作

mysql> delete from cus_info where id=3;
Query OK, 1 row affected (0.00 sec)

查看鎖信息：performance_schema.data_locks

注意看，這里加的行級(jí)鎖為：X鎖（排它鎖）、not gap（不是gap鎖）。

-- 為了不影響下面的 select ... for update; 這里我們先回滾事務(wù)
mysql> rollback; -- 或者 commit
Query OK, 0 rows affected (0.00 sec)

3. RR級(jí)別Next-Key鎖驗(yàn)證

1)、開(kāi)啟事務(wù)1，并執(zhí)行 select...for update 操作：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from cus_info where id > 3 and id < 8 for update;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)

查看鎖信息：performance_schema.data_locks。給區(qū)間（3, 8）和記錄8 加鎖，即給區(qū)間 (3, 8] 加了鎖。事務(wù)1的編號(hào)為20320

2)、開(kāi)啟事務(wù)2，執(zhí)行 insert 操作，可以看到 insert 失敗了，說(shuō)明事務(wù)1確實(shí)對(duì)（3, 8] 這個(gè)區(qū)間范圍本身加了 gap鎖+記錄鎖。

-- 1. 開(kāi)啟事務(wù)2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 2. insert前先執(zhí)行 普通select查詢看看
mysql> select * from cus_info where id > 3 and id < 8;
+----+-----------------------+-------------+-----------+-----+
| id | cus_id                | phone_num   | nick_name | sex |
+----+-----------------------+-------------+-----------+-----+
|  5 | 200606011708203560005 | 10000000005 | 王        | 0   |
|  7 | 200606011708203560007 | 10000000007 | 趙        | 1   |
+----+-----------------------+-------------+-----------+-----+
2 rows in set (0.00 sec)
-- 3. insert id=6的記錄 失敗，因?yàn)槭聞?wù)1給 (3, 8] 區(qū)間加了 gap鎖+記錄鎖。
mysql> INSERT INTO cus_info (id, cus_id, phone_num, nick_name, sex) VALUES(6, '200606011708203560006', '10000000006', '阿文', '0');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

我們 在insert語(yǔ)句報(bào)錯(cuò)（報(bào)出 Lock wait timeout）之前再來(lái)查看鎖信息，可以看到：事務(wù)2（事務(wù)編號(hào)為20321）生成的行鎖為：X鎖（排它鎖）、Gap（間隙鎖）、insert intention（插入意向鎖），并處于 waiting 狀態(tài)。