间隙锁死锁问题的处理

464

问题描述

  1. 事务超时

由于insert 语句导致事务等待超时, 后续回滚失败。

  1. 死锁

这两个问题都指向了同一条SQL, 那么而且死锁属于事务超时,因此可以看成同一问题。

此处的死锁信息是从DAS平台获取, 有兴趣的可以向运维申请权限进行查看 阿里云登录 - 欢迎登录阿里云,安全稳定的云计算服务平台

问题梳理

基础环境

  1. 表信息
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='自定义字段的存储表';
  1. 索引信息

主键索引:id

二级索引:indx_core_id('core_id')

  1. 隔离级别

Java代码:默认

MySQL:read commit

MyCat: repeatable read

所以最终隔离级别是repeatable read

image.png

业务梳理

//删除原有的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());

}

image.png

  1. remove旧数据

这里有个坑,第一次update 数据时,肯定update不了, 因为压根就没有符合条件的值(core_id = materialId), 因为是首次回调。

material_customer_attr中关于coreId是否唯一的分布如下,可见大约99.6%的数据coreId都是唯一的,所以大部分回调请求 remove操作都不会成功。

数据类型条数
coreId唯一的记录75475
coreId有重复的记录292
  1. insert数据

insert如果拿不到对应的锁,就会发生生产上的那个事务等待超时问题。

问题数据

随机抽取了5条sentry中的问题数据。发现了他们的共性:

  1. 数据不存在
  2. 存在于一个大间隙中
tenantIdcore_id状态
t2718072无此数据
t2718078无此数据
t2718073无此数据
t2718068无此数据

8072附近的存量数据为 [8055 , 8084]

问题判断

由以上信息可以初步猜测是间隙锁问题。

但是,但是,数据库的隔离级别是read commit,不会产生间隙锁!!!!

这个问题后来就搁置了, 因为思路断了。后来摇摇说Mycat中间件的隔离级别是repeatable read,就能顺下来了。

死锁日志分析

事务 1事务 2(已回滚)
Session ID337673085337673090
Thread id702927707418
请求类型updateupdate
事务ID17627338381762733835
涉及表t332.material_customer_attrt332.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 waitingindex idx_core_id of table t332.material_customer_attr trx id 1762733835 lock_mode X locks gap before rec insert intention waiting
等待锁索引名idx_core_ididx_core_id
等待锁类型X locks gap before rec insert intention waitingX 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
事务SQLINSERT 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 )

image.png

core_id 在(49660,49670) 之前存在间隙

session1session2备注
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)空隙

session1session2备注
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;

解决方案

image.png

  1. 先查询,再根据id删除
  • 减少了update语句使用,避免了间隙锁的产生
  • 使用id进行删除, 使用主键索引作为条件,尽量避免间隙锁的产生
  1. 减少长事务

将ES更新操作从事务中移出,避免长事务的使用