Carry の Blog
Carry の Blog
2024-07-27
目录

锁与死锁

# 锁与死锁

在数据库系统中，锁机制是保证数据一致性和并发控制的核心技术。然而，不当的锁使用可能导致死锁问题，严重影响系统的可用性。本文将深入探讨MySQL中的锁机制及其死锁问题。

# 1. 锁机制基础

# 1.1 锁的分类

MySQL中的锁可以从多个维度进行分类：

# 1.1.1 按锁的粒度分类

-- 表级锁
LOCK TABLES table1 READ, table2 WRITE;

-- 行级锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
1
2
3
4
5

# 1.1.2 按锁的性质分类

-- 共享锁（S锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（X锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;
1
2
3
4
5

# 1.1.3 按锁的类型分类

-- 记录锁（Record Lock）
-- 锁定索引记录本身

-- 间隙锁（Gap Lock）
-- 锁定索引记录之间的间隙

-- Next-Key Lock
-- 记录锁 + 间隙锁的组合
1
2
3
4
5
6
7
8

# 1.2 锁的获取与释放

-- 锁的获取
START TRANSACTION;
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- 获取排他锁

-- 锁的释放
COMMIT; -- 或 ROLLBACK;
-- 事务结束时自动释放锁
1
2
3
4
5
6
7
8

# 2. 死锁的概念与产生原因

# 2.1 死锁定义

死锁（Deadlock）是指两个或多个事务相互等待对方持有的锁，形成循环等待的局面，导致所有事务都无法继续执行。

# 2.2 死锁产生的必要条件

死锁的产生需要同时满足以下四个条件：

  1. 互斥条件：资源不能被多个事务同时使用
  2. 请求和保持条件：事务已获得部分资源，但仍需请求其他资源
  3. 不可剥夺条件：已分配的资源不能被强制释放
  4. 循环等待条件：存在事务间的循环等待链

# 2.3 死锁示例

-- 事务A
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
-- 获得记录1的排他锁

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 等待记录2的排他锁

-- 事务B
START TRANSACTION;
UPDATE users SET balance = balance - 200 WHERE id = 2;
-- 获得记录2的排他锁

UPDATE accounts SET balance = balance + 200 WHERE id = 1;
-- 等待记录1的排他锁

-- 死锁发生：A等待B释放锁，B等待A释放锁
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 3. 死锁检测机制

# 3.1 MySQL的死锁检测

MySQL的InnoDB存储引擎内置了死锁检测机制：

-- 查看死锁检测状态
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 死锁检测开关
SET GLOBAL innodb_deadlock_detect = ON;
1
2
3
4
5

# 3.2 死锁检测算法

-- 查看死锁信息
SHOW ENGINE INNODB STATUS\G
-- 查看LATEST DETECTED DEADLOCK部分
1
2
3

# 3.3 死锁检测过程

-- 死锁检测过程示例
-- 1. 事务A获取锁
-- 2. 事务B尝试获取锁，发现冲突
-- 3. 系统检测到循环等待
-- 4. 回滚其中一个事务（通常是代价较小的）
1
2
3
4
5

# 4. 死锁预防策略

# 4.1 锁排序策略

-- 保证事务获取锁的顺序一致
-- 事务A按ID升序获取锁
START TRANSACTION;
SELECT * FROM users WHERE id IN (1, 2, 3) FOR UPDATE;

-- 事务B也按相同顺序获取锁
START TRANSACTION;
SELECT * FROM users WHERE id IN (1, 2, 3) FOR UPDATE;
1
2
3
4
5
6
7
8

# 4.2 最小化事务范围

-- 短事务
START TRANSACTION;
-- 执行必要的操作
UPDATE users SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 避免长事务
START TRANSACTION;
-- 执行大量操作
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- ... 更多操作
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13

# 4.3 合理使用锁类型

-- 优先使用共享锁而非排他锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 仅在必要时使用排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
1
2
3
4
5

# 5. 死锁诊断与分析

# 5.1 查看死锁信息

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS\G

-- 查看死锁日志
SELECT * FROM performance_schema.events_waits_current 
WHERE EVENT_NAME LIKE '%deadlock%';
1
2
3
4
5
6

# 5.2 死锁检测脚本

-- 创建死锁监控脚本
DELIMITER //
CREATE PROCEDURE detect_deadlocks()
BEGIN
    DECLARE deadlock_count INT DEFAULT 0;
    
    -- 检查死锁次数
    SELECT COUNT(*) INTO deadlock_count 
    FROM performance_schema.events_waits_history_long 
    WHERE EVENT_NAME = 'wait/innodb/lock/deadlock';
    
    IF deadlock_count > 0 THEN
        INSERT INTO deadlock_log (timestamp, count) 
        VALUES (NOW(), deadlock_count);
    END IF;
END //
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 5.3 监控死锁发生频率

-- 监控死锁统计信息
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS 
WHERE VARIABLE_NAME LIKE '%Deadlock%';
1
2
3
4
5
6

# 6. 常见死锁场景及解决方案

# 6.1 范围锁导致的死锁

-- 场景：两个事务对相同范围的记录加锁
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;

-- 事务B
START TRANSACTION;
SELECT * FROM users WHERE age BETWEEN 25 AND 35 FOR UPDATE;

-- 可能导致死锁
1
2
3
4
5
6
7
8
9
10

# 6.2 多表操作死锁

-- 场景：跨表操作导致的死锁
-- 事务A
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- 事务B
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE user_id = 1;
UPDATE users SET balance = balance + 200 WHERE id = 1;

-- 可能导致死锁
1
2
3
4
5
6
7
8
9
10
11
12

# 6.3 间隙锁导致的死锁

-- 场景：间隙锁竞争
-- 事务A
START TRANSACTION;
SELECT * FROM users WHERE id > 10 AND id < 20 FOR UPDATE;

-- 事务B
START TRANSACTION;
SELECT * FROM users WHERE id > 15 AND id < 25 FOR UPDATE;

-- 可能导致死锁
1
2
3
4
5
6
7
8
9
10

# 7. 死锁处理策略

# 7.1 自动回滚策略

-- MySQL自动处理死锁
-- 当检测到死锁时，InnoDB会选择一个事务进行回滚
-- 通常选择回滚代价较小的事务

-- 查看死锁回滚信息
SHOW ENGINE INNODB STATUS\G
-- 查看死锁信息中的回滚事务
1
2
3
4
5
6
7

# 7.2 手动干预策略

-- 手动杀死死锁事务
-- 1. 查找死锁事务
SELECT 
    trx_id,
    trx_mysql_thread_id,
    trx_query,
    trx_state
FROM information_schema.innodb_trx 
WHERE trx_state = 'LOCK WAIT';

-- 2. 杀死指定事务
KILL <thread_id>;
1
2
3
4
5
6
7
8
9
10
11
12

# 7.3 应用层重试机制

# Python示例：应用层重试机制
import time
import mysql.connector
from mysql.connector import Error

def execute_with_retry(query, params=None, max_retries=3):
    for attempt in range(max_retries):
        try:
            connection = mysql.connector.connect(
                host='localhost',
                database='test_db',
                user='user',
                password='password'
            )
            
            cursor = connection.cursor()
            cursor.execute(query, params)
            result = cursor.fetchall()
            connection.commit()
            
            return result
            
        except mysql.connector.Error as err:
            if err.errno == 1213:  # Deadlock error
                print(f"Deadlock detected, retry {attempt + 1}")
                time.sleep(0.1 * (2 ** attempt))  # 指数退避
                continue
            else:
                raise err
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    raise Exception("Max retries exceeded")
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 8. 性能优化建议

# 8.1 减少锁竞争

-- 优化查询语句，减少锁的范围
-- 不好的做法
SELECT * FROM users WHERE age > 25 FOR UPDATE;

-- 好的做法
SELECT * FROM users WHERE id IN (1, 2, 3) FOR UPDATE;
1
2
3
4
5
6

# 8.2 使用合适的索引

-- 确保查询使用合适的索引
EXPLAIN SELECT * FROM users WHERE age = 25 FOR UPDATE;

-- 创建适当的索引
CREATE INDEX idx_age ON users(age);
1
2
3
4
5

# 8.3 事务设计优化

-- 事务设计优化示例
-- 1. 尽量减少事务中的操作
-- 2. 合理安排操作顺序
-- 3. 及时提交事务

START TRANSACTION;
-- 执行必要的操作
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;
COMMIT;
1
2
3
4
5
6
7
8
9
10

# 9. 监控与告警

# 9.1 创建死锁监控脚本

-- 创建死锁监控事件
CREATE EVENT deadlock_monitor
ON SCHEDULE EVERY 5 MINUTE
DO
BEGIN
    DECLARE deadlock_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO deadlock_count 
    FROM performance_schema.events_waits_history_long 
    WHERE EVENT_NAME = 'wait/innodb/lock/deadlock';
    
    IF deadlock_count > 0 THEN
        INSERT INTO alert_log (alert_type, message, timestamp) 
        VALUES ('DEADLOCK_DETECTED', 
                CONCAT('Deadlock count: ', deadlock_count), 
                NOW());
    END IF;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 9.2 配置死锁告警

-- 查看死锁相关变量
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

-- 开启死锁打印
SET GLOBAL innodb_print_all_deadlocks = ON;
1
2
3
4
5
6

# 10. 最佳实践总结

# 10.1 锁使用原则

  1. 最小化锁范围：只锁定必要的数据
  2. 最短事务时间：尽快提交事务
  3. 一致的锁顺序：所有事务按相同顺序获取锁
  4. 避免长事务：减少锁的持有时间

# 10.2 死锁预防措施

  1. 应用层重试机制：实现自动重试逻辑
  2. 合理的事务设计：避免复杂的跨表操作
  3. 索引优化：确保查询使用合适的索引
  4. 监控告警：建立完善的监控体系

# 10.3 错误处理策略

-- 事务错误处理示例
BEGIN;
-- 执行业务逻辑
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 1;

-- 检查是否有错误
IF @@ERROR_COUNT > 0 THEN
    ROLLBACK;
    -- 记录错误并处理
ELSE
    COMMIT;
END IF;
1
2
3
4
5
6
7
8
9
10
11
12
13

# 11. 总结

锁机制是数据库并发控制的核心，而死锁是并发编程中需要重点防范的问题。通过理解锁的工作原理、掌握死锁的检测和预防方法，可以有效避免系统出现死锁问题，提高系统的稳定性和可用性。

在实际应用中，应该遵循合理的事务设计原则，建立完善的监控体系，并在应用层面实现重试机制，从而最大程度地减少死锁对系统的影响。同时，持续优化数据库设计和查询语句，也是预防死锁的有效手段。

#MySQL#锁机制#死锁#事务处理#学习笔记
上次更新: 3/4/2026

← 数据误删恢复→

最近更新
01
表空间管理与回收
03-04
02
MySQL抖动刷脏页
03-04
03
count函数详解
03-04
更多文章>
Theme by Vdoing