MySQL 死锁

2025-05-05 10:07:00
丁国栋
原创 24
摘要:本文记录MySQL 死锁的相关知识和处理措施。

如何监控mysql是否发生死锁?

MySQL 死锁监控方法详解

  1. 查看死锁日志

方法一:查看最近死锁信息

SHOW ENGINE InnoDB STATUS\G

在输出结果中查找"LATEST DETECTED DEADLOCK"部分,包含:

  • 死锁发生时间

  • 涉及的事务和SQL语句

  • 等待的锁资源

  • 被回滚的事务

方法二:启用详细死锁日志 在my.cnf/my.ini中添加:

[mysqld]
InnoDB_print_all_deadlocks = 1

这样所有死锁信息都会写入错误日志,而不仅是最近一次。

  1. 监控系统变量
-- 查看死锁相关统计
SHOW STATUS LIKE 'InnoDB_row_lock%';
SHOW STATUS LIKE 'InnoDB_deadlocks%';
-- 重要指标解释
InnoDB_row_lock_current_waits: 当前等待的锁数量
InnoDB_row_lock_time: 锁等待总时长(ms)
InnoDB_row_lock_time_avg: 平均锁等待时间(ms)
InnoDB_row_lock_time_max: 最大锁等待时间(ms)
InnoDB_row_lock_waits: 锁等待总次数
InnoDB_deadlocks: 死锁发生次数
  1. 使用性能模式(Performance Schema)
-- 启用锁监控
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/lock/metadata/sql/mdl';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_transactions%';
-- 查询锁等待信息
SELECT * FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE 'wait/lock%';
-- 查询死锁事务
SELECT * FROM performance_schema.events_transactions_current
WHERE STATE = 'DEADLOCK';
  1. 使用information_schema
-- 查看当前锁等待
SELECT * FROM information_schema.InnoDB_LOCKS;
SELECT * FROM information_schema.InnoDB_LOCK_WAITS;
SELECT * FROM information_schema.InnoDB_TRX;
-- 综合查询示例
SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.InnoDB_LOCK_WAITS w
INNER JOIN information_schema.InnoDB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.InnoDB_TRX r ON r.trx_id = w.requesting_trx_id;
  1. 配置自动监控

方法一:设置报警阈值

-- 设置锁等待超时时间(秒)
SET GLOBAL InnoDB_lock_wait_timeout = 50;

方法二:使用监控工具

  • Prometheus + MySQL Exporter

  • Percona Monitoring and Management (PMM)

  • Zabbix MySQL模板

  • Datadog/New Relic等APM工具

  1. 定期检查脚本示例
#!/bin/bash
# 检查死锁并发送报警
deadlocks=$(mysql -e "SHOW STATUS LIKE 'InnoDB_deadlocks'" | awk 'NR==2 {print $2}')
if [ "$deadlocks" -gt 0 ]; then
    # 获取详细死锁信息
    deadlock_info=$(mysql -e "SHOW ENGINE InnoDB STATUS\G")
    # 发送报警(示例使用邮件)
    echo "$deadlock_info" | mail -s "MySQL Deadlock Alert on $(hostname)" admin@example.com
    # 可选:记录到日志文件
    echo "$(date) - Detected $deadlocks deadlock(s)" >> /var/log/mysql_deadlocks.log
    echo "$deadlock_info" >> /var/log/mysql_deadlocks.log
fi
  1. 分析死锁的实用建议

  2. 收集完整信息:

    • 死锁发生时间点

    • 涉及的SQL语句

    • 表结构和索引情况

    • 系统负载情况

  3. 重现步骤:

    -- 1. 设置隔离级别
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    -- 2. 开启事务
    START TRANSACTION;
    -- 3. 执行会引发死锁的SQL
    UPDATE eps_user SET balance = balance - 100 WHERE id = 1;
    -- 在另一个会话中执行冲突操作...
  4. 解决方案参考:

    • 调整事务大小和持续时间

    • 统一操作顺序

    • 优化SQL和索引

    • 考虑使用乐观锁

    • 适当降低隔离级别

如果造成死锁的2个查询超过了 InnoDB_lock_wait_timeout 设置的时间会怎样?

MySQL死锁与锁等待超时的关系

当发生死锁的两个查询超过了InnoDB_lock_wait_timeout设置的时间时,MySQL的处理方式会有特殊之处,这与普通的锁等待超时不同。

死锁与锁等待超时的关键区别

  1. 死锁(Deadlock):

    • 是循环等待条件:事务A等待事务B,同时事务B也在等待事务A

    • MySQL的InnoDB引擎有死锁检测机制(默认启用)

    • 检测到死锁后会立即回滚其中一个事务(选择代价较小的事务)

  2. 锁等待超时(Lock Wait Timeout):

    • 是单向等待:事务A等待事务B,但事务B没有在等事务A

    • 等待时间超过InnoDB_lock_wait_timeout值(默认50秒)才会触发

    • 等待的事务会收到ERROR 1205 (HY000): Lock wait timeout exceeded错误

如果两个造成死锁的查询超过了InnoDB_lock_wait_timeout时间会怎样?实际上,这种情况不会发生,因为:

  1. InnoDB的死锁检测机制(默认开启)会在死锁形成时立即介入,不会等待超时

    • 检测到死锁后会在毫秒级时间内做出反应

    • 选择回滚其中一个事务(依据事务的undo log量判断)

  2. InnoDB_lock_wait_timeout仅适用于非死锁的锁等待场景

    • 例如:一个长事务持有锁不释放,其他会话等待这些锁
  3. 即使将InnoDB_lock_wait_timeout设为很短(如1秒):

    SET SESSION InnoDB_lock_wait_timeout=1;

    死锁仍会被优先检测并处理,不会等到超时

验证实验

可以通过以下实验验证:

-- 会话1
START TRANSACTION;
select balance from eps_user where id = 1;
UPDATE eps_user SET balance = balance + 100 WHERE id = 1;
-- 会话2
START TRANSACTION;
UPDATE eps_user SET balance = balance - 100 WHERE id = 1; -- 这里会死锁
-- 观察结果:会立即报错"Deadlock found"而非等待超时 

提示:如果不是死锁: MySQL 会报错 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

相关参数控制

  1. 死锁检测开关:

    SHOW VARIABLES LIKE 'InnoDB_deadlock_detect'; -- 默认ON

    如果关闭(OFF),系统会依赖锁等待超时来处理死锁(不推荐)

  2. 锁等待超时设置:

    SHOW VARIABLES LIKE 'InnoDB_lock_wait_timeout'; -- 默认50秒
    SET GLOBAL InnoDB_lock_wait_timeout=30; -- 修改全局设置

生产环境建议

  1. 保持InnoDB_deadlock_detect=ON(默认值)

  2. 设置合理的InnoDB_lock_wait_timeout

    • OLTP系统:建议10-30秒

    • 报表系统:可以设置更长(如120秒)

  3. 应用程序应处理这两种错误:

    • 死锁错误:ERROR 1213 (40001): Deadlock found

    • 锁等待超时错误:ERROR 1205 (HY000): Lock wait timeout exceeded

  4. 实现自动重试逻辑(针对死锁情况)

总结:死锁情况会被InnoDB优先检测并立即处理,不会等到InnoDB_lock_wait_timeout超时。该参数仅影响非死锁的锁等待场景。

死锁的例子

------------------------
2025-04-30 15:00:30 0x7fd045975700
*** (1) TRANSACTION:
TRANSACTION 9426720547, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 24978624, OS thread handle 140531733772032, query id 170473145852 172.16.10.123 zentao updating
DELETE FROM `zt_config` wHeRe 1=1  AND  `vision`  = '' AND  `owner`  = 'system' AND  `module`  = 'common' AND  `section`  = 'global' AND  `key`  = 'mode'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `怎套`.`zt_config` trx id 9426720547 lock_mode X locks rec but not gap waiting
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 0; hex ; asc ;;
 1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system                        ;;
 2: len 6; hex 636f6d6d6f6e; asc common;;
 3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global                        ;;
 4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode                          ;;
 5: len 3; hex 007480; asc  t ;;
*** (2) TRANSACTION:
TRANSACTION 9426720546, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 24978625, OS thread handle 140532497471232, query id 170473145857 172.16.10.123 zentao update
INSERT INTO `zt_config` (`owner`,`module`,`section`,`key`,`vision`,`value`) VALUES('system','common','global','mode','','PLM')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock_mode X locks rec but not gap
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 0; hex ; asc ;;
 1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system                        ;;
 2: len 6; hex 636f6d6d6f6e; asc common;;
 3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global                        ;;
 4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode                          ;;
 5: len 3; hex 007480; asc  t ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock mode S waiting
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
 0: len 0; hex ; asc ;;
 1: len 30; hex 73797374656d202020202020202020202020202020202020202020202020; asc system                        ;;
 2: len 6; hex 636f6d6d6f6e; asc common;;
 3: len 30; hex 676c6f62616c202020202020202020202020202020202020202020202020; asc global                        ;;
 4: len 30; hex 6d6f64652020202020202020202020202020202020202020202020202020; asc mode                          ;;
 5: len 3; hex 007480; asc  t ;;
*** WE ROLL BACK TRANSACTION (1)

MySQL 死锁日志分析

这段文字是 MySQL 数据库的死锁日志(Deadlock Log),记录了数据库发生死锁时的详细信息。

  • 关于 lock_mode X locks rec but not gap waitinglock_mode X locks rec but not gap
  1. lock_mode X locks rec but not gap waiting 英文翻译:排他锁模式,锁定记录但不锁定间隙,正在等待中

  2. lock_mode X locks rec but not gap 英文翻译:排他锁模式,锁定记录但不锁定间隙

关键术语解析:

  • lock_mode X → 排他锁模式(X锁)

  • locks rec → 锁定记录(record lock)

  • but not gap → 但不锁定间隙(非间隙锁)

  • waiting → 等待状态(表示该锁尚未获取,正在等待)

补充说明:

  1. 第一种带waiting的描述表示事务正在尝试获取这个排他锁但被阻塞
  2. 第二种不带waiting的描述表示该排他锁已被成功获取
  3. "不锁定间隙"意味着该锁只锁定索引记录本身,不锁定记录之前的间隙(与gap lock相对)

这种锁状态常见于:

  • UPDATE语句

  • DELETE语句

  • SELECT...FOR UPDATE语句

当使用唯一索引精确查询时(如WHERE id=1),InnoDB通常会使用这种"仅锁定记录"的锁模式。

  • MySQL日志中判断排他锁(X)和共享锁(S)的方法

在MySQL的死锁日志中,锁类型会明确标注出来。以下是最初提供的死锁日志中判断锁类型的方法:

日志关键部分解析

在最初提供的日志中,有以下关键锁信息:

事务1等待的锁

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720547 lock_mode X locks rec but not gap waiting
  • lock_mode X:明确表示这是一个排他锁(X锁)

  • locks rec but not gap:锁定记录但不锁定间隙

  • waiting:正在等待获取这个锁

事务2持有的锁

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock_mode X locks rec but not gap
  • lock_mode X:同样是排他锁(X锁)

  • 没有waiting标记,表示这个锁已被该事务持有

事务2等待的锁

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 21970879 page no 9 n bits 176 index unique of table `zentao`.`zt_config` trx id 9426720546 lock mode S waiting
  • lock mode S:明确表示这是一个共享锁(S锁)

  • waiting:正在等待获取这个锁

MySQL锁类型标识总结

在InnoDB死锁日志中,锁类型会通过以下方式明确标识:

  1. 排他锁(Exclusive Lock/X锁):

    • 标识为 lock_mode X

    • 用于UPDATE、DELETE、SELECT...FOR UPDATE等操作

    • 阻止其他事务获取任何类型的锁

  2. 共享锁(Shared Lock/S锁):

    • 标识为 lock mode S

    • 用于普通SELECT...LOCK IN SHARE MODE操作

    • 允许其他事务获取共享锁,但阻止排他锁

  3. 意向锁(Intention Lock):

    • 意向共享锁:lock_mode IS

    • 意向排他锁:lock_mode IX

  4. 间隙锁(Gap Lock):

    • lock_mode X locks gap before rec

    • 防止其他事务在索引记录间隙中插入

实际日志分析案例

根据原始日志,死锁形成的原因是:

  1. 事务1:尝试获取X锁(删除操作)但被阻塞

    DELETE FROM `zt_config` WHERE ...
  2. 事务2:

    • 已经持有同一记录的X锁(来自INSERT操作)

    • 同时又在尝试获取同一记录的S锁(可能是约束检查)

这样就形成了:

  • 事务1等待事务2释放X锁

  • 事务2等待事务1释放它需要的S锁

  • 循环等待导致死锁

如何预防这类死锁

  1. 统一操作顺序:确保所有事务以相同顺序访问表

  2. 减少事务范围:尽快提交事务

  3. 使用合适的索引:减少锁定的范围

  4. 考虑使用乐观锁:通过版本号控制而非悲观锁

  5. 调整隔离级别:在某些场景下使用READ COMMITTED

理解日志中的锁标识对于诊断和解决死锁问题至关重要,X和S的明确标注是分析死锁原因的关键线索。

死锁原因分析

  1. 事务1 (TRANSACTION 9426720547) 正在执行一个 DELETE 操作,试图删除 zt_config 表中的特定记录。

    • 它正在等待获取该记录的排他锁(X lock)
  2. 事务2 (TRANSACTION 9426720546) 正在执行一个 INSERT 操作,试图向 zt_config 表插入新记录。

    • 它已经持有该记录的排他锁(X lock)

    • 但同时又在等待获取同一记录的共享锁(S lock)

  3. 死锁形成:

    • 事务1等待事务2释放锁

    • 事务2在等待事务1释放锁(因为事务1的DELETE操作可能导致事务2需要检查约束)

    • 形成循环等待,MySQL检测到后选择回滚事务1

解决方案

  1. 应用层修改:

    • 确保对 zt_config 表的操作顺序一致

    • 将相关操作封装在同一个事务中

    • 考虑使用乐观锁而非悲观锁

  2. 数据库层优化:

    • 调整事务隔离级别(如从 REPEATABLE READ 改为 READ COMMITTED)

    • zt_config 表添加适当的索引

    • 考虑使用 SELECT ... FOR UPDATE 明确锁定顺序

  3. 监控与重试:

    • 实现应用层的死锁检测和自动重试机制

    • 监控频繁发生死锁的表和操作

这种死锁通常发生在高并发环境下对同一表进行混合操作(INSERT/DELETE/UPDATE)时,特别是在有唯一索引约束的情况下。

使用代码(如php+pdo 驱动)连接使用InnoDB引擎的MySQL服务器,如果php没有显式使用事务,那么也可能会出现上述死锁的情况么?

PHP+PDO连接MySQL时死锁的可能性分析

即使PHP代码没有显式使用事务,使用InnoDB引擎的MySQL仍然可能出现死锁情况。以下是详细解释:

可能发生死锁的情况

  1. 自动提交模式下的单条语句死锁
// 连接MySQL(默认autocommit=1)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
// 这条UPDATE语句本身就会创建一个隐式事务
$pdo->exec("UPDATE eps_user SET balance = balance - 100 WHERE id = 1");

即使没有显式事务,单条DML语句(INSERT/UPDATE/DELETE)在InnoDB中也会自动创建一个事务,当多个会话同时操作同一数据时可能发生死锁。

  1. 复合操作导致的死锁
// 假设有两个脚本几乎同时执行以下操作
// 脚本1:
$pdo->exec("UPDATE table_a SET col1 = 1 WHERE id = 5");
$pdo->exec("UPDATE table_b SET col2 = 2 WHERE id = 3");
// 脚本2:
$pdo->exec("UPDATE table_b SET col2 = 2 WHERE id = 3");
$pdo->exec("UPDATE table_a SET col1 = 1 WHERE id = 5");

虽然每条语句都是自动提交的,但当两个脚本以相反顺序操作相同行时,仍可能形成循环等待。

为什么会出现这种情况

  1. InnoDB的特性:

    • 所有DML操作都会创建事务(即使autocommit=1)

    • 锁是在语句执行时获取的,不是事务开始时

    • 锁只在语句完成后才释放(autocommit=1时立即提交)

  2. 死锁形成的条件:

    • 资源互斥:多个会话需要相同的锁

    • 持有并等待:会话持有锁同时等待其他锁

    • 循环等待:形成A等B,B等A的环路

    • 不可抢占:MySQL不能强制剥夺已获得的锁

  3. PHP+PDO的特殊性:

    • PDO默认autocommit=1(自动提交模式)

    • 但自动提交≠无事务,只是每个语句自动形成独立事务

    • 高并发时多个"微事务"仍可能冲突

解决方案

  1. 显式使用事务(推荐):
$pdo->beginTransaction();
try {
    $pdo->exec("UPDATE ...");
    $pdo->exec("UPDATE ...");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    // 处理错误或重试
}
  1. 操作顺序一致化: 确保不同脚本总是以相同顺序访问表/行

  2. 减少事务持有时间:

    • 尽快提交事务

    • 避免在事务中执行耗时操作

  3. 死锁检测与重试:

$retry = 3;
while ($retry--) {
    try {
        $pdo->beginTransaction();
        // ...执行操作...
        $pdo->commit();
        break;
    } catch (PDOException $e) {
        $pdo->rollBack();
        if (strpos($e->getMessage(), 'Deadlock') === false || $retry === 0) {
            throw $e;
        }
        usleep(rand(100000, 500000)); // 随机等待
    }
}
  1. 调整隔离级别(谨慎使用):
// 可以降低到READ COMMITTED减少锁冲突
$pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");

总结:即使PHP没有显式使用事务,由于InnoDB的机制和自动提交的特性,在高并发环境下仍然可能出现死锁。良好的事务设计和错误处理是避免这类问题的关键。

介绍一下mysql的事务隔离级别,介绍一下他们的优缺点和适用场景。

MySQL 事务隔离级别详解

MySQL 支持四种标准的事务隔离级别,它们定义了事务之间如何相互影响以及数据可见性的规则。了解这些隔离级别对于设计高性能、高并发的数据库应用至关重要。

  1. READ UNCOMMITTED (读未提交)

特点:

  • 最低的隔离级别

  • 事务可以读取其他事务未提交的修改("脏读")

  • 没有任何锁机制限制

优点:

  • 最高性能,几乎没有锁开销

  • 适合对数据一致性要求极低的场景

缺点:

  • 会出现脏读、不可重复读和幻读问题

  • 数据一致性难以保证

适用场景:

  • 统计分析类应用,允许近似结果

  • 数据仓库的ETL过程

  • 对实时性要求极高但可容忍脏读的场景

  1. READ COMMITTED (读已提交)

特点:

  • 事务只能看到其他事务已提交的修改

  • 避免了脏读问题

  • 使用行级锁,但只在语句执行期间持有

优点:

  • 比REPEATABLE READ性能更好

  • 避免了脏读问题

  • Oracle、PostgreSQL等数据库的默认级别

缺点:

  • 会出现不可重复读和幻读问题

  • 同一个事务中两次读取同一数据可能得到不同结果

适用场景:

  • 大多数OLTP应用的合理选择

  • 需要避免脏读但可接受不可重复读的场景

  • 报表生成等不需要事务内一致性的操作

  1. REPEATABLE READ (可重复读)

特点:

  • MySQL的默认隔离级别

  • 保证在同一个事务中多次读取同一数据结果相同

  • 使用MVCC(多版本并发控制)和间隙锁(Gap Lock)

优点:

  • 避免了脏读和不可重复读问题

  • 适合需要事务内一致性的场景

缺点:

  • 仍可能出现幻读问题(MySQL通过间隙锁部分解决)

  • 比READ COMMITTED有更高的锁开销

  • 可能导致更多的死锁

适用场景:

  • 需要事务内读取一致性的应用

  • 财务系统、订单处理等关键业务

  • MySQL的默认选择,适合大多数通用场景

  1. SERIALIZABLE (可串行化)

特点:

  • 最高的隔离级别

  • 通过强制事务串行执行来避免所有并发问题

  • 使用范围锁和表级锁

优点:

  • 完全避免脏读、不可重复读和幻读

  • 提供最严格的数据一致性保证

缺点:

  • 性能最差,并发度最低

  • 锁争用严重,可能导致大量超时

  • 不适用于高并发系统

适用场景:

  • 需要绝对数据一致性的关键系统

  • 银行核心系统、证券交易等金融应用

  • 数据迁移或批量处理等低并发操作

隔离级别对比表

隔离级别 脏读 不可重复读 幻读 性能 并发性
READ UNCOMMITTED 可能 可能 可能 最高 最高
READ COMMITTED 避免 可能 可能
REPEATABLE READ 避免 避免 可能
SERIALIZABLE 避免 避免 避免

“可能”的意思是可能出现、可能发生;“避免”的意思是不会出现,不会发生。

选择建议

  1. 默认选择:大多数应用使用REPEATABLE READ(MySQL默认)或READ COMMITTED(Oracle/PostgreSQL默认)即可

  2. 性能优先:对一致性要求不高的场景可考虑READ COMMITTED

  3. 一致性优先:关键业务系统可能需要SERIALIZABLE

  4. 特殊需求:某些场景可以混合使用不同隔离级别(如主业务用REPEATABLE READ,报表用READ COMMITTED)

  5. MySQL注意:InnoDB在REPEATABLE READ下通过间隙锁部分解决了幻读问题,这是与标准SQL的不同之处

实际应用中,隔离级别的选择需要在数据一致性和系统性能之间找到平衡点。

解释一下mysql的事务隔离级别中的脏读、幻读、不可重复读的概念和例子。

MySQL事务隔离级别中的并发问题详解

  1. 脏读 (Dirty Read)

概念:一个事务读取了另一个未提交事务修改过的数据。

发生条件:READ UNCOMMITTED隔离级别

例子:

-- 会话1 (事务A)
START TRANSACTION;
UPDATE eps_user SET balance = balance - 100 WHERE id = 1;
-- 尚未提交
-- 会话2 (事务B) 使用READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM eps_user WHERE id = 1; -- 读取到未提交的修改(脏数据)
-- 会话1
ROLLBACK; -- 事务A回滚
-- 会话2
SELECT balance FROM eps_user WHERE id = 1; -- 现在读取的值与之前不同
COMMIT;

影响:事务B基于从未真正存在过的数据做出了决策。

  1. 不可重复读 (Non-repeatable Read)

概念:在同一个事务内,多次读取同一数据返回不同结果(因为其他事务修改并提交了该数据)。期望是在同一个事务内多次读取得到相同的结果,即可以重复读。

发生条件:READ UNCOMMITTED 或 READ COMMITTED隔离级别

例子:

-- 会话1 (事务A)
START TRANSACTION;
SELECT balance FROM eps_user WHERE id = 1; -- 第一次读取,返回1000
-- 会话2 (事务B)
UPDATE eps_user SET balance = 900 WHERE id = 1;
COMMIT;
-- 会话1
SELECT balance FROM eps_user WHERE id = 1; -- 第二次读取,返回900
COMMIT;

影响:事务A中的两次读取结果不一致,可能导致业务逻辑错误。

  1. 幻读 (Phantom Read)

概念:在同一个事务内,多次执行相同的查询返回不同的行集合(因为其他事务新增或删除了符合查询条件的行并提交)。

发生条件:READ UNCOMMITTED、READ COMMITTED 或 REPEATABLE READ隔离级别

例子:

-- 会话1 (事务A)
START TRANSACTION;
SELECT * FROM eps_user WHERE balance > 800; -- 返回2条记录
-- 会话2 (事务B)
INSERT INTO eps_user(id, balance) VALUES(3, 1000);
COMMIT;
-- 会话1
SELECT * FROM eps_user WHERE balance > 800; -- 现在返回3条记录
COMMIT;

与不可重复读的区别:

  • 不可重复读针对的是已存在行的数据变更

  • 幻读针对的是行数量的变化(新增或删除的行)

MySQL各隔离级别对问题的解决情况

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED 可能 可能 可能
READ COMMITTED 避免 可能 可能
REPEATABLE READ 避免 避免 可能*
SERIALIZABLE 避免 避免 避免

*注:MySQL的InnoDB在REPEATABLE READ下通过MVCC和间隙锁(Gap Lock)机制避免了大部分幻读情况,这与SQL标准有所不同。

实际示例演示

创建测试表:

CREATE TABLE test_concurrency (
    id INT PRIMARY KEY,
    value VARCHAR(100)
) ENGINE=InnoDB;
INSERT INTO test_concurrency VALUES
(1, '原始数据A'),
(2, '原始数据B');

演示1:脏读

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM test_concurrency; -- 看到原始数据
-- 会话2
START TRANSACTION;
UPDATE test_concurrency SET value = '修改未提交' WHERE id = 1;
-- 会话1
SELECT * FROM test_concurrency; -- 看到未提交的修改(脏读)
-- 会话2
ROLLBACK;
-- 会话1
SELECT * FROM test_concurrency; -- 数据恢复原状
COMMIT;

演示2:不可重复读

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM test_concurrency WHERE id = 1; -- 第一次读取
-- 会话2
UPDATE test_concurrency SET value = '已提交修改' WHERE id = 1;
COMMIT;
-- 会话1
SELECT * FROM test_concurrency WHERE id = 1; -- 第二次读取结果不同
COMMIT;

演示3:幻读

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 看到2条
-- 会话2
INSERT INTO test_concurrency VALUES (3, '新数据C');
COMMIT;
-- 会话1
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 仍看到2条(MVCC避免幻读)
UPDATE test_concurrency SET value = CONCAT(value, '*') WHERE value LIKE '%数据%';
-- 此时会发现3行被更新,因为UPDATE能看到最新数据
SELECT * FROM test_concurrency WHERE value LIKE '%数据%'; -- 现在看到3条
COMMIT;

理解这些并发问题有助于根据应用需求选择合适的隔离级别,在数据一致性和系统性能之间取得平衡。

如果替换InnoDB引擎为myisam引擎可以避免这种死锁么?

使用MyISAM引擎能否避免死锁

替换InnoDB为MyISAM引擎不能真正避免死锁,但死锁的表现形式和发生机制会有本质不同。以下是详细分析:

MyISAM与InnoDB锁机制的根本区别

特性 InnoDB MyISAM
锁粒度 行级锁 表级锁
锁类型 共享锁(S)、排他锁(X)、意向锁等 读锁(共享)、写锁(排他)
死锁检测 支持自动检测和解决 不支持
事务支持 完整ACID事务 不支持事务
并发控制 MVCC(多版本并发控制) 纯锁机制

MyISAM下的"类死锁"情况

虽然MyISAM没有严格意义上的死锁(deadlock),但会出现并发写阻塞导致的长时间等待,表现类似死锁:

  1. 写锁排队问题:
-- 会话1:
LOCK TABLE t WRITE;  -- 获取写锁
-- 长时间不释放...
-- 会话2:
LOCK TABLE t WRITE;  -- 必须等待会话1释放
-- 此时所有其他会话的读写操作都会被阻塞
  1. 读写混合阻塞:
-- 会话1:
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE; -- 获取读锁
-- 不释放...
-- 会话2:
UPDATE t SET col1=1 WHERE id=1; -- 需要写锁,必须等待会话1释放
-- 此时会话3的普通SELECT也会被阻塞(MyISAM读会排队等待)

为什么MyISAM不能真正解决问题

  1. 更粗粒度的阻塞:

    • InnoDB死锁通常只影响少数几行

    • MyISAM的锁会阻塞整个表的访问

  2. 无自动解决机制:

    • InnoDB能自动检测并回滚死锁事务

    • MyISAM只能等待超时(lock_wait_timeout,默认31536000秒/1年!)

  3. 更严重的性能问题:

    -- 即使是不相关的更新也会互相阻塞
    -- 会话1:
    UPDATE users SET status=1 WHERE id=1;
    -- 会话2:
    UPDATE users SET name='test' WHERE id=2; -- 必须等待
  4. 缺乏事务支持:

    // MyISAM下无法回滚
    $pdo->beginTransaction();
    $pdo->exec("UPDATE table1..."); // 立即生效
    $pdo->exec("UPDATE table2..."); // 失败也无法回滚前一句
    $pdo->commit(); // 对MyISAM无意义

何时可以考虑MyISAM

虽然不推荐用MyISAM解决死锁问题,但在以下场景可能适用:

  1. 只读或极少写的表(数据字典、配置表)
  2. 需要全文索引(MySQL 5.6前InnoDB不支持)
  3. 空间数据存储(GIS功能在MyISAM中更成熟)
  4. 极度简单的查询(无事务要求的日志表)

更好的替代方案

  1. 优化InnoDB使用:

    • 合理设计事务范围和持续时间

    • 统一操作顺序避免循环等待

    • 使用SELECT...FOR UPDATE明确锁定

  2. 调整参数:

    SET InnoDB_lock_wait_timeout = 50; -- 设置更合理的等待超时
    SET InnoDB_deadlock_detect = ON;    -- 确保死锁检测开启
  3. 架构层面:

    • 引入消息队列缓解并发压力

    • 考虑读写分离

    • 对热点数据使用缓存

结论:单纯替换存储引擎不是解决死锁的正确方法。MyISAM会带来更严重的并发性能问题,而合理设计和使用InnoDB才是根本解决方案。

如何很容易的复现MySQL 死锁?

MySQL 死锁复现方法

下面我将介绍几种简单有效的方法来复现MySQL死锁场景,帮助理解死锁的形成机制。

方法一:经典交叉更新(最易实现)

准备测试表

CREATE TABLE deadlock_test (
    id INT PRIMARY KEY,
    value INT
) ENGINE=InnoDB;
INSERT INTO deadlock_test VALUES (1, 100), (2, 200);

复现步骤

会话1:

BEGIN;
UPDATE deadlock_test SET value = value + 1 WHERE id = 1;
-- 不要提交,保持事务开放

会话2:

BEGIN;
UPDATE deadlock_test SET value = value + 1 WHERE id = 2;
-- 不要提交,保持事务开放

会话1:

UPDATE deadlock_test SET value = value + 1 WHERE id = 2; -- 这会阻塞

会话2:

UPDATE deadlock_test SET value = value + 1 WHERE id = 1; -- 触发死锁

此时MySQL会检测到死锁并自动回滚其中一个事务。

方法二:Gap Lock导致的死锁

准备测试表

CREATE TABLE gap_lock_test (
    id INT PRIMARY KEY,
    name VARCHAR(20)
) ENGINE=InnoDB;
INSERT INTO gap_lock_test VALUES (1, 'A'), (3, 'C'), (5, 'E');

复现步骤

会话1:

BEGIN;
SELECT * FROM gap_lock_test WHERE id BETWEEN 2 AND 4 FOR UPDATE; -- 获取gap锁

会话2:

BEGIN;
SELECT * FROM gap_lock_test WHERE id BETWEEN 2 AND 4 FOR UPDATE; -- 同样获取gap锁

会话1:

INSERT INTO gap_lock_test VALUES (2, 'B'); -- 尝试在gap中插入

会话2:

INSERT INTO gap_lock_test VALUES (4, 'D'); -- 触发死锁

方法三:并发插入唯一键冲突

准备测试表

CREATE TABLE unique_conflict (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) UNIQUE
) ENGINE=InnoDB;

复现步骤

会话1:

BEGIN;
INSERT INTO unique_conflict (username) VALUES ('test_user');
-- 不要提交

会话2:

BEGIN;
INSERT INTO unique_conflict (username) VALUES ('test_user'); -- 阻塞

会话1:

INSERT INTO unique_conflict (username) VALUES ('test_user'); -- 触发死锁

方法四:混合读写操作死锁

准备测试表

CREATE TABLE mixed_ops (
    id INT PRIMARY KEY,
    balance DECIMAL(10,2)
) ENGINE=InnoDB;
INSERT INTO mixed_ops VALUES (1, 1000.00), (2, 2000.00);

复现步骤

会话1:

BEGIN;
SELECT * FROM mixed_ops WHERE id = 1 FOR UPDATE;

会话2:

BEGIN;
SELECT * FROM mixed_ops WHERE id = 2 FOR UPDATE;

会话1:

SELECT * FROM mixed_ops WHERE id = 2 LOCK IN SHARE MODE; -- 阻塞

会话2:

SELECT * FROM mixed_ops WHERE id = 1 LOCK IN SHARE MODE; -- 触发死锁

查看死锁信息

发生死锁后,可以查看详细信息:

SHOW ENGINE INNODB STATUS\G

在输出中查找"LATEST DETECTED DEADLOCK"部分。

复现技巧提示

  1. 使用BEGIN显式开启事务而非START TRANSACTION(更易控制)
  2. 在事务中适当添加SELECT SLEEP(1)人为制造时间差
  3. 使用多个终端或MySQL客户端窗口模拟并发
  4. 先让两个会话各自获取不同的锁,再尝试获取对方持有的锁

自动化测试脚本

#!/bin/bash
# 死锁自动复现脚本(需要两个MySQL客户端)
# 会话1操作
mysql -e "
CREATE TABLE IF NOT EXISTS deadlock_demo(id INT PRIMARY KEY, v INT) ENGINE=InnoDB;
TRUNCATE TABLE deadlock_demo;
INSERT INTO deadlock_demo VALUES (1,100),(2,200);
BEGIN;
UPDATE deadlock_demo SET v = v + 1 WHERE id = 1;
SELECT SLEEP(1);  -- 等待会话2执行
UPDATE deadlock_demo SET v = v + 1 WHERE id = 2;
COMMIT;
" &
# 会话2操作
mysql -e "
BEGIN;
UPDATE deadlock_demo SET v = v + 1 WHERE id = 2;
SELECT SLEEP(1);  -- 等待会话1执行
UPDATE deadlock_demo SET v = v + 1 WHERE id = 1;
COMMIT;
" &
wait
echo "检查死锁日志:"
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 50 "LATEST DETECTED DEADLOCK"

通过以上方法,可以轻松复现并研究各种MySQL死锁场景,帮助更好地理解和解决生产环境中的死锁问题。

MySQL InnoDB 对死锁的处理机制

InnoDB 存储引擎对死锁有一套完整的检测和处理机制,以下是其核心处理方式:

  1. 死锁检测机制

自动检测:

  • 默认启用 (innodb_deadlock_detect=ON)

  • 周期性检查事务等待图(wait-for graph)中的循环依赖

  • 检测频率由系统负载决定,通常非常快速(毫秒级)

检测原理:

  • 构建事务等待图(有向图)

  • 检查图中是否存在环(cycle)

  • 使用深度优先搜索(DFS)算法查找环

  1. 死锁处理策略

当检测到死锁时,InnoDB会:

  1. 选择牺牲者(victim):

    • 根据事务权重(通常依据修改的数据量)

    • 选择回滚代价较小的事务作为牺牲者

    • 可通过innodb_deadlock_detect_algorithm配置算法(默认DEPTH_FIRST)

  2. 回滚操作:

    • 自动回滚被选中的整个事务

    • 释放该事务持有的所有锁

    • 写入undo日志保证数据一致性

  3. 错误返回:

    • 向客户端返回ERROR 1213 (40001): Deadlock found

    • SHOW ENGINE INNODB STATUS中记录详细死锁信息

  4. 相关配置参数

参数名 默认值 说明
innodb_deadlock_detect ON 是否启用死锁检测
innodb_lock_wait_timeout 50(秒) 锁等待超时时间
innodb_deadlock_detect_algorithm DEPTH_FIRST 检测算法
innodb_print_all_deadlocks OFF 是否记录所有死锁到错误日志
  1. 特殊情况处理

禁用死锁检测时 (innodb_deadlock_detect=OFF)

  • 依赖innodb_lock_wait_timeout处理死锁

  • 不推荐生产环境使用,可能导致长时间阻塞

锁升级情况

  • 当锁请求需要等待超过innodb_lock_wait_timeout

  • 会返回ERROR 1205 (HY000): Lock wait timeout exceeded

  1. 死锁信息记录

死锁发生时,InnoDB会在以下位置记录信息:

  1. 错误日志:

    # 需开启
    SET GLOBAL innodb_print_all_deadlocks = ON;
  2. 引擎状态:

    SHOW ENGINE INNODB STATUS\G

    查看"LATEST DETECTED DEADLOCK"部分

  3. 性能模式:

    SELECT * FROM performance_schema.events_transactions_current;
  4. 最佳实践建议

  5. 应用层处理:

    • 实现自动重试逻辑(针对死锁错误)

    • 添加适当的休眠时间(指数退避)

  6. 数据库设计:

    • 统一事务操作顺序

    • 合理设计索引减少锁定范围

  7. 监控:

    -- 监控死锁频率
    SELECT count_star, sum_timer_wait/1000000000 as total_sec
    FROM performance_schema.events_waits_summary_global_by_event_name
    WHERE event_name = 'wait/io/table/sql/handler';

InnoDB的死锁处理机制设计精巧,能在保证数据一致性的同时,最小化死锁对系统的影响。了解这些机制有助于开发更健壮的数据库应用。

指数退避(Exponential Backoff)详解

指数退避是一种在计算机科学和网络通信中广泛使用的重试策略,特别适用于处理死锁、网络拥塞或资源竞争等情况。

基本概念

指数退避的核心思想是:每次重试前等待的时间呈指数增长,而不是固定间隔或随机间隔。这种设计可以:

  1. 避免大量请求同时重试导致的"惊群效应"
  2. 在系统恢复过程中逐渐增加负载
  3. 平衡即时响应和系统保护的需求

算法原理

典型的指数退避算法包含以下要素:

  1. 初始等待时间(base delay):如50ms
  2. 最大等待时间(max delay):如5秒
  3. 退避因子(通常为2)
  4. 随机抖动(jitter):避免同步重试

公式表示:

wait_time = min((2^n) * base_delay, max_delay) ± random_jitter

其中n是重试次数

MySQL死锁处理中的应用

在处理MySQL死锁时,指数退避的Java实现示例:

int retryCount = 0;
int maxRetries = 5;
long baseDelayMs = 50;
long maxDelayMs = 5000;
while (retryCount < maxRetries) {
    try {
        // 尝试执行数据库操作
        executeTransaction();
        break;
    } catch (SQLException e) {
        if (e.getErrorCode() == 1213) { // 死锁错误码
            retryCount++;
            if (retryCount >= maxRetries) {
                throw e;
            }
            // 计算等待时间(指数退避)
            long waitTime = Math.min(
                (long) (baseDelayMs * Math.pow(2, retryCount)),
                maxDelayMs
            );
            // 添加随机抖动(±20%)
            waitTime = waitTime * (80 + (int)(Math.random() * 40)) / 100;
            Thread.sleep(waitTime);
        } else {
            throw e;
        }
    }
}

为什么使用指数退避?

  1. 避免雪崩效应:当多个事务同时遇到死锁时,随机化的指数等待可以分散重试时间点
  2. 自适应负载:系统越繁忙,等待时间越长,自动适应系统状态
  3. 公平性:较早遇到错误的事务会获得较早的重试机会

实际应用场景

除了数据库死锁,指数退避还常用于:

  1. 网络请求失败重试(HTTP API调用)
  2. 分布式锁获取
  3. 消息队列消费失败处理
  4. 微服务间的服务调用

变体算法

  1. 线性退避:等待时间线性增长(如每次增加固定值)
  2. 多项式退避:等待时间按多项式函数增长
  3. 自适应退避:根据系统反馈动态调整参数

PHP 实现 MySQL 死锁处理的指数退避机制

以下是使用 PHP 实现指数退避策略处理 MySQL 死锁的完整代码示例:

<?php
function executeWithRetry($maxRetries = 5, $baseDelayMs = 50, $maxDelayMs = 5000) {
    $retryCount = 0;
    $lastException = null;
    while ($retryCount < $maxRetries) {
        try {
            // 尝试执行数据库操作
            return executeTransaction();
        } catch (PDOException $e) {
            $lastException = $e;
            // 检查是否为死锁错误 (MySQL 死锁错误码为 1213)
            if ($e->errorInfo[1] == 1213) {
                $retryCount++;
                if ($retryCount >= $maxRetries) {
                    break;
                }
                // 计算等待时间(指数退避)
                $waitTime = min(
                    $baseDelayMs * pow(2, $retryCount),
                    $maxDelayMs
                );
                // 添加随机抖动(±20%)
                $jitter = 0.8 + mt_rand(0, 40) / 100;
                $waitTime = $waitTime * $jitter;
                // 转换为微秒
                usleep($waitTime * 1000);
            } else {
                // 非死锁错误直接抛出
                throw $e;
            }
        }
    }
    // 重试次数用尽后抛出最后捕获的异常
    throw $lastException;
}
function executeTransaction() {
    $pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    try {
        $pdo->beginTransaction();
        // 执行你的SQL语句
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
        $stmt->execute();
        // 更多SQL操作...
        $pdo->commit();
        return true;
    } catch (PDOException $e) {
        $pdo->rollBack();
        throw $e; // 重新抛出异常供外部处理
    }
}
// 使用示例
try {
    $result = executeWithRetry();
    echo "操作成功执行";
} catch (PDOException $e) {
    echo "操作失败,最终错误: " . $e->getMessage();
}
?>

关键点说明

  1. 指数退避计算:

    • 使用 pow(2, $retryCount) 实现指数增长

    • min() 函数确保不超过最大等待时间

  2. 随机抖动:

    • 通过 mt_rand() 生成 0.8 到 1.2 之间的随机因子

    • 避免多个客户端同步重试

  3. 等待实现:

    • 使用 usleep() 进行毫秒级等待(注意参数是微秒)
  4. 错误处理:

    • 专门捕获 PDOException

    • 检查 errorInfo[1] 获取 MySQL 特定错误码

    • 只有死锁错误(1213)才会触发重试

  5. 事务管理:

    • 每个重试都会开启新的事务

    • 确保每次重试都是全新的操作

实际应用建议

  1. 参数调整:

    // 生产环境推荐设置
    executeWithRetry(
       $maxRetries = 3,       // 最大重试次数
       $baseDelayMs = 100,    // 初始等待100ms
       $maxDelayMs = 3000     // 最长等待3秒
    );
  2. 日志记录:

    // 在catch块中添加日志记录
    error_log("死锁发生,第{$retryCount}次重试,等待{$waitTime}ms");
  3. 监控集成:

    • 可以添加计数器统计死锁发生次数

    • 与监控系统集成报警

这个实现保持了指数退避的核心优势,同时适应PHP的语言特性,是处理MySQL死锁的可靠方案。

在数据库应用中,指数退避因其简单有效而成为处理死锁的首选策略。通过合理设置初始等待时间和最大等待时间,可以在系统响应速度和稳定性之间取得良好平衡。

发表评论
博客分类