灿的博客

知识管理、自我管理、印象笔记、工具收录

记线上mysql死锁问题:“如何避免死锁”

 分类  

回忆之前参与过的一个项目，在项目的初期，我们是基于主库完成读写操作，并没有做读写分离。偶尔收到系统死锁异常报警信息。 经过日志分析，主要是作为幂等性校验的一张表出现死锁异常，初步怀疑是索引导致的死锁问题。 发生死锁的表结构及索引情况:

CREATE TABLE `payment_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payment_sn` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '支付流水',
  `order_id` bigint(20) NOT NULL COMMENT '订单表id',
  `member_id` bigint(20) NOT NULL COMMENT '交易的用户ID',
  `amount` decimal(8,2) NOT NULL COMMENT '交易金额',
  `pay_channel` tinyint(4) NOT NULL COMMENT '支付类型 0:余额 1:微信 2:支付宝',
  `source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '支付来源 wx app web',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '支付状态 -1：取消 0 未完成 1已完成 -2:异常',
  `completion_time` datetime(0) DEFAULT NULL COMMENT '交易完成时间',
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `created_at` datetime(0) DEFAULT NULL,
  `updated_at` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_payment_status`(`order_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

首先，创建一张支付记录表，主要用于校验订单重复支付。 为了能重现该问题，我们先关闭自动提交事务提交。(MySQL 数据库和 Oracle 提交事务不太一样，MySQL 数据库默认情况下是自动提交事务) 以下命令行用于查看自动提交事务是否开启：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

接下来关闭自动提交事务:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

在做幂等性校验时，通过订单号检验是否已支付，如未支付则新增支付记录。 开启两个终端模拟事务并发情况，执行顺序以及实验现象如下:

时间编号 事务A 事务B
① begin; begin;
② select * from payment_record where order_id = 1 for update;
③ select * from payment_record where order_id = 2 for update;
④ insert into payment_record(payment_sn, order_id, member_id, amount, pay_channel, source, status, note, completion_time, created_at, updated_at)values(“202103282139136303820751694”, 1, 1, 50, 1, “wx”, 1, “”, “2021-03-28 10:57:03”, “2021-03-28 10:57:03”, “2021-03-28 10:57:03”);
⑤ 阻塞等待
⑥ insert into payment_record(payment_sn, order_id, member_id, amount, pay_channel, source, status, note, completion_time, created_at, updated_at)values(“202103282139136303820751694”, 2, 2, 50, 1, “wx”, 1, “”, “2021-03-28 10:57:03”, “2021-03-28 10:57:03”, “2021-03-28 10:57:03”);
⑦ 插入成功
⑧ commit; commit;

可以看到事务A执行插入操作，陷入阻塞

mysql> insert into `payment_record`(`payment_sn`, `order_id`, `member_id`, `amount`, `pay_channel`, `source`, `status`, `note`, `completion_time`, `created_at`, `updated_at`)values("202103282139136303820751694", 1, 1, 50, 1, "wx", 1, "", "2021-03-28 10:57:03", "2021-03-28 10:57:03", "2021-03-28 10:57:03");
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这时候可以用select * from information_schema.innodb_locks;查看锁情况：

mysql> select * from information_schema.innodb_locks;
+------------------+-------------+-----------+-----------+-------------------------+--------------------+------------+-----------+----------+------------------------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table              | lock_index         | lock_space | lock_page | lock_rec | lock_data              |
+------------------+-------------+-----------+-----------+-------------------------+--------------------+------------+-----------+----------+------------------------+
| 777832:12921:4:1 | 777832      | X         | RECORD    | `demo`.`payment_record` | idx_payment_status |      12921 |         4 |        1 | supremum pseudo-record |
| 777833:12921:4:1 | 777833      | X         | RECORD    | `demo`.`payment_record` | idx_payment_status |      12921 |         4 |        1 | supremum pseudo-record |
+------------------+-------------+-----------+-----------+-------------------------+--------------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

问个问题，看到这里，你是否疑惑，为什么 SELECT 要加 for update 排他锁，而不是使用共享锁呢？

假想一下，因为网络原因，用户重复多次点击支付，多个订单号一样的请求同时进来，就可能出现幻读。一开始事务A中的查询没有该订单号的支付记录，后来事务B新增了一个该订单号的支付记录，此时事务A再新增一条该订单号的支付记录，就会创建重复的支付记录。针对这情况，可以使用锁间隙算法来防止幻读。

分享