逻辑删除与唯一索引冲突使用NULL

转载

mob64ca141275de 2024-09-13 12:35:30

文章标签 逻辑删除与唯一索引冲突使用NULL mysql 数据 bc 死锁 文章分类 数据仓库大数据

说明：本文中所有叙述均基于MySQL 5.6版本，Innodb引擎，数据库隔离级别为可重复读

场景复现

在项目提交测试过程中，测试发现会偶现接口调用失败，后台日志显示数据库死锁。

经过日志错误分析，定位到如下的功能模块，此功能模块场景描述：

操作事务型的数据库保存操作，涉及数据库多个表的操作，其中包括表configuration_base_dynamic（表结构如下所示），该表主要功能是保存单据的扩展信息，为了能支持扩展性，所以其采取单据ID+扩展Code+扩展信息Value的方式存储，在以后需求扩展时，无需新增表字段，直接横向扩展即可。

CREATE TABLE `configuration_base_dynamic` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `requirement_id` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '需求单ID',
  `type` INT(11) NOT NULL DEFAULT '0' COMMENT '不同配置数据区分列：1:目的地,2:品类',
  `code` INT(11) DEFAULT NULL COMMENT 'code',
  `value` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'code对应的值',
  `is_valid` TINYINT(1) NOT NULL DEFAULT '1' COMMENT '1:有效 0:无效',
  PRIMARY KEY (`id`),
  KEY `index_requirement_id` (`requirement_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='动态基础信息配置表';
 
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000020','1','1602','南京','1');
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000030','1','200','北京','1');
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000030','1','1602','南京','1');
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000036','1','200','北京','1');

每次执行上述保存操作过程中，会先将原来的值逻辑删除即使用update configuration_base_dynamic set is_valid=0 where requirement_id=xx 的方式，然后再insert 新的数据

/**
 * 重点关注 出现问题的方法
 * @param requirementInfo
 */
@Override
@Transactional(rollbackFor = Exception.class)
public void saveRequirement(RequirementInfo requirementInfo)  {
    // 实际代码中有很多较多操作 简化期间 这里用一个耗时代替
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
 
    RequirementBase base = requirementInfo.getBase();
    log.info("开始删除单号{} DESTINATION数据",base.getRequirementId());
    /**
     *  执行逻辑删除 将相应数据设为is_valid=0
     *  update configuration_base_dynamic
     *  set is_valid = 0
     *  where requirement_id = #{requirementId} and type = #{type} and is_valid=1
     */
    configurationBaseDynamicDao.delete(base.getRequirementId(), BaseDynamicTypeEnum.DESTINATION);
    log.info("完成删除单号{} DESTINATION数据",base.getRequirementId());
 
    List<Type> types = JsonUtil.toList(base.getDestinationInfo(), Type.class);
    // 将type转换为ConfigurationBaseDynamic对象格式
    List<ConfigurationBaseDynamic> configurationBaseDynamics = getConfigurationBaseDynamics(base, types);
 
    log.info("开始插入单号{} DESTINATION数据",base.getRequirementId());
    /**
     * 执行插入数据的操作
     */
    configurationBaseDynamicDao.insertBatch(configurationBaseDynamics);
    System.out.println("xxx");
    log.info("完成插入单号{} DESTINATION数据",base.getRequirementId());
 
    // 实际代码中有很多较多操作 简化期间 这里用一个耗时代替
    try {
        Thread.sleep(1000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
}

模拟多个线程并发调用如上代码重现报错

/**
 * 启动两个线程 模拟并发操作
 * @throws Exception
 */
@Test
public void saveRequirement() throws Exception{
    Thread t1 = new Thread(new Runnable() {
        @Override
        public void run() {
            saveReq(3000000);
        }
    });
    Thread t2 = new Thread(new Runnable() {
        @Override
        public void run() {
            saveReq(4000000);
        }
    });
    t1.start();
    t2.start();
 
    t1.join();
    t2.join();
    Thread.sleep(2000);
}
 
public void saveReq(Integer requirementId) {
    RequirementInfo requirementInfo = new RequirementInfo();
    RequirementBase base = new RequirementBase();
    base.setRequirementId(requirementId);
    base.setDestinationInfo("[{\"key\":1,\"value\":2},{\"key\":3,\"value\":4}]");
    requirementInfo.setBase(base);
    requirementServiceNew.saveRequirement(requirementInfo);
}

执行后日志：

2019-08-23 14:22:39.522 [Thread-18] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 开始删除单号3000000 DESTINATION数据  
2019-08-23 14:22:39.522 [Thread-19] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 开始删除单号4000000 DESTINATION数据  
2019-08-23 14:22:39.613 [Thread-19] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 完成删除单号4000000 DESTINATION数据  
2019-08-23 14:22:39.613 [Thread-18] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 完成删除单号3000000 DESTINATION数据  
2019-08-23 14:22:39.619 [Thread-19] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 开始插入单号4000000 DESTINATION数据  
2019-08-23 14:22:39.622 [Thread-18] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 开始插入单号3000000 DESTINATION数据  
2019-08-23 14:22:39.647 [Thread-18] INFO  c.t.c.r.r.i.RequirementServiceImplNew - 完成插入单号3000000 DESTINATION数据  
Exception in thread "Thread-19" org.springframework.dao.DeadlockLoserDataAccessException:   
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction  
### The error may involve com.ttu.crm.req.domain.req.mapper.ConfigurationBaseDynamicMapper.insertSelective-Inline  
### The error occurred while setting parameters  
### SQL: insert into configuration_base_dynamic      ( requirement_id,                       type,                       code,                       value )       values ( ?,                       ?,                       ?,                       ? )  
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction  
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 
Deadlock found when trying to get lock; try restarting transaction  
 …..篇幅需要，省略部分堆栈信息
 at com.ttu.crm.req.dao.impl.ConfigurationBaseDynamicDaoImpl.insert(ConfigurationBaseDynamicDaoImpl.java:32)  
 at com.ttu.crm.req.dao.impl.ConfigurationBaseDynamicDaoImpl.insertBatch(ConfigurationBaseDynamicDaoImpl.java:40)  
 at com.ttu.crm.req.requirement.impl.RequirementServiceImplNew.saveRequirement(RequirementServiceImplNew.java:59)  
….. 篇幅需要，省略部分堆栈信息
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction  
….. 篇幅需要，省略部分堆栈信息

死锁分析

根据上述报错场景来看，死锁是发生在删除数据后插入数据发生死锁，我们在Mysql中重现语句执行，使用Mysql的死锁检测分析详细死锁成因

时刻Time	事务A	事务B
T1	begin; update configuration_base_dynamic set is_valid = 0 where requirement_id = 3000000 and type = 1 and is_valid =1;	begin; update configuration_base_dynamic set is_valid = 0 where requirement_id = 4000000 and type = 1 and is_valid =1;
T2	insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values(‘3000000’,‘1’,‘200’,‘北京’,‘1’); 进入等待状态
T3		insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`)values(‘4000000’,‘1’,‘200’,‘北京’,‘1’); 报死锁
T4	插入成功

执行命令

set GLOBAL innodb_status_output_locks=ON;
show engine innodb status \G;

查看锁定信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-08-22 17:47:14 3234
*** (1) TRANSACTION:
TRANSACTION 19948, ACTIVE 67 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 13, OS thread handle 0x1a94, query id 276 localhost ::1 root upd
ate
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `val
ue`, `is_valid`) values('3000000','1','200','北京','1')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 4 n bits 80 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 19948 lock_mode X insert intent
ion waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 19949, ACTIVE 46 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 0x3234, query id 277 localhost ::1 root upd
ate
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `val
ue`, `is_valid`) values('4000000','1','200','????','1')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 131 page no 4 n bits 80 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 19949 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 4 n bits 80 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 19949 lock_mode X insert intent
ion waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

分析如上日志可以还原出死锁场景

1、T1执行update语句，并且requirement_id不存在且比现有表数据大，所以生成（1000036，正无穷] 的next-key锁锁住大于1000036的区间，此区间内其他事务不能插入数据

2、T2执行update语句，并且requirement_id不存在且比现有表数据大，所以生成（1000036，正无穷] 的next-key锁锁住大于1000036的区间，此区间内其他事务不能插入数据

3、T1插入3000000数据，需要等待T2释放（1000036，正无穷] 区间的锁，进入等待。T2插入4000000数据，需要等待T1释放（1000036，正无穷] 区间的锁，因此生成死锁，T2回滚释放锁，T1插入成功。

Mysql行锁及加锁说明

Mysql行锁及加锁说明
1、record lock: 记录锁, 按照索引维度加锁

2、gap lock: 间隙锁, 锁住记录之间的间隙,防止其他事务在此间隙插入数据

3、next-key lock: record lock+gap lock 为左开右闭区间

4、Insert Intention Locks：插入意向锁一个事务在插入一条记录时需要判断插入位置是否被别的事务加了gap锁, 如果存在gap锁，插入需要等待，等待的时候生成一个名为Insert Intention Locks的锁。

有了以上理论知识，我们看下上述执行update语句后数据库加锁情况：

begin;
update configuration_base_dynamic
 set is_valid = 0
where requirement_id = 3000000
  and type = 1
  and is_valid =1;
 
 
---TRANSACTION 20230, ACTIVE 275 sec
2 lock struct(s), heap size 360, 1 row lock(s)
--说明: 2种锁结构(IX表锁、next-key lock),共1个行锁
MySQL thread id 1, OS thread handle 0x2604, query id 15 localhost ::1 root init
show engine innodb status
TABLE LOCK table `tusiji`.`configuration_base_dynamic` trx id 20230 lock mode IX
RECORD LOCKS space id 131 page no 4 n bits 80 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 20230 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
--说明:行锁作用在索引index_requirement_id上，为next-key lock，锁定(1000036,supremum(可以理解为正无穷大的数字)],之后就很好理解了，另外一个事务想插入数据 就会被这个lock阻止。

说明:
2种锁结构(IX表锁、next-key lock),共1个行锁

行锁作用在索引index_requirement_id上，为next-key lock，锁定(1000036,supremum(可以理解为正无穷大的数字)],之后就很好理解了，另外一个事务想插入数据就会被这个lock阻止。

如何能够保护比现有表中现有的最大记录到正无穷之间的间隙，以及最小记录到负无穷之间的间隙，引入Supremum（代表正无穷大的记录）、Infimum(负无穷大的记录)

逻辑删除与唯一索引冲突使用NULL_mysql

进一步思考，如果更新的requirement_id不是比现有数据大，而是在现有数据范围内，那么是如何锁定的呢?

begin;
update configuration_base_dynamic
set is_valid = 0
where requirement_id = 1000030
  and type = 1
  and is_valid =1;
 
 
---TRANSACTION 20764, ACTIVE 10 sec
4 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 2  
--说明: 4种锁结构(IX表锁、next-key lock、record-lock、gap-lock),共5个行锁 
MySQL thread id 1, OS thread handle 0x1f34, query id 26 localhost ::1 root init
show engine innodb status
TABLE LOCK table `tusiji`.`configuration_base_dynamic` trx id 20764 lock mode IX
RECORD LOCKS space id 132 page no 4 n bits 72 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 20764 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 000f425e; asc   B^;;             --说明: f425e=1000030 对应requirement_id
 1: len 8; hex 0000000000000002; asc         ;; --说明:0000000000000002=2对应id
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 000f425e; asc   B^;;             --说明: f425e=1000030 对应requirement_id
 1: len 8; hex 0000000000000003; asc         ;; --说明: 0000000000000003=3对应id
--说明: next-key lock  锁住索引index_requirement_id范围 (1000020,1000030(id=2)] (1000030(id=2),1000030(id=3)]
 
 
RECORD LOCKS space id 132 page no 3 n bits 72 index `PRIMARY` of table `tusiji`.
`configuration_base_dynamic` trx id 20764 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 8; hex 0000000000000002; asc         ;;  --说明: 0000000000000002=2对应id
 1: len 6; hex 00000000511c; asc     Q ;;        --说明: 511c=20764对应trx_id
 2: len 7; hex 14000001c10802; asc        ;;
 3: len 4; hex 000f425e; asc   B^;;             --说明: f425e=1000030 对应requirement_id
 4: len 4; hex 80000001; asc     ;;             --说明: 1 对应type
 5: len 4; hex 800000c8; asc     ;;             --说明: c8=200 对应code
 6: len 6; hex e58c97e4baac; asc       ;;
 7: len 1; hex 80; asc  ;;                     
Record lock, heap no 4 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 8; hex 0000000000000003; asc         ;;  --说明: 0000000000000003=3对应id
 1: len 6; hex 00000000511c; asc     Q ;;
 2: len 7; hex 14000001c10825; asc       %;;
 3: len 4; hex 000f425e; asc   B^;;              --说明: f425e=1000030 对应requirement_id
 4: len 4; hex 80000001; asc     ;;              --说明: 1 对应type
 5: len 4; hex 80000642; asc    B;;              --说明: 642=1602 对应code
 6: len 6; hex e58d97e4baac; asc       ;;
 7: len 1; hex 80; asc  ;;
--说明: record lock  锁住主键唯一索引 id=2,3
 
 
RECORD LOCKS space id 132 page no 4 n bits 72 index `index_requirement_id` of ta
ble `tusiji`.`configuration_base_dynamic` trx id 20764 lock_mode X locks gap bef
ore rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 000f4264; asc   Bd;;               --说明: f4264=1000036对应requirement_id
 1: len 8; hex 0000000000000004; asc         ;;   --说明: 4对应id
--说明: gaplock  (1000030(id=3),1000036(id=4))
 
--综上所述：产生的行锁明细   2个next-key lock (1000020,1000030(id=2)]、(1000030(id=2),1000030(id=3)]
--                           2个record lock  对应主键索引 id=2、id=3
--                           1个gap lock  (1000030(id=3),1000036(id=4))

说明:
4种锁结构(IX表锁、next-key lock、record-lock、gap-lock),共5个行锁

产生的行锁明细
1、 2个next-key lock (1000020,1000030(id=2)]、(1000030(id=2),1000030(id=3)]
2、 2个record lock 对应主键索引 id=2、id=3
3、 1个gap lock (1000030(id=3),1000036(id=4))

不妨来验证一下上述我们分析的加锁结果，另起一个session

-- 插入requirement_id=1000022 在间隙(1000020,1000030(id=2)]间
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000022','1','200','北京','1');
-- 插入被阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
-- 根据索引requirement_id=1000030更新数据
update configuration_base_dynamic set code=300 where requirement_id= 1000030;
-- 更新被阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
-- 根据主键id=3更新数据
update configuration_base_dynamic set code=300 where id= 3;
-- 更新被阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
-- 插入requirement_id=1000033 在间隙(1000030(id=3),1000036(id=4))
insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `is_valid`) values('1000033','1','200','北京','1');
-- 更新被阻塞
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 
-- 上述锁中1000036对应开区间 所以不包含1000036 可以成功更新
update configuration_base_dynamic set code=300 where requirement_id= 1000036;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

回到项目中出现的这个死锁问题，给我们带来了哪些经验？

1、对于类似新增保存单据的场景相较编辑要特别小心，因为单据ID都是递增的，每次都是锁住最大记录到Supremum的区间，只要存在并发，锁住的可能性比较大。如果是单纯的编辑，在已存在的单据ID范围内操作，并发的时候两个单据ID锁住相同区域可能性小于新增保存的时候。

2、尽量减少锁定的时间例如

🔶代码中事务方法不要加入RPC调用，影响事务执行时间

🔶不要维护一个非常大的事务，如果非常大的事务，是否考虑进行拆分

🔶事务中执行的操作较多，如果可能，将耗时较长的锁定操作放在事务方法中靠后执行，这样即缩短了锁定的时间

3、在锁定前，可以判断数据是否存在如果不存在，不做操作，避免加无用的锁，阻塞其他事务

总结一下行锁加锁规则：

原则1：加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。

优化1：索引上的等值查询（唯一索引且存在符合条件记录），给唯一索引加锁的时候，next-key lock 退化为record lock。

优化2：索引上的等值查询(唯一索引不存在符合条件记录或者非唯一索引)，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

特殊点：唯一索引上的范围查询会访问到不满足条件的第一个值为止

下面我们来验证一下加锁规则

CREATE TABLE `lock_test` (  
 `id` int(11) NOT NULL,  
 `code` int(11) NOT NULL,  
 `value` int(11) NOT NULL,  
 PRIMARY KEY (`id`),  
 KEY `index_code` (`code`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
     
insert into lock_test values(1,0,10),(10,5,100),(20,10,200),(30,15,300),(40,20,400),(50,25,500);

逻辑删除与唯一索引冲突使用NULL_bc_02

案例1 主键索引等值&范围锁
考虑以下两个语句

1、update lock_test set value=value+1,code=code+1 where id=10;

2、update lock_test set value=value+1,code=code+1 where id>=10 and id<11;

从执行结果来看是没有区别的，但是加锁的范围却是不同的，套用上述行锁加锁规则，尝试分析：

1、按照id=10的更新根据

原则1,2 加next-key lock 为 id （5,10] ，next-key lock是左开右闭。同时，根据优化1，next-key lock 退化为record lock id=10

2、根据id>=10 and id<11的更新

根据原则1,2及特殊点1，加net-key lock 为 id (1,10]， (10,20]，根据优化1 (1,10] 退化为record lock id=10 因此最终为record lock id=10 及 next-key lock id （10,20]

验证一下数据库实际加锁明细

begin  
update lock_test set value=value+1,code=code+1 where id=10;  
       
---TRANSACTION 21290, ACTIVE 37 sec  
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1  
MySQL thread id 4, OS thread handle 0x19bc, query id 60 localhost ::1 root init  
show engine innodb status  
TABLE LOCK table `tusiji`.`lock_test` trx id 21290 lock mode IX  
RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`.  
`lock_test` trx id 21290 lock_mode X locks rec but not gap  
--说明: lock_mode X locks rec but not gap 代表record lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明: id=10
1: len 6; hex 00000000532a; asc     S*;;  
2: len 7; hex 1a000001c31f5b; asc       [;;  
3: len 4; hex 80000006; asc     ;;  --说明: code=code+1=5+1=6
4: len 4; hex 80000065; asc    e;;  --说明: value=value+1=100+1=101
--说明: reocrd lock  id=10  
   

begin  
update lock_test set value=value+1,code=code+1 where  id>=10 and id<11;  
  
---TRANSACTION 21292, ACTIVE 106 sec  
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1  
MySQL thread id 4, OS thread handle 0x19bc, query id 65 localhost ::1 root init  
show engine innodb status  
TABLE LOCK table `tusiji`.`lock_test` trx id 21292 lock mode IX  
RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`.  
`lock_test` trx id 21292 lock_mode X locks rec but not gap  
--说明: lock_mode X locks rec but not gap 代表record lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明: id=10
1: len 6; hex 00000000532c; asc     S,;;  
2: len 7; hex 1b000001c43099; asc      0 ;;  
3: len 4; hex 80000006; asc     ;;  --说明: code=code+1=5+1=6
4: len 4; hex 80000065; asc    e;;  --说明: value=value+1=100+1=101
--说明:reocrd lock  id=10  

RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`.  
`lock_test` trx id 21292 lock_mode X  
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 80000014; asc     ;;  --说明:id=20
1: len 6; hex 000000005146; asc     QF;;  
2: len 7; hex b100000163012a; asc     c *;;  
3: len 4; hex 8000000a; asc     ;;  --说明:code=10
4: len 4; hex 800000c8; asc     ;;  --说明:value=200
-- 说明:next-key lock id (10,20]

案例2 非唯一索引等值锁&范围锁
考虑以下两个语句

1、update lock_test set value=1 where code=5;

2、update lock_test set value=1 where code>=5 and code<6;

从执行结果来看是没有区别的，但是加锁的范围确是不同的。请看加锁明细：

begin;  
update lock_test set value=1 where code=5;  
    
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1  
MySQL thread id 4, OS thread handle 0x19bc, query id 78 localhost ::1 root init  
show engine innodb status  
TABLE LOCK table `tusiji`.`lock_test` trx id 21302 lock mode IX  
RECORD LOCKS space id 134 page no 4 n bits 80 index `index_code` of table `tusij  
i`.`lock_test` trx id 21302 lock_mode X
--说明: lock_mode X  代表next-key lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  
0: len 4; hex 80000005; asc     ;;  --说明:code=5
1: len 4; hex 8000000a; asc     ;;  --说明:id=10 是索引index_code中code=5对应的主键id=10
--说明:next-key lock (0,5]  
  
RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`.  
`lock_test` trx id 21302 lock_mode X locks rec but not gap  
--说明: lock_mode X locks rec but not gap 代表record lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明:id=10
1: len 6; hex 000000005336; asc     S6;;  
2: len 7; hex 1f000001a326a0; asc      & ;;  
3: len 4; hex 80000005; asc     ;;  --说明:code=5
4: len 4; hex 80000001; asc     ;;  --说明:value=1
--说明: record lock  id=10  
  
RECORD LOCKS space id 134 page no 4 n bits 80 index `` of table `tusij  
i`.`lock_test` trx id 21302 lock_mode X locks gap before rec  
--说明: lock_mode X locks gap 代表gap lock
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明:code=10
1: len 4; hex 80000014; asc     ;;  --说明:id=20  是索引index_code中code=10对应的主键id=20
--说明：gap lock  index_code (5,10)  
   
begin;  
update lock_test set value=1 where code>=5 and code<6;  
    
---TRANSACTION 21299, ACTIVE 42 sec  
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 1  
MySQL thread id 4, OS thread handle 0x19bc, query id 70 localhost ::1 root init  
show engine innodb status  
TABLE LOCK table `tusiji`.`lock_test` trx id 21299 lock mode IX  
RECORD LOCKS space id 134 page no 4 n bits 80 index `index_code` of table `tusi  
i`.`lock_test` trx id 21299 lock_mode X  
--说明: lock_mode X  代表next-key lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  
0: len 4; hex 80000005; asc     ;;  --说明:code=5
1: len 4; hex 8000000a; asc     ;;  --说明:id=10
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明:code=10
1: len 4; hex 80000014; asc     ;;  --说明:id=20
--说明: next-key lock  (0,5] (5,10]  

RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`  
`lock_test` trx id 21299 lock_mode X locks rec but not gap  
--说明: lock_mode X locks rec but not gap 代表record lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;  --说明:id=10
1: len 6; hex 000000005333; asc     S3;;  
2: len 7; hex 1e000001a21631; asc       1;;  
3: len 4; hex 80000005; asc     ;;  --说明:code=5
4: len 4; hex 80000001; asc     ;;  --说明:value=1
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 80000014; asc     ;;  --说明:id=20
1: len 6; hex 000000005146; asc     QF;;  
2: len 7; hex b100000163012a; asc     c *;;  
3: len 4; hex 8000000a; asc     ;;  --说明:code=10
4: len 4; hex 800000c8; asc     ;;  --说明:value=200
--说明: record-lock id=10,id=20

案例3 limit限制加锁范围
考虑以下两个语句

1、update lock_test set value=1 where code=5;

2、update lock_test set value=1 where code=5 limit 1;

从数据上来看 code=5的数据只有一条，所以两条语句执行没有区别，但是加锁范围是有区别的，语句1的加锁如案例2中已经说明，重点看语句2的加锁

begin;
update lock_test set value=1 where code=5 limit 1;

---TRANSACTION 1826, ACTIVE 47 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
--说明: 3种锁结构 2个行锁  
MySQL thread id 17944, OS thread handle 123145481035776, query id 31 localhost root starting
show engine innodb status
TABLE LOCK table `test`.`lock_test` trx id 1826 lock mode IX
RECORD LOCKS space id 25 page no 4 n bits 80 index index_code of table `test`.`lock_test` trx id 1826 lock_mode X
--说明: lock_mode X  代表next-key lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;; --说明:code=5
 1: len 4; hex 8000000a; asc     ;; --说明:id=10 是索引index_code中code=5对应的主键id=10
--说明:next-key lock (0,5]  

RECORD LOCKS space id 25 page no 3 n bits 80 index PRIMARY of table `test`.`lock_test` trx id 1826 lock_mode X locks rec but not gap
--说明: lock_mode X locks rec but not gap 代表record lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;; --说明:id=10
 1: len 6; hex 000000000722; asc      ";;
 2: len 7; hex 39000001440110; asc 9   D  ;;
 3: len 4; hex 80000005; asc     ;; --说明:code=5
 4: len 4; hex 80000001; asc     ;; --说明:value=1
 --说明: record lock  id=10

加了limit后少了加锁 gap lock index_code (5,10)，在更新或者删除数据的时候尽量加 limit。这样不仅可以控制影响的数据的条数，让操作更安全，还可以减小加锁的范围

案例4 等值查询间隙锁

begin;  
update lock_test set value=1 where id=8;  
     
---TRANSACTION 21255, ACTIVE 94 sec  
2 lock struct(s), heap size 360, 1 row lock(s)  
--说明: 2种锁结构 1个行锁  
MySQL thread id 1, OS thread handle 0x19bc, query id 9 localhost ::1 root init  
show engine innodb status  
TABLE LOCK table `tusiji`.`lock_test` trx id 21255 lock mode IX  
RECORD LOCKS space id 134 page no 3 n bits 80 index `PRIMARY` of table `tusiji`.  
`lock_test` trx id 21255 lock_mode X locks gap before rec  
--说明: lock_mode X locks gap 代表gap lock
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  
0: len 4; hex 8000000a; asc     ;;             --说明: 对应id=10  
1: len 6; hex 000000005146; asc     QF;;  
2: len 7; hex b100000163011d; asc     c  ;;  
3: len 4; hex 80000005; asc     ;;             --说明: 对应code=5  
4: len 4; hex 80000064; asc    d;;             --说明: 对应value=100  
--说明: gap lock 锁住主键间隙(1,10)  
   
--验证加锁情况 新启动一个session  
insert into lock_test(id,code,value) values(8,2,80);  
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  
  
update lock_test set code=code+1,value=value+1 where id=10;  
Query OK, 1 row affected (0.05 sec)  
Rows matched: 1  Changed: 1  Warnings: 0

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。