文章目录5. 隔离性实现原理5.3 锁5.3.9 死锁5.3.9.1 示例5.3.9.2 死锁产生的条件5.3.9.3 InnoDB对死锁的检测5.3.9.4 如何避免死锁5.4 查看并设置隔离级别5. 隔离性实现原理5.3 锁5.3.9 死锁5.3.9.1 示例由于每个事务都持有另一个事务所需的锁导致事务无法继续进行的情况称为死锁。以下图为例两个事务都不会主动释放自己持有的锁并且都在等待对方持有的资源变得可用。下面通过一个示例演示一下死锁的发生过程其中涉及两个客户端A和B并通过启用全局变量innodb_print_all_deadlocks来查看死锁的信息同时死锁信息也会保存到错误日志中首先打开一个客户端A并执行以下操作# 客户端A启用innodb_print_all_deadlocks mysql SET GLOBAL innodb_print_all_deadlocks ON; Query OK, 0 rows affected (0.00 sec) # 创建两个张表animals和birds mysql CREATE TABLE animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE InnoDB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE InnoDB; Query OK, 0 rows affected (0.01 sec) # 分别向两张表中写入数据 mysql INSERT INTO animals (name,value) VALUES (dog,10); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO birds (name,value) VALUES (magpie,20); Query OK, 1 row affected (0.00 sec) # 客户端A开启一个事务 mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 使用共享锁查询animals表中的某一行 mysql SELECT value FROM animals WHERE namedog FOR SHARE; ------- | value | ------- | 10 | ------- 1 row in set (0.00 sec)接下来打开客户端B并执行以下操作# 开启一个事务B mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 使用共享锁查询birds表中的某一行 mysql SELECT value FROM birds WHERE namemagpie FOR SHARE; ------- | value | ------- | 20 | ------- 1 row in set (0.00 sec)在任意一个客户端中查看两个select操作持有的锁信息# 查看锁信息 mysql SELECT ENGINE_TRANSACTION_ID as Trx_Id, - OBJECT_NAME as Table, - INDEX_NAME as Index, - LOCK_DATA as Data, - LOCK_MODE as Mode, - LOCK_STATUS as Status, - LOCK_TYPE as Type - FROM performance_schema.data_locks; ----------------------------------------------------------------------------- | Trx_Id | Table | Index | Data | Mode | Status | Type | ----------------------------------------------------------------------------- | 421458607848664 | animals | NULL | NULL | IS | GRANTED | TABLE | | 421458607850280 | birds | NULL | NULL | IS | GRANTED | TABLE | | 421458607848664 | animals | PRIMARY | dog | S,REC_NOT_GAP | GRANTED | RECORD | | 421458607850280 | birds | PRIMARY | magpie | S,REC_NOT_GAP | GRANTED | RECORD | ----------------------------------------------------------------------------- 4 rows in set (0.00 sec) mysql在客户端B中更新animals表中的行# 更新animals表中的行 mysql UPDATE animals SET value30 WHERE namedog;客户B开始等待可以进入客户端A查看锁的等待信息:# 查看等待中的锁 mysql SELECT REQUESTING_ENGINE_LOCK_ID as Req_Lock_Id, - REQUESTING_ENGINE_TRANSACTION_ID as Req_Trx_Id, - BLOCKING_ENGINE_LOCK_ID as Blk_Lock_Id, - BLOCKING_ENGINE_TRANSACTION_ID as Blk_Trx_Id - FROM performance_schema.data_lock_waits; ----------------------------------------------------------------------------------------------------------------------- | Req_Lock_Id | Req_Trx_Id | Blk_Lock_Id | Blk_Trx_Id | ----------------------------------------------------------------------------------------------------------------------- | 139983631139624:310:234:4:2:139983520331960 | 14875 | 139983631138008:310:234:4:2:139983520319456 | 421458607848664 | ----------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) # 查看锁信息此时客户端A持有锁客户端B持有锁客户端B等待锁距离死锁只差一步“客户端A等待锁” mysql SELECT ENGINE_LOCK_ID as Lock_Id, - ENGINE_TRANSACTION_ID as Trx_id, - OBJECT_NAME as Table, - INDEX_NAME as Index, - LOCK_DATA as Data, - LOCK_MODE as Mode, - LOCK_STATUS as Status, - LOCK_TYPE as Type - FROM performance_schema.data_locks; -------------------------------------------------------------------------------------------------------------------------- | Lock_Id | Trx_id | Table | Index | Data | Mode | Status | Type | -------------------------------------------------------------------------------------------------------------------------- | 139983631138008:310:1298:139983520322448 | 421458607848664 | animals | NULL | NULL | IS | GRANTED | TABLE | | 139983631139624:310:1298:139983520334616 | 14875 | animals | NULL | NULL | IX | GRANTED | TABLE | | 139983631139624:310:1299:139983520334528 | 14875 | birds | NULL | NULL | IS | GRANTED | TABLE | | 139983631138008:310:234:4:2:139983520319456 | 421458607848664 | animals | PRIMARY | dog | S,REC_NOT_GAP | GRANTED | RECORD | | 139983631139624:310:234:4:2:139983520332304 | 14875 | animals | PRIMARY | dog | X,REC_NOT_GAP | WAITING | RECORD | | 139983631139624:310:235:4:2:139983520331616 | 14875 | birds | PRIMARY | magpie | S,REC_NOT_GAP | GRANTED | RECORD | -------------------------------------------------------------------------------------------------------------------------- 6 rows in set (0.00 sec) mysqlInnoDB只有在事务试图修改数据时才使用顺序事务id之前的只读事务id由421458607848664变为14875如果客户端A试图同时更新birds中的一行将导致死锁此时客户端A持有锁客户端B持有锁客户端B等待锁客户端A等待锁满足死锁的四个条件# 更新birds中的某一行此是发生死锁 mysql UPDATE birds SET value40 WHERE namemagpie; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生时InnoDB主动回滚导致死锁的事务事务A释放了所有的锁所以此时可以看到客户端B的更新执行成功。# 之前的更新操作 mysql UPDATE animals SET value30 WHERE namedog; # ... 中间等待了很久直到客户端A触发死锁 Query OK, 1 row affected (4.01 sec) Rows matched: 1 Changed: 1 Warnings: 0可以通过以下方式查看当前服务器发生死锁的次数# 查看当前服务器发生死锁的次数 mysql SELECT count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAMElock_deadlocks; ------- | count | ------- | 1 | ------- 1 row in set (0.00 sec) mysqlInnoDB的j监视器包含了关于死锁和事务的相关信息可以通过SHOW ENGINE INNODB STATUS;查看LATEST DETECTED DEADLOCK节点的内容mysql SHOW ENGINE INNODB STATUS; # ...... ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2026-06-07 19:15:19 139983148107328 *** (1) TRANSACTION: TRANSACTION 14875, ACTIVE 418 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s) MySQL thread id 70, OS thread handle 139983461516864, query id 543 localhost 127.0.0.1 root updating UPDATE animals SET value30 WHERE namedog *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 235 page no 4 n bits 72 index PRIMARY of table test_db.birds trx id 14875 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 6d6167706965; asc magpie;; 1: len 6; hex 000000003a1a; asc : ;; 2: len 7; hex 82000000fe0110; asc ;; 3: len 4; hex 80000014; asc ;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 234 page no 4 n bits 72 index PRIMARY of table test_db.animals trx id 14875 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 3; hex 646f67; asc dog;; 1: len 6; hex 000000003a18; asc : ;; 2: len 7; hex 82000000fd0110; asc ;; 3: len 4; hex 8000000a; asc ;; *** (2) TRANSACTION: TRANSACTION 14876, ACTIVE 445 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 69, OS thread handle 139983460460096, query id 544 localhost 127.0.0.1 root updating UPDATE birds SET value40 WHERE namemagpie *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 234 page no 4 n bits 72 index PRIMARY of table test_db.animals trx id 14876 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 3; hex 646f67; asc dog;; 1: len 6; hex 000000003a18; asc : ;; 2: len 7; hex 82000000fd0110; asc ;; 3: len 4; hex 8000000a; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 235 page no 4 n bits 72 index PRIMARY of table test_db.birds trx id 14876 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 6d6167706965; asc magpie;; 1: len 6; hex 000000003a1a; asc : ;; 2: len 7; hex 82000000fe0110; asc ;; 3: len 4; hex 80000014; asc ;; # 回滚事务2 *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 14877 Purge done for trxs n:o 14871 undo n:o 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421458607848664, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607849472, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607847856, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607847048, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 14875, ACTIVE 587 sec 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1 MySQL thread id 70, OS thread handle 139983461516864, query id 546 localhost 127.0.0.1 root starting SHOW ENGINE INNODB STATUS # ......错误日志中也记录了死锁相关的信息# 查看错误日志路径 mysql SELECT log_error; -------------------------- | log_error | -------------------------- | /var/log/mysql/error.log | -------------------------- 1 row in set (0.00 sec) mysql5.3.9.2 死锁产生的条件互斥访问如果线程1获取到了锁A那么线程2就不能同时得到锁A不可抢占获取到锁的线程只能自己主动释放锁别的线程不能从他的手中抢占锁保持与请求线程1已经获得了锁A还要在这个基础上再去获了锁B循环等待线程1等待线程2释放锁线程2也等待线程1释放锁死锁发生时系统中一定有由两个或两个以上的线程组成的一条环路该环路中的每个线程都在等待着下一个线程释放锁以上四条是造成死锁的必要条件必须同时满足所以如果想要打破死锁可以破坏以上四个条件之一最常见的方式就是打破循环等待。前两个是锁自身的性质一般不建议改。第三个是常见场景不建议改改了可能要调整业务逻辑。5.3.9.3 InnoDB对死锁的检测InnoDB在运行时会对死锁进行检测当死锁检测启用时(默认)InnoDB自动检测事务死锁并回滚一个或多个事务来打破死锁。InnoDB尝试选择小事务进行回滚其中事务的大小由插入、更新或删除的行数决定。谁触发死锁谁回滚。如果系统变量innodb_table_locks 1(默认) 和autocommit 0InnoDB可以检测到表级锁和行级锁级别发生的死锁否则无法检测到由lock TABLES语句设置的表锁或由非InnoDB存储引擎设置的锁对于无法检测到的死锁可以通过设置系统变量innodb_lock_wait_timeout的值来指定锁的超时时间来解决死锁问题当超过200个事务等待锁资源或等待的锁个数超过1,000,000个时也会被视为死锁并尝试将等待列表的事务回滚。在高并发系统中多个线程等待相同的锁时死锁检测可能会导致性能降性变慢此时禁用死锁检测并依赖innodb_lock_wait_timeout设置进行事务回滚可能性能更高。可以通过设置系统变量innodb_deadlock_detect[{OFF|ON}]禁用死锁检测。5.3.9.4 如何避免死锁MySQL是一个多线程程序死锁的情况大概率会发生但他并不可怕除非频繁出现导致无法运行某些事务InnoDB使用自动行级锁即使在只插入或删除单行的事务中也可能出现死锁。这是因为插入或删除行并不是真正的原子操作同时会对索引记录进行修改并设置锁使用以下技术来处理死锁并降低发生死锁的可能性:使用事务而不是使用LOCK TABLES语句手动加锁并使用innodb_lock_wait_timeout变量设置锁的超时时间保证任何情况下锁都可以自动释放经常使用SHOW ENGINE INNODB STATUS命令来确定最近一次死锁的原因。这可以帮助我们修改应用程序以避免死锁如果出现频繁的死锁警告可以通过启用innodb_print_all_deadlocks变量来收集调试信息。对于死锁的信息都记录在MySQL错误日志中调试完成后记得禁用此选项如果事务由于死锁而失败记得重新发起事务再执行一次尽量避免大事务保持事务粒度小且持续时间短这样事务之间就不容易发生冲突从而降低发生死锁的概率修改完成后立即提交事务也可以降低死锁发生的概率。特别注意的是不要在一个交互式会话中长时间打开一个未提交的事务当事务中要修改多个表或同一表中的不同行时每次都要以一致的顺序执行这些操作使事务中的修改操作形成定义良好的队列可以避免死锁。而不是在不同的位置编写多个类似的INSERT、UPDATE和DELETE语句。我们写的程序其实就是把一系列操作组织成一个方法或函数向表中添加适当的索引以便查询时扫描更少的索引并设置更少的锁可以使用EXPLAIN SELECT来确定哪些索引用于当前的查询使用表级锁防止对表进行并发更新可以避免死锁但代价是系统的并发性降低如果在查询时加锁比如SELECT…FOR UPDATE 或 SELECT…FOR SHARE尝试使用较低的隔离级别比如READ COMMITTED5.4 查看并设置隔离级别事务的隔离级别分为全局作用域和会话作用域查看不同作用域事务的隔离级别可以使用以下的方式# 全局作用域 SELECT GLOBAL.transaction_isolation; # 会话作用域 SELECT SESSION.transaction_isolation; # 可以看到默认的事务隔离级别是REPEATABLE-READ(可重复读) --------------------------------- | SESSION.transaction_isolation | --------------------------------- | REPEATABLE-READ | # 默认是可重复读 --------------------------------- 1 row in set (0.00 sec)设置事务的隔离级别和访问模式可以使用以下语法# 通过GLOBAL|SESSION分别指定不同作用域的事务隔离级别 SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level|access_mode; # 隔离级别 level: { | REPEATABLE READ # 可重复读 | READ COMMITTED # 读已提交 | READ UNCOMMITTED # 读未提交 | SERIALIZABLE # 串行化 } # 访问模式 access_mode: { | READ WRITE # 表示事务可以对数据进行读写 | READ ONLY # 表示事务是只读不能对数据进行读写 } # 示例 # 设置全局事务隔离级别为串行化 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; # 设置会话事务隔离级别为串行化 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; # 如果不指定任何作用域设置将在下一个事务开始生效 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;通过选项文件指定事务的隔离级别以便MySQL启动的时候读取并设置[mysqld] transaction-isolation REPEATABLE-READ # 隔离级别为可重复读 transaction-read-only OFF # 关闭只读意味着访问模式为读写TIPS:官网MySQL8.0更新描述The tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead.翻译tx_isolation和tx_read_only系统变量已被删除。请改用transaction_isolation和transaction_read_only所以在MySQL5.7及以前的版本中使用tx_isolation和tx_read_only来设置事务的隔离级别和访问模式。通过SET语法设置系统变量的方式设置事务的隔离级别# 方式一 SET GLOBAL transaction_isolation SERIALIZABLE; # 注意使用SET语法时有空格要用-代替 SET SESSION transaction_isolation REPEATABLE-READ; # 方式二 SET GLOBAL.transaction_isolationSERIALIZABLE; # 注意使用SET语法时有空格要用-代替 SET SESSION.transaction_isolationREPEATABLE-READ;设置事务隔离级别的语句不能在已开启的事务中执行否则将会报错# 开启事务 mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 修改事务的隔离级别将会报错 mysql SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ERROR 1568 (25001): Transaction characteristics cant be changed while a transaction is in progress
【MySQL高阶】28.事务(3)-死锁
文章目录5. 隔离性实现原理5.3 锁5.3.9 死锁5.3.9.1 示例5.3.9.2 死锁产生的条件5.3.9.3 InnoDB对死锁的检测5.3.9.4 如何避免死锁5.4 查看并设置隔离级别5. 隔离性实现原理5.3 锁5.3.9 死锁5.3.9.1 示例由于每个事务都持有另一个事务所需的锁导致事务无法继续进行的情况称为死锁。以下图为例两个事务都不会主动释放自己持有的锁并且都在等待对方持有的资源变得可用。下面通过一个示例演示一下死锁的发生过程其中涉及两个客户端A和B并通过启用全局变量innodb_print_all_deadlocks来查看死锁的信息同时死锁信息也会保存到错误日志中首先打开一个客户端A并执行以下操作# 客户端A启用innodb_print_all_deadlocks mysql SET GLOBAL innodb_print_all_deadlocks ON; Query OK, 0 rows affected (0.00 sec) # 创建两个张表animals和birds mysql CREATE TABLE animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE InnoDB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE InnoDB; Query OK, 0 rows affected (0.01 sec) # 分别向两张表中写入数据 mysql INSERT INTO animals (name,value) VALUES (dog,10); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO birds (name,value) VALUES (magpie,20); Query OK, 1 row affected (0.00 sec) # 客户端A开启一个事务 mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 使用共享锁查询animals表中的某一行 mysql SELECT value FROM animals WHERE namedog FOR SHARE; ------- | value | ------- | 10 | ------- 1 row in set (0.00 sec)接下来打开客户端B并执行以下操作# 开启一个事务B mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 使用共享锁查询birds表中的某一行 mysql SELECT value FROM birds WHERE namemagpie FOR SHARE; ------- | value | ------- | 20 | ------- 1 row in set (0.00 sec)在任意一个客户端中查看两个select操作持有的锁信息# 查看锁信息 mysql SELECT ENGINE_TRANSACTION_ID as Trx_Id, - OBJECT_NAME as Table, - INDEX_NAME as Index, - LOCK_DATA as Data, - LOCK_MODE as Mode, - LOCK_STATUS as Status, - LOCK_TYPE as Type - FROM performance_schema.data_locks; ----------------------------------------------------------------------------- | Trx_Id | Table | Index | Data | Mode | Status | Type | ----------------------------------------------------------------------------- | 421458607848664 | animals | NULL | NULL | IS | GRANTED | TABLE | | 421458607850280 | birds | NULL | NULL | IS | GRANTED | TABLE | | 421458607848664 | animals | PRIMARY | dog | S,REC_NOT_GAP | GRANTED | RECORD | | 421458607850280 | birds | PRIMARY | magpie | S,REC_NOT_GAP | GRANTED | RECORD | ----------------------------------------------------------------------------- 4 rows in set (0.00 sec) mysql在客户端B中更新animals表中的行# 更新animals表中的行 mysql UPDATE animals SET value30 WHERE namedog;客户B开始等待可以进入客户端A查看锁的等待信息:# 查看等待中的锁 mysql SELECT REQUESTING_ENGINE_LOCK_ID as Req_Lock_Id, - REQUESTING_ENGINE_TRANSACTION_ID as Req_Trx_Id, - BLOCKING_ENGINE_LOCK_ID as Blk_Lock_Id, - BLOCKING_ENGINE_TRANSACTION_ID as Blk_Trx_Id - FROM performance_schema.data_lock_waits; ----------------------------------------------------------------------------------------------------------------------- | Req_Lock_Id | Req_Trx_Id | Blk_Lock_Id | Blk_Trx_Id | ----------------------------------------------------------------------------------------------------------------------- | 139983631139624:310:234:4:2:139983520331960 | 14875 | 139983631138008:310:234:4:2:139983520319456 | 421458607848664 | ----------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) # 查看锁信息此时客户端A持有锁客户端B持有锁客户端B等待锁距离死锁只差一步“客户端A等待锁” mysql SELECT ENGINE_LOCK_ID as Lock_Id, - ENGINE_TRANSACTION_ID as Trx_id, - OBJECT_NAME as Table, - INDEX_NAME as Index, - LOCK_DATA as Data, - LOCK_MODE as Mode, - LOCK_STATUS as Status, - LOCK_TYPE as Type - FROM performance_schema.data_locks; -------------------------------------------------------------------------------------------------------------------------- | Lock_Id | Trx_id | Table | Index | Data | Mode | Status | Type | -------------------------------------------------------------------------------------------------------------------------- | 139983631138008:310:1298:139983520322448 | 421458607848664 | animals | NULL | NULL | IS | GRANTED | TABLE | | 139983631139624:310:1298:139983520334616 | 14875 | animals | NULL | NULL | IX | GRANTED | TABLE | | 139983631139624:310:1299:139983520334528 | 14875 | birds | NULL | NULL | IS | GRANTED | TABLE | | 139983631138008:310:234:4:2:139983520319456 | 421458607848664 | animals | PRIMARY | dog | S,REC_NOT_GAP | GRANTED | RECORD | | 139983631139624:310:234:4:2:139983520332304 | 14875 | animals | PRIMARY | dog | X,REC_NOT_GAP | WAITING | RECORD | | 139983631139624:310:235:4:2:139983520331616 | 14875 | birds | PRIMARY | magpie | S,REC_NOT_GAP | GRANTED | RECORD | -------------------------------------------------------------------------------------------------------------------------- 6 rows in set (0.00 sec) mysqlInnoDB只有在事务试图修改数据时才使用顺序事务id之前的只读事务id由421458607848664变为14875如果客户端A试图同时更新birds中的一行将导致死锁此时客户端A持有锁客户端B持有锁客户端B等待锁客户端A等待锁满足死锁的四个条件# 更新birds中的某一行此是发生死锁 mysql UPDATE birds SET value40 WHERE namemagpie; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction死锁发生时InnoDB主动回滚导致死锁的事务事务A释放了所有的锁所以此时可以看到客户端B的更新执行成功。# 之前的更新操作 mysql UPDATE animals SET value30 WHERE namedog; # ... 中间等待了很久直到客户端A触发死锁 Query OK, 1 row affected (4.01 sec) Rows matched: 1 Changed: 1 Warnings: 0可以通过以下方式查看当前服务器发生死锁的次数# 查看当前服务器发生死锁的次数 mysql SELECT count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAMElock_deadlocks; ------- | count | ------- | 1 | ------- 1 row in set (0.00 sec) mysqlInnoDB的j监视器包含了关于死锁和事务的相关信息可以通过SHOW ENGINE INNODB STATUS;查看LATEST DETECTED DEADLOCK节点的内容mysql SHOW ENGINE INNODB STATUS; # ...... ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2026-06-07 19:15:19 139983148107328 *** (1) TRANSACTION: TRANSACTION 14875, ACTIVE 418 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s) MySQL thread id 70, OS thread handle 139983461516864, query id 543 localhost 127.0.0.1 root updating UPDATE animals SET value30 WHERE namedog *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 235 page no 4 n bits 72 index PRIMARY of table test_db.birds trx id 14875 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 6d6167706965; asc magpie;; 1: len 6; hex 000000003a1a; asc : ;; 2: len 7; hex 82000000fe0110; asc ;; 3: len 4; hex 80000014; asc ;; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 234 page no 4 n bits 72 index PRIMARY of table test_db.animals trx id 14875 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 3; hex 646f67; asc dog;; 1: len 6; hex 000000003a18; asc : ;; 2: len 7; hex 82000000fd0110; asc ;; 3: len 4; hex 8000000a; asc ;; *** (2) TRANSACTION: TRANSACTION 14876, ACTIVE 445 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 69, OS thread handle 139983460460096, query id 544 localhost 127.0.0.1 root updating UPDATE birds SET value40 WHERE namemagpie *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 234 page no 4 n bits 72 index PRIMARY of table test_db.animals trx id 14876 lock mode S locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 3; hex 646f67; asc dog;; 1: len 6; hex 000000003a18; asc : ;; 2: len 7; hex 82000000fd0110; asc ;; 3: len 4; hex 8000000a; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 235 page no 4 n bits 72 index PRIMARY of table test_db.birds trx id 14876 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 6; hex 6d6167706965; asc magpie;; 1: len 6; hex 000000003a1a; asc : ;; 2: len 7; hex 82000000fe0110; asc ;; 3: len 4; hex 80000014; asc ;; # 回滚事务2 *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 14877 Purge done for trxs n:o 14871 undo n:o 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421458607848664, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607849472, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607847856, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421458607847048, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 14875, ACTIVE 587 sec 4 lock struct(s), heap size 1128, 4 row lock(s), undo log entries 1 MySQL thread id 70, OS thread handle 139983461516864, query id 546 localhost 127.0.0.1 root starting SHOW ENGINE INNODB STATUS # ......错误日志中也记录了死锁相关的信息# 查看错误日志路径 mysql SELECT log_error; -------------------------- | log_error | -------------------------- | /var/log/mysql/error.log | -------------------------- 1 row in set (0.00 sec) mysql5.3.9.2 死锁产生的条件互斥访问如果线程1获取到了锁A那么线程2就不能同时得到锁A不可抢占获取到锁的线程只能自己主动释放锁别的线程不能从他的手中抢占锁保持与请求线程1已经获得了锁A还要在这个基础上再去获了锁B循环等待线程1等待线程2释放锁线程2也等待线程1释放锁死锁发生时系统中一定有由两个或两个以上的线程组成的一条环路该环路中的每个线程都在等待着下一个线程释放锁以上四条是造成死锁的必要条件必须同时满足所以如果想要打破死锁可以破坏以上四个条件之一最常见的方式就是打破循环等待。前两个是锁自身的性质一般不建议改。第三个是常见场景不建议改改了可能要调整业务逻辑。5.3.9.3 InnoDB对死锁的检测InnoDB在运行时会对死锁进行检测当死锁检测启用时(默认)InnoDB自动检测事务死锁并回滚一个或多个事务来打破死锁。InnoDB尝试选择小事务进行回滚其中事务的大小由插入、更新或删除的行数决定。谁触发死锁谁回滚。如果系统变量innodb_table_locks 1(默认) 和autocommit 0InnoDB可以检测到表级锁和行级锁级别发生的死锁否则无法检测到由lock TABLES语句设置的表锁或由非InnoDB存储引擎设置的锁对于无法检测到的死锁可以通过设置系统变量innodb_lock_wait_timeout的值来指定锁的超时时间来解决死锁问题当超过200个事务等待锁资源或等待的锁个数超过1,000,000个时也会被视为死锁并尝试将等待列表的事务回滚。在高并发系统中多个线程等待相同的锁时死锁检测可能会导致性能降性变慢此时禁用死锁检测并依赖innodb_lock_wait_timeout设置进行事务回滚可能性能更高。可以通过设置系统变量innodb_deadlock_detect[{OFF|ON}]禁用死锁检测。5.3.9.4 如何避免死锁MySQL是一个多线程程序死锁的情况大概率会发生但他并不可怕除非频繁出现导致无法运行某些事务InnoDB使用自动行级锁即使在只插入或删除单行的事务中也可能出现死锁。这是因为插入或删除行并不是真正的原子操作同时会对索引记录进行修改并设置锁使用以下技术来处理死锁并降低发生死锁的可能性:使用事务而不是使用LOCK TABLES语句手动加锁并使用innodb_lock_wait_timeout变量设置锁的超时时间保证任何情况下锁都可以自动释放经常使用SHOW ENGINE INNODB STATUS命令来确定最近一次死锁的原因。这可以帮助我们修改应用程序以避免死锁如果出现频繁的死锁警告可以通过启用innodb_print_all_deadlocks变量来收集调试信息。对于死锁的信息都记录在MySQL错误日志中调试完成后记得禁用此选项如果事务由于死锁而失败记得重新发起事务再执行一次尽量避免大事务保持事务粒度小且持续时间短这样事务之间就不容易发生冲突从而降低发生死锁的概率修改完成后立即提交事务也可以降低死锁发生的概率。特别注意的是不要在一个交互式会话中长时间打开一个未提交的事务当事务中要修改多个表或同一表中的不同行时每次都要以一致的顺序执行这些操作使事务中的修改操作形成定义良好的队列可以避免死锁。而不是在不同的位置编写多个类似的INSERT、UPDATE和DELETE语句。我们写的程序其实就是把一系列操作组织成一个方法或函数向表中添加适当的索引以便查询时扫描更少的索引并设置更少的锁可以使用EXPLAIN SELECT来确定哪些索引用于当前的查询使用表级锁防止对表进行并发更新可以避免死锁但代价是系统的并发性降低如果在查询时加锁比如SELECT…FOR UPDATE 或 SELECT…FOR SHARE尝试使用较低的隔离级别比如READ COMMITTED5.4 查看并设置隔离级别事务的隔离级别分为全局作用域和会话作用域查看不同作用域事务的隔离级别可以使用以下的方式# 全局作用域 SELECT GLOBAL.transaction_isolation; # 会话作用域 SELECT SESSION.transaction_isolation; # 可以看到默认的事务隔离级别是REPEATABLE-READ(可重复读) --------------------------------- | SESSION.transaction_isolation | --------------------------------- | REPEATABLE-READ | # 默认是可重复读 --------------------------------- 1 row in set (0.00 sec)设置事务的隔离级别和访问模式可以使用以下语法# 通过GLOBAL|SESSION分别指定不同作用域的事务隔离级别 SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level|access_mode; # 隔离级别 level: { | REPEATABLE READ # 可重复读 | READ COMMITTED # 读已提交 | READ UNCOMMITTED # 读未提交 | SERIALIZABLE # 串行化 } # 访问模式 access_mode: { | READ WRITE # 表示事务可以对数据进行读写 | READ ONLY # 表示事务是只读不能对数据进行读写 } # 示例 # 设置全局事务隔离级别为串行化 SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; # 设置会话事务隔离级别为串行化 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; # 如果不指定任何作用域设置将在下一个事务开始生效 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;通过选项文件指定事务的隔离级别以便MySQL启动的时候读取并设置[mysqld] transaction-isolation REPEATABLE-READ # 隔离级别为可重复读 transaction-read-only OFF # 关闭只读意味着访问模式为读写TIPS:官网MySQL8.0更新描述The tx_isolation and tx_read_only system variables have been removed. Use transaction_isolation and transaction_read_only instead.翻译tx_isolation和tx_read_only系统变量已被删除。请改用transaction_isolation和transaction_read_only所以在MySQL5.7及以前的版本中使用tx_isolation和tx_read_only来设置事务的隔离级别和访问模式。通过SET语法设置系统变量的方式设置事务的隔离级别# 方式一 SET GLOBAL transaction_isolation SERIALIZABLE; # 注意使用SET语法时有空格要用-代替 SET SESSION transaction_isolation REPEATABLE-READ; # 方式二 SET GLOBAL.transaction_isolationSERIALIZABLE; # 注意使用SET语法时有空格要用-代替 SET SESSION.transaction_isolationREPEATABLE-READ;设置事务隔离级别的语句不能在已开启的事务中执行否则将会报错# 开启事务 mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) # 修改事务的隔离级别将会报错 mysql SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ERROR 1568 (25001): Transaction characteristics cant be changed while a transaction is in progress
