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 waiting 和 lock_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 (可串行化)

特点：

• 最高的隔离级别

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

• 使用范围锁和表级锁

优点：

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

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

缺点：

• 性能最差，并发度最低

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

• 不适用于高并发系统

适用场景：

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

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

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

隔离级别对比表

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

选择建议

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各隔离级别对问题的解决情况

*注：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锁机制的根本区别

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. 相关配置参数

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死锁的可靠方案。

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