文章目录
一、前言
最近在读《MySQL 是怎样运行的》、《MySQL技术内幕 InnoDB存储引擎 》，后续会随机将书中部分内容记录下来作为学习笔记，部分内容经过个人删改，因此可能存在错误，如想详细了解相关内容强烈推荐阅读相关书籍。
系列文章内容目录：
- 【MySQL00】【 杂七杂八】
- 【MySQL01】【 Explain 命令详解】
- 【MySQL02】【 InnoDB 记录存储结构】
- 【MySQL03】【 Buffer Pool】
- 【MySQL04】【 redo 日志】
- 【MySQL05】【 undo 日志】
- 【MySQL06】【MVCC】
- 【MySQL07】【锁】
- 【MySQL08】【死锁】
二、查看事务加锁情况
1. 使用 information_schema 数据库中表获取锁信息
1.1 INNODB_TRX
INNODB_TRX 存储了 InnoDB 当前正在执行的事务信息，包括事务id（如果没有分配事务id，则显示当前事务对应的内存结构的指针）、事务状态（比如事务正在运行还是在等待获取某个锁、事务正在执行的语句、事务是何时开启的）等。如下：
- 开启一个事务 T1
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE number = 8 FOR UPDATE; +--------+-------+---------+ | number | name | country | +--------+-------+---------+ | 8 | c曹操 | 魏 | +--------+-------+---------+ 1 row in set (0.00 sec) - 在另一个会话中查询 INNODB_TRX 表：
mysql> SELECT * FROM information_schema.INNODB_TRX\G *************************** 1. row *************************** trx_id: 195617 trx_state: RUNNING trx_started: 2024-06-27 09:51:28 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 2 trx_mysql_thread_id: 10 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_structs: 2 trx_lock_memory_bytes: 1136 trx_rows_locked: 1 trx_rows_modified: 0 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec)
从执行结果可以看到，当前系统中有一个事务id为 195617 的事务，它的状态为 “正在运行”（RUNNING），隔离级别为 REPEATABLE READ。这里主要关注下面几个属性：
- trx_tables_locked ：表示当前事务加了多少表级锁
- trx_rows_locked ：表示当前事务加了多少行级锁
- trx_lock_structs ：表示当前事务生成了多少内存中的锁结构。
1.2 INNODB_LOCKS
INNODB_LOCKS 记录了一些锁信息，主要包括：
- 如果一个事务想要获取锁但是未获取到，则记录该锁信息
- 如果一个事务获取到了某个所，但是这个锁阻塞了别的事务，则记录该锁信息。
innodb_locks表在8.0.13版本中由performance_schema.data_locks表所代替
-
INNODB_LOCKS 表只有在系统中发生某个事务获取不到锁而被阻塞时才会有记录，因此我们需要再执行一个事务 T2
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE number = 8 FOR UPDATE; +--------+-------+---------+ | number | name | country | +--------+-------+---------+ | 8 | c曹操 | 魏 | +--------+-------+---------+ 1 row in set (0.00 sec) -
查询 INNODB_LOCKS 表记录，如下
mysql> SELECT * FROM information_schema.INNODB_LOCKS; +-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+ | 195619:2432:3:8 | 195619 | X | RECORD | `demo`.`hero` | PRIMARY | 2432 | 3 | 8 | 8 | | 195617:2432:3:8 | 195617 | X | RECORD | `demo`.`hero` | PRIMARY | 2432 | 3 | 8 | 8 | +-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+ 2 rows in set, 1 warning (0.00 sec)
可以看到 trx_id 为 195617 和 195619两个事务存在记录，但是上述内容无法区分是到底谁获取到其他事务的需要的锁，以及谁因为没有获取到锁而阻塞。可以通过 INNODB_LOCK_WAITS 表获取到
1.3 INNODB_LOCK_WAITS
INNODB_LOCK_WAITS 表明每个阻塞的事务是因为获取不到哪个事务持有的锁而阻塞。继续上面的情况，执行如下语句:
innodb_lock_waits表在8.0.13版本中 由performance_schema.data_lock_waits 表代替。
mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 195619 | 195619:2432:3:8 | 195617 | 195617:2432:3:8 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)
其中：
- requesting_trx_id ：表示因为获取不到锁而被阻塞的事务的事务id；本例中表示事务T2 的id
- blocking_trx_id ：表示因为获取到别的事务需要的锁而导致其被阻塞的事务的事务id；本地中表示事务T1的id
需要注意的是：INNODB_LOCKS 和 INNODB_LOCK_WAITS 已经被标记过时，在MySQL 8.0 中就已经被移除。
2. 使用 SHOW ENGIN INNODB STATUS 获取锁信息
-
以下面SQL为例
mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero FORCE INDEX(idx_name) WHERE name > 'c曹操' AND name <= 'x荀彧' AND country != '吴' ORDER BY name DESC FOR UPDATE; +--------+-------+---------+ | number | name | country | +--------+-------+---------+ | 15 | x荀彧 | 魏 | | 1 | l刘备 | 蜀 | +--------+-------+---------+ 2 rows in set (0.00 sec)该SQL的加锁流程在 【MySQL07】【锁】有过分析，其加锁结构如下：
-
执行 SHOW ENGINE INNODB STATUS，如下：
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** ... 省略部分信息 ------------ TRANSACTIONS ------------ # 下一个待分配的事务id信息 Trx id counter 195622 # 一些关于 purge 信息 Purge done for trx's n:o < 195295 undo n:o < 0 state: running but idle # 每个回滚段中有一个 History 链表，这些链表的总长是 1933 History list length 1933 # 每个事务的具体信息 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283969552689208, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552688336, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552687464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552686592, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552685720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) # 事务id为 195621 的事务，处于活跃状态 139s ---TRANSACTION 195621, ACTIVE 139 sec # 该事务有4个锁结构， 9 个行锁 4 lock struct(s), heap size 1136, 9 row lock(s) # 执行该事务的线程在 MySQL 中的编号为 9，操作系统中的线程号是 19744, 本次查询的编号是 255,,客户端主机名为 localhost, 用户名四 root MySQL thread id 9, OS thread handle 19744, query id 255 localhost ::1 root cleaning up ... 忽略剩余内容 1 row in set (0.00 sec)上面输出的结构无法看出到底哪个事务对哪些记录加了哪些锁，因此可以将 innodb_status_output_locks 设置为 on 然后再输出日志，如下(这里只保存了 TRANSACTIONS 部分的日志，上述SQL 重新执行了，所以下面的事务ID 有了变化，本次事务id 195625)：
# 设置 innodb_status_output_locks = ON mysql> SET GLOBAL innodb_status_output_locks = ON; Query OK, 0 rows affected (0.00 sec) # 再次执行 SHOW ENGINE INNODB STATUS语句 mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** ... 省略部分信息 ------------ TRANSACTIONS ------------ Trx id counter 195625 Purge done for trxs n:o < 195623 undo n:o < 0 state: running but idle History list length 1934 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 283969552690080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552689208, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552688336, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552687464, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552686592, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 283969552685720, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 195624, ACTIVE 51 sec 4 lock struct(s), heap size 1136, 9 row lock(s) MySQL thread id 9, OS thread handle 19744, query id 264 localhost ::1 root cleaning up # 表明事务id = 195624 的事务对 hero 表加了 IX 锁（意向独占锁）。 TABLE LOCK table `demo`.`hero` trx id 195624 lock mode IX # 表示一个锁结构，这个锁结构的 Space ID 为 2432, Page No 为 4, n_bits 为 80, 对应的索引是 idx_name, 这个锁结构存放的锁的类型是 X 型 gap 锁 RECORD LOCKS space id 2432 page no 4 n bits 80 index idx_name of table `demo`.`hero` trx id 195624 lock_mode X locks gap before rec # 上面锁结构加锁的详细信息(就是 name = z诸葛亮的 二级索引记录) Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 1: len 4; hex 80000003; asc ;; # 表示一个锁结构，这个锁结构的 Space ID 为 2432, Page No 为 4, n_bits 为 80, 对应的索引是 idx_name, 这个锁结构存放的锁的类型是 X 型 next-key 锁 RECORD LOCKS space id 2432 page no 4 n bits 80 index idx_name of table `demo`.`hero` trx id 195624 lock_mode X # 上面锁结构加锁的详细信息(就是name = l刘备、c曹操、x荀彧、s孙权) Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 63e69bb9e6938d; asc c ;; 1: len 4; hex 80000008; asc ;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 6ce58898e5a487; asc l ;; 1: len 4; hex 80000001; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 73e5ad99e69d83; asc s ;; 1: len 4; hex 80000014; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 7; hex 78e88d80e5bda7; asc x ;; 1: len 4; hex 8000000f; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 7; hex 67e585b3e7bebd; asc g ;; 1: len 4; hex 8000001e; asc ;; # 表示一个锁结构，这个锁结构的 Space ID 为 2432, Page No 为 3, n_bits 为 80, 对应的索引是 PRIMARY, 也就是聚簇索引，这里存放的锁的类型是 X 型正经记录锁（lock_mode X locks rec but not gap） RECORD LOCKS space id 2432 page no 3 n bits 80 index PRIMARY of table `demo`.`hero` trx id 195624 lock_mode X locks rec but not gap # 上面锁结构加锁的详细信息(就是id = 1,15,20 的记录) Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000001761e; asc v ;; 2: len 7; hex 3c000002c10110; asc < ;; 3: len 7; hex 6ce58898e5a487; asc l ;; 4: len 3; hex e89c80; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 8000000f; asc ;; 1: len 6; hex 000000017634; asc v4;; 2: len 7; hex a90000011d0110; asc ;; 3: len 7; hex 78e88d80e5bda7; asc x ;; 4: len 3; hex e9ad8f; asc ;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 6; hex 000000017642; asc vB;; 2: len 7; hex 30000001e81fb2; asc 0 ;; 3: len 7; hex 73e5ad99e69d83; asc s ;; 4: len 3; hex e590b4; asc ;; ... 省略部分信息 1 row in set (0.00 sec)
三、死锁
| 执行顺序 | 事务 T1 | 事务T2 |
|---|---|---|
| 1 | BEGIN; | |
| 2 | BEGIN; | |
| 3 | SELECT * FROM hero WHERE number = 1 FOR UPDATE; | |
| 4 | SELECT * FROM hero WHERE number = 3 FOR UPDATE; | |
| 5 | SELECT * FROM hero WHERE number = 1 FOR UPDATE; （阻塞） | |
| 6 | SELECT * FROM hero WHERE number = 1 FOR UPDATE;（阻塞） |
上述流程比较清楚：
- 事务T1 获取到了 number = 1 的行锁，对该记录加了X 锁；
- 事务T2 获取到了 number = 3 的行锁，对该记录加了X 锁；
- 事务T1 获取 number = 3 的行锁，但是因为该行记录锁被 T2 持有因此陷入等待；
- 事务T2 获取 number = 1 的行锁，但是因为该行记录锁被 T1 持有因此陷入等待，陷入死锁。
InnoDB 有个死锁检测机制，当检测到死锁发生时，会选择一个较小的事务（在事务执行过程中插入、更新、删除记录条数更少的记录）进行回滚，并提示客户端信息：
Deadlock found when trying to get lock; try restarting transaction
可以通过 SHOW ENGINE INNODB STATUS 语句来查看最近发生的一次死锁信息，
SHOW ENGINE INNODB STATUS 只会显示最近一次发生的死锁信息，如果死锁频繁出现，可以将全局系统变量 innodb_print_all_deadlocks 设置为 ON，这样可以将死锁发生时的信息都记录在 MySQL 的错误日志中。
如下：
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
... 忽略部分信息
# 最近一次死锁
------------------------
LATEST DETECTED DEADLOCK
------------------------
# 死锁发生时间， 0x3d08 是操作系统为当前会话分配的线程的线程编号。
2024-06-27 13:55:20 0x29ec
# 死锁发生的第一个事务的有关信息
*** (1) TRANSACTION:
# 为事务分配的id是 195635， 事务处于 ACTIVE 状态 7s了，事务当前做的操作是 " starting index read"
# 该事务的id 比第二个事务的id 小，也就说明当前事务是先执行的，即 事务 T1
TRANSACTION 195635, ACTIVE 7 sec starting index read
# 此事务当前执行的语句使用了一个表，为一个表上了锁
mysql tables in use 1, locked 1
# 此事务处于 LOCK WAIT 状态，拥有3个锁结构，2个行锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
# 执行此事务的线程信息
MySQL thread id 22, OS thread handle 15624, query id 461 localhost ::1 root statistics
# 发生死锁时执行的语句
SELECT * FROM hero WHERE number = 3 FOR UPDATE
# 此事务当前在等待获取的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2432 page no 3 n bits 80 index PRIMARY of table `demo`.`hero` trx id 195635 lock_mode X locks rec but not gap waiting
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000001763c; asc v<;;
2: len 7; hex ad000001210110; asc ! ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z ;;
4: len 3; hex e89c80; asc ;;
# 死锁发生时第二个事务的信息
*** (2) TRANSACTION:
# 为事务分配的id是 195636， 事务处于 ACTIVE 状态 5s了，事务当前做的操作是 " starting index read"
# 该事务的id 比第一个事务的id 大，也就说明当前事务是后执行的， 也就是事务T2
TRANSACTION 195636, ACTIVE 5 sec starting index read, thread declared inside InnoDB 5000
# 此事务当前执行的语句使用了一个表，为一个表上了锁
mysql tables in use 1, locked 1
# 此事务拥有3个锁结构，2个行锁
3 lock struct(s), heap size 1136, 2 row lock(s)
# 执行此事务的线程信息
MySQL thread id 21, OS thread handle 10732, query id 460 localhost ::1 root statistics
# 发生死锁时执行的语句
SELECT * FROM hero WHERE number = 1 FOR UPDATE
# 此事务已经获取的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2432 page no 3 n bits 80 index PRIMARY of table `demo`.`hero` trx id 195636 lock_mode X locks rec but not gap
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 00000001763c; asc v<;;
2: len 7; hex ad000001210110; asc ! ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z ;;
4: len 3; hex e89c80; asc ;;
# 此事务等待的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2432 page no 3 n bits 80 index PRIMARY of table `demo`.`hero` trx id 195636 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000001761e; asc v ;;
2: len 7; hex 3c000002c10110; asc < ;;
3: len 7; hex 6ce58898e5a487; asc l ;;
4: len 3; hex e89c80; asc ;;
# InnoDB 决定回滚 第二个事务
*** WE ROLL BACK TRANSACTION (2)
... 忽略部分信息
1 row in set (0.00 sec)
四、参考内容
书籍：《MySQL是怎样运行的——从根儿上理解MySQL》、《MySQL技术内幕 InnoDB存储引擎 》
https://blog.csdn.net/filling_l/article/details/112854716
如有侵扰，联系删除。 内容仅用于自我记录学习使用。如有错误，欢迎指正
扫一扫
655
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
