MySQL 锁引发的问题

开始

背景

业务：从亚马逊获取用户的数据报表(如库存信息)更新至自己的数据库，由于项目是接手过来的，而且表数据量非常大故并没有对表重构，主要是对代码进行重构

库存库表除了主键索引还有以用户、sku作普通索引(大隐患)

存在bug的思路

• 数据库建立索引
• 获取每个产品的条目明细
• SKU存在多条数据则删除多余数据再更新
• SKU存在一条则更新

A同志之所以这样操作想着是删除再插入对于更新，后者效率更高。

问题

Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

由异常可知：锁等待超时

分析

8.0+,查看数据隔离级别:

select @@transaction_isolation;
REPEATABLE-READ

手动测试，表设计：

CREATE TABLE `aws_xxx`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sku` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'sku',
  `title` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_sku`(`sku`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 990145 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'asin表' ROW_FORMAT = DYNAMIC;

在客户端1执行：

BEGIN;
delete from aws_xxx where sku < 50 and sku>=40;

事务未提交。

在客户端2执行:

UPDATE aws_xxx SET title='456' WHERE sku=50

发生了堵塞。

查看是否有锁冲突:

select * from information_schema.INNODB_TRX;

查看锁情况:

select * from sys.innodb_lock_waits
2022-07-08 15:18:18 00:00:02    2   `account-office`.`aws_xxx`  account-office  aws_xxx         PRIMARY RECORD  14808740    2022-07-08 15:18:18 00:00:02    2   0   626458  UPDATE aws_xxx SET title='789' WHERE sku='50'   140679742227008:3767:4:52:140679640616176   X,REC_NOT_GAP   14808735    626429      140679742226136:3767:4:52:140679640609928   X   2022-07-08 15:17:48 00:00:32    101 9   KILL QUERY 626429   KILL 626429

locked index:PRIMARY 表示锁在主键索引

locked type:RECORD 行锁

locked mode:X 排他锁

可以知道客户端2的确被堵塞。

原因

在可重复读级别下，sku>40的规则是往后遍历知道不满足条件sku=50时停止，即同样会锁住sku=50。

自然上述会发生锁竞争直到update语句锁超时。

解决

• 修改数据为读提交(不切实际)

• 修改执行数据库代码

  • 不进行更新操作
  • 如有重复sku则全部删除
  • 再插入新数据