灿的博客

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

记线上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再新增一条该订单号的支付记录,就会创建重复的支付记录。针对这情况,可以使用锁间隙算法来防止幻读。


分享