MySQL InnoDB锁机制实战：行锁、间隙锁与死锁排查方案

MySQL InnoDB存储引擎的锁机制是保证并发数据一致性的基础，但不当的索引设计和事务控制会导致死锁和性能退化。理解行锁、间隙锁、临键锁的触发条件，掌握死锁分析和排查方法，是数据库运维和MySQL性能调优的必备技能。本文通过实际案例演示锁机制的运作原理和排查流程。

InnoDB锁类型与加锁规则

InnoDB在RR（可重复读）隔离级别下使用临键锁防止幻读。临键锁是行锁和间隙锁的组合，锁定一个左开右闭的索引区间。不同查询条件的加锁规则不同：

行锁与间隙锁实战演示

创建测试表并插入数据，观察不同场景下的加锁行为：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    status VARCHAR(20),
    INDEX idx_user (user_id),
    UNIQUE INDEX uk_order_no (order_no)
);

INSERT INTO orders VALUES
(1, 'ORD001', 100, 99.00, 'PAID'),
(5, 'ORD005', 100, 199.00, 'PAID'),
(10, 'ORD010', 200, 50.00, 'PENDING'),
(15, 'ORD015', 200, 300.00, 'PAID'),
(20, 'ORD020', 300, 150.00, 'PAID');

场景1：等值查询命中唯一索引，加行锁

-- 事务A
BEGIN;
SELECT * FROM orders WHERE order_no = 'ORD005' FOR UPDATE;
-- 锁定id=5的行

-- 事务B尝试修改其他行，成功
UPDATE orders SET amount = 100 WHERE id = 1;
-- 事务B尝试修改被锁行，阻塞
UPDATE orders SET amount = 200 WHERE id = 5;
-- ERROR 1205: Lock wait timeout exceeded

场景2：等值查询未命中，加间隙锁

-- 事务A（user_id=100对应的id是1和5，查询id=3不存在）
BEGIN;
SELECT * FROM orders WHERE user_id = 100 AND id = 3 FOR UPDATE;
-- 在id=1和id=5之间加间隙锁，锁定(1, 5)

-- 事务B尝试在间隙内插入，阻塞
INSERT INTO orders VALUES (3, 'ORD003', 100, 60.00, 'PAID');
-- ERROR 1205: Lock wait timeout exceeded

-- 事务B尝试插入间隙外的数据，成功
INSERT INTO orders VALUES (8, 'ORD008', 100, 80.00, 'PAID');

场景3：范围查询加临键锁

-- 事务A
BEGIN;
SELECT * FROM orders WHERE id >= 10 AND id < 15 FOR UPDATE;
-- 锁定(5, 10], (10, 15), [15]即临键锁覆盖
-- 实际锁范围：(5, 15]

-- 事务B尝试在范围内插入，阻塞
INSERT INTO orders VALUES (12, 'ORD012', 250, 70.00, 'PAID');
-- 事务B尝试修改范围内已有行，阻塞
UPDATE orders SET status = 'PAID' WHERE id = 10;

死锁场景分析与排查

死锁在并发更新同一批数据但顺序不同时频繁出现。模拟典型死锁场景：

-- 事务A：先锁id=1，再锁id=5
BEGIN;
UPDATE orders SET amount = 100 WHERE id = 1;
-- 事务A持有id=1的行锁

-- 事务B：先锁id=5，再锁id=1
BEGIN;
UPDATE orders SET amount = 200 WHERE id = 5;
-- 事务B持有id=5的行锁

-- 事务A继续：尝试锁id=5，等待事务B释放
UPDATE orders SET amount = 150 WHERE id = 5;
-- 阻塞...

-- 事务B继续：尝试锁id=1，等待事务A释放
UPDATE orders SET amount = 250 WHERE id = 1;
-- 死锁触发，InnoDB自动检测并回滚一个事务
-- ERROR 1213: Deadlock found when trying to get lock

InnoDB自动检测死锁并回滚开销较小的事务。通过SHOW ENGINE INNODB STATUS查看最近一次死锁详情：

SHOW ENGINE INNODB STATUS\G

-- 输出中的LATEST DETECTED DEADLOCK段：
-- *** (1) TRANSACTION:
-- TRANSACTION 12345, ACTIVE 3 sec starting index read
-- mysql tables in use 1, locked 1
-- LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
-- *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- RECORD LOCKS space id 50 page no 3 n bits 72 index PRIMARY of table orders
-- trx id 12345 lock_mode X locks rec but not gap waiting
-- Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; ...
--
-- *** (2) TRANSACTION:
-- TRANSACTION 12346, ACTIVE 2 sec starting index read
-- mysql tables in use 1, locked 1
-- 3 lock struct(s), heap size 1136, 2 row lock(s)
-- *** (2) HOLDS THE LOCK(S):
-- RECORD LOCKS ... index PRIMARY of table orders
-- trx id 12346 lock_mode X locks rec but not gap
-- *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
-- ... lock_mode X locks rec but not gap waiting
--
-- *** WE ROLL BACK TRANSACTION (2)

日志中的关键信息：两个事务的加锁顺序、锁类型（X表示排他锁）、锁模式（locks rec but not gap表示行锁，locks gap before rec表示间隙锁）、等待锁的具体记录。通过分析锁顺序可确定死锁根因。

死锁预防与优化

死锁的根本原因是加锁顺序不一致。以下措施可大幅降低死锁概率：

// 1. 统一加锁顺序：按主键升序批量更新
public void batchUpdateOrders(List<Long> ids, BigDecimal amount) {
    // 排序确保加锁顺序一致
    ids.sort(Long::compare);
    for (Long id : ids) {
        jdbcTemplate.update(
            "UPDATE orders SET amount = ? WHERE id = ?", amount, id);
    }
}

// 2. 缩短事务时间：大事务拆分为小事务
@Transactional
public void processOrder(Long orderId) {
    // 快速更新核心字段
    orderMapper.updateStatus(orderId, "PROCESSING");
}

// 3. 使用乐观锁替代悲观锁
public boolean updateWithOptimisticLock(Long id, int expectedVersion) {
    int rows = jdbcTemplate.update(
        "UPDATE orders SET status = 'PAID', version = version + 1 " +
        "WHERE id = ? AND version = ?",
        id, expectedVersion);
    return rows > 0;
}

开启死锁监控将死锁事件记录到单独日志：

-- my.cnf配置
[mysqld]
innodb_print_all_deadlocks = ON
innodb_lock_wait_timeout = 10  -- 锁等待超时缩短至10秒

-- 死锁信息写入error log
-- 查看位置：SHOW VARIABLES LIKE 'log_error';

生产环境应建立死锁监控告警，当死锁频率超过阈值时触发排查。通过information_schema.INNODB_TRX和INNODB_LOCK_WAITS视图实时监控锁等待，配合慢查询日志定位导致锁等待的SQL语句。SQL查询优化中，索引优化是减少锁范围最有效的手段，确保所有更新查询都走索引，避免全表扫描导致的锁升级。数据备份恢复方案设计时也需考虑锁等待超时对备份窗口的影响。