MYSQL死锁问题如何排查

场景描述：

mysql数据库死锁是比较常见的问题，我们通过下面的场景复现一下死锁问题。 假设我们的商城系统里包含这2个表：

products -- 产品表

orders -- 订单表

步骤一：复现死锁

创建表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    stock INT
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    quantity INT
);

插入初始数据
INSERT INTO products VALUES (1, 'Product A', 100);
INSERT INTO products VALUES (2, 'Product B', 200);
INSERT INTO orders VALUES (1, 1, 5);
INSERT INTO orders VALUES (2, 2, 10);

我们模拟2个表的并发事务 事务1：

  START TRANSACTION;
  UPDATE products SET stock = stock - 5 WHERE id = 1;
  -- 模拟延迟
  DO SLEEP(2);
  UPDATE orders SET quantity = quantity + 5 WHERE id = 1;
  COMMIT;

事务2:

  START TRANSACTION;
  UPDATE orders SET quantity = quantity - 5 WHERE id = 1;
  -- 模拟延迟
  DO SLEEP(2);
  UPDATE products SET stock = stock + 5 WHERE id = 1;
  COMMIT;


◦	明确需先察觉死锁情况，是排查起点

步骤二：识别死锁

死锁发生时，MySQL 会自动检测并回滚事务，识别方式有：

1.检查应用程序日志找类似"Deadlock found when trying to get lock" 的错误信息
2.用mysql 命令查看最近死锁信息 

SHOW ENGINE INNODB STATUS;

输出中，找到"LATEST DETECTED DEADLOCK" 部分。

步骤三：分析死锁

从 SHOW ENGINE INNODB STATUS 输出里查看相关信息来分析

  ------------------------
  LATEST DETECTED DEADLOCK
  ------------------------
  *** (1) TRANSACTION:
  TRANSACTION 8-131, ACTIVE 6 sec starting index read
  mysql tables in use 1, locked 1
  LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
  MySQL thread id 8, OS thread
  UPDATE products SET stock = stock - 5 WHERE id = 1

 *** (2) TRANSACTION:
 TRANSACTION 8-132, ACTIVE 4 sec starting index read
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1136, 2 row lock(s)
 MySQL thread id 9, OS thread handle 14
 UPDATE orders SET quantity = quantity - 5 WHERE id = 1

18 *** WE ROLL BACK TRANSACTION (1)

这个输出告诉我们：

• 事务1正在更新products对象
• 事务2正在更新orders对象
• MySQL选择回滚事务1解决死锁

步骤四：解决死锁

1.保持一致访问顺序

修改应用程序代码，确保所有事务按照相同顺序访问表；

2.减小事务范围

尽可能缩小事务范围，减少持有锁时间；

3.使用乐观锁

对于某些表，可以使用版本号来实现乐观锁

ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
-- 更新时检查版本号
UPDATE products SET stock=stock - 5 ,version=version+1 where id=1 and version=0;

步骤五：监控和预防

①设置死锁监控

SET GLOBAL innodb_print_all_deadlocks = 1

这个命令将所有的死锁信息记录到错误日志中；

②定期检查死锁情况

SELECT * FROM information_schema.INNODB_TRX;

这个命令可以检查当前正在进行的事务；

③使用性能模式监控锁等待

select * from performance_schema.events_waits_current where event_name like 'wait/synch/mutex/innodb%' ;