问题描述
-
事务超时
由于insert 语句导致事务等待超时, 后续回滚失败。
-
死锁
这两个问题都指向了同一条SQL, 那么而且死锁属于事务超时,因此可以看成同一问题。
此处的死锁信息是从DAS平台获取, 有兴趣的可以向运维申请权限进行查看 阿里云登录 - 欢迎登录阿里云,安全稳定的云计算服务平台
问题梳理
基础环境
- 表信息
CREATE TABLE `material_customer_attr` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`core_id` bigint(20) DEFAULT NULL COMMENT 'coreId',
`attr_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'attrCode',
`attr_value` mediumtext COLLATE utf8mb4_unicode_ci COMMENT 'attrValue',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`create_user` bigint(20) DEFAULT NULL COMMENT '创建人',
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`update_user` bigint(20) DEFAULT NULL COMMENT '更新人',
`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除(0:未删除,1:已删除)',
PRIMARY KEY (`id`),
KEY `indx_core_id` (`core_id`)
) ENGINE=InnoDB AUTO_INCREMENT=639216 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='自定义字段的存储表';
- 索引信息
主键索引:id
二级索引:indx_core_id('core_id')
- 隔离级别
Java代码:默认
MySQL:read commit
MyCat: repeatable read
所以最终隔离级别是repeatable read
业务梳理
//删除原有的customerAttr
materialCustomerAttrService.remove(new QueryWrapper<MaterialCustomerAttrPO>().lambda()
.eq(MaterialCustomerAttrPO::getCoreId, materialId)
.eq(MaterialCustomerAttrPO::getAttrCode, IMAGE_COLOR_CODE));
StringBuilder stringBuilder = new StringBuilder();
colorList.forEach(c -> {
stringBuilder.append(c).append(",");
});
if (stringBuilder.length() > 0) {
stringBuilder.deleteCharAt(stringBuilder.length() - 1);
}
//更新es以及入库
MaterialCustomerAttrPO materialCustomerAttrPO = new MaterialCustomerAttrPO();
materialCustomerAttrPO.setAttrCode(IMAGE_COLOR_CODE);
materialCustomerAttrPO.setCoreId(materialId);
materialCustomerAttrPO.setStatus(0);
materialCustomerAttrPO.setAttrValue(stringBuilder.toString());
materialCustomerAttrPO.setCreateUser(1L);
materialCustomerAttrPO.setCreateTime(new Date());
materialCustomerAttrService.save(materialCustomerAttrPO);
try {
materialSearchService.updateDamDocument(Lists.newArrayList(materialId));
} catch (JsonProcessingException e) {
log.error("<======> 新图片色值回调,同步es失败,失败原因:{}", e.getMessage());
}
- remove旧数据
这里有个坑,第一次update 数据时,肯定update不了, 因为压根就没有符合条件的值(core_id = materialId), 因为是首次回调。
material_customer_attr中关于coreId是否唯一的分布如下,可见大约99.6%的数据coreId都是唯一的,所以大部分回调请求 remove操作都不会成功。
数据类型 | 条数 |
---|---|
coreId唯一的记录 | 75475 |
coreId有重复的记录 | 292 |
- insert数据
insert如果拿不到对应的锁,就会发生生产上的那个事务等待超时问题。
问题数据
随机抽取了5条sentry中的问题数据。发现了他们的共性:
- 数据不存在
- 存在于一个大间隙中
tenantId | core_id | 状态 |
---|---|---|
t271 | 8072 | 无此数据 |
t271 | 8078 | 无此数据 |
t271 | 8073 | 无此数据 |
t271 | 8068 | 无此数据 |
8072附近的存量数据为 [8055 , 8084]
问题判断
由以上信息可以初步猜测是间隙锁问题。
但是,但是,数据库的隔离级别是read commit,不会产生间隙锁!!!!
这个问题后来就搁置了, 因为思路断了。后来摇摇说Mycat中间件的隔离级别是repeatable read,就能顺下来了。
死锁日志分析
事务 1 | 事务 2(已回滚) | |
---|---|---|
Session ID | 337673085 | 337673090 |
Thread id | 702927 | 707418 |
请求类型 | update | update |
事务ID | 1762733838 | 1762733835 |
涉及表 | t332 .material_customer_attr | t332 .material_customer_attr |
等待锁 | index idx_core_id of table t332 .material_customer_attr trx id 1762733838 lock_mode X locks gap before rec insert intention waiting | index idx_core_id of table t332 .material_customer_attr trx id 1762733835 lock_mode X locks gap before rec insert intention waiting |
等待锁索引名 | idx_core_id | idx_core_id |
等待锁类型 | X locks gap before rec insert intention waiting | X locks gap before rec insert intention waiting |
持有锁 | index idx_core_id of table t332 .material_customer_attr trx id 1762733835 lock_mode X locks gap before rec | |
持有锁索引名 | idx_core_id | |
持有锁类型 | X locks gap before rec | |
事务SQL | INSERT INTO material_customer_attr ( core_id, attr_code, attr_value, create_time, create_user, status ) VALUES ( 49666, 'CUSTOMER_IMAGE_COLOR', '#F1E0C2,#9DD20C,#E1BA62,#FF3F3E,#FFFFFF,#332713,#BDAD8E,#DD9433,#E1E185,#82A006', '2022-04-27 09:05:54.962', 1, 0 ) | INSERT INTO material_customer_attr ( core_id, attr_code, attr_value, create_time, create_user, status ) VALUES ( 49664, 'CUSTOMER_IMAGE_COLOR', '#DAD1CB,#ADA39A,#FFFFFF,#F8E9D6,#9C7564,#3B3029,#FEBC43,#F3505D,#6C4F41', '2022-04-27 09:05:54.962', 1, 0 ) |
core_id 在(49660,49670) 之前存在间隙
session1 | session2 | 备注 |
---|---|---|
set session transaction isolation level REPEATABLE READ; | ||
BEGIN;UPDATE material_customer_attr set status = 1 where core_id = 49666; | session1在(49660,49670)上加间隙锁 | |
set session transaction isolation level REPEATABLE READ; | ||
BEGIN;UPDATE material_customer_attr set status = 1 where core_id = 49664; | session1在(49660,49670)上加间隙锁 | |
INSERT INTO t2 .material_customer_attr ( core_id , attr_code , attr_value , create_time , create_user , update_time , update_user , status ) VALUES ( 49666, 'CUSTOMER_IMAGE_COLOR', '#33524F,#637475,#CAD4DA,#92A19E,#262B2B,#5E918C', '2022-04-14 22:22:15', 1, NULL, NULL, 1); | 需要获得插入意向锁,与session2持有锁互斥session1阻塞 | |
INSERT INTO t2 .material_customer_attr ( core_id , attr_code , attr_value , create_time , create_user , update_time , update_user , status ) VALUES ( 3, 'CUSTOMER_IMAGE_COLOR', '#33524F,#637475,#CAD4DA,#92A19E,#262B2B,#5E918C', '2022-04-14 22:22:15', 1, NULL, NULL, 1); | 需要获得插入意向锁,与session1持有锁互斥构成互斥条件,发生死锁。回滚 |
事务超时复现(lock wait timeout)
同理 事务超时也可以复现。
预留core_id (1,7)空隙
session1 | session2 | 备注 |
---|---|---|
set session transaction isolation level REPEATABLE READ; | ||
BEGIN;UPDATE material_customer_attr set status = 1 where core_id = 2; | session1 获得(1,7) 间隙锁 | |
INSERT INTO t2 .material_customer_attr ( core_id , attr_code , attr_value , create_time , create_user , update_time , update_user , status ) VALUES ( 2, 'CUSTOMER_IMAGE_COLOR', '#33524F,#637475,#CAD4DA,#92A19E,#262B2B,#5E918C', '2022-04-14 22:22:15', 1, NULL, NULL, 1); | session1 获得 插入意向锁 | |
set session transaction isolation level REPEATABLE READ; | ||
BEGIN;UPDATE material_customer_attr set status = 1 where core_id = 3; | session2 获得间隙锁 | |
INSERT INTO t2 .material_customer_attr ( core_id , attr_code , attr_value , create_time , create_user , update_time , update_user , status ) VALUES ( 3, 'CUSTOMER_IMAGE_COLOR', '#33524F,#637475,#CAD4DA,#92A19E,#262B2B,#5E918C', '2022-04-14 22:22:15', 1, NULL, NULL, 1); | session2等待 插入意向锁。无法获得---Lock wait timeout exceeded; |
解决方案
- 先查询,再根据id删除
- 减少了update语句使用,避免了间隙锁的产生
- 使用id进行删除, 使用主键索引作为条件,尽量避免间隙锁的产生
- 减少长事务
将ES更新操作从事务中移出,避免长事务的使用