线上mysql的update语句获取锁超时情况分析以及复盘

线上mysql的update语句获取锁超时情况分析以及复盘

现象

多租户项目生产环境下，需要进行数据修复，执行一条update语句。在执行过程中一直报获取锁超时，无法执行

初步分析

有其他事务，将需要修改的数据上锁，导致update获取锁超时。那么开始分析该租户是否有需要执行时间很长的事务。

发现另一个租户，正在异步的执行大方法，该方法有一个很大的事务。

初步分析结果：一个大的事务，将需要改的数据上了锁，一直没有释放，导致无法执行。

深入分析

为什么我update一个租户的数据时，会去获取另一个租户的事务在处理数据的锁？

需要分析一下该update语句的获取锁，以及加锁机制。

首先tenant_id该字段上有非唯一键的二级索引，这种索引不是主键的聚簇索引，那么该查询该索引时，最后得到的是主键的id，之后再去主键的聚簇索引中查找数据。

那么对于tenant_id二级非唯一索引进行update时，会对其满足条件的索引加锁（X锁，独占锁）。并且在RR（Read Repeat 可重复读）隔离等级下，对其所相邻的范围上锁（Gap 间隙锁），对查询到的聚簇加X锁。

那么在update使用租户索引时，相当于锁住了一个范围，形成了不通租户间的干扰。

现象模拟

创建一个有唯一键，有租户索引的表

首先模拟开启test租户的大事务，阻塞不提交

-- 模拟大事务在插入数据
START TRANSACTION;

INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107120012', '111', 'test');

-- 在这里先不提交，模拟阻塞

模拟其master租户正常提交数据

-- 模拟其他租户正常插入数据
START TRANSACTION;

INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107120013', '111', 'master');

COMMIT;

模拟master租户update

-- 模拟其他租户的update
START TRANSACTION;

UPDATE test_content SET content = '666' WHERE tenant_id = 'master';

ROLLBACK;

产生获取锁超时

附测试sql脚本

-- 建表
CREATE TABLE `test_content` (
  `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tenant_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tenant_key` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 模拟数据
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090001', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090002', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090003', '111', 'master');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090004', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090005', '111', 'master');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090006', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090009', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090017', '111', 'test');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107090029', '111', 'master');
INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107120011', '111', 'master');

-- 模拟大事务在插入数据
START TRANSACTION;

INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107120012', '111', 'test');

-- 在这里先不提交，模拟阻塞
COMMIT;

-- 模拟其他租户正常插入数据
START TRANSACTION;

INSERT INTO `demo`.`test_content`(`id`, `content`, `tenant_id`) VALUES ('202107120013', '111', 'master');

COMMIT;

-- 模拟其他租户的update
START TRANSACTION;

UPDATE test_content SET content = '666' WHERE tenant_id = 'master';

ROLLBACK;

问题复盘

1. 该问题的直接产生原有就是开启大事务不提交，导致update语句获取锁超时。
2. update使用非唯一键索引，需要获取修改数据id间的gap锁，这之间又有其他租户的数据，所以看起来明明使用了租户索引，却产生了租户间的干涉。
3. 租户索引的数据分布不当，如果租户索引对应的数据范围能够没有交叉，那么就不会产生干扰。

修复方案

1. 对大事务进行优化，分批次提交数据。
2. 对id的生成策略进行重新规划，如：使用租户id作为id的前缀（0001 + yyyyMMdd + 自定义策略）

后续

在执行update语句时，使用的是非唯一的间接索引时，产生的锁影响范围广，并且当sql写的有问题时，update语句没有命中索引时，会锁表。后续在执行update语句时，尽量使用唯一键索引去更新，尽量减少影响范围。

在多租户表设计时，应当考虑各个租户的数据分布，思考数据库使用的存储引擎对数据的影响，指定合适的多租户数据存储方案。