谈谈数据库中的行锁(二):由一次项目中死锁说起

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

场景复现

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

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

  1. 操作事务型的数据库保存操作,涉及数据库多个表的操作,其中包括表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');
  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
T1begin;
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;
T2insert into configuration_base_dynamic (requirement_id, type, code, value, is_valid) values(‘3000000’,‘1’,‘200’,‘北京’,‘1’);
进入等待状态
T3insert 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(负无穷大的记录)
在这里插入图片描述
进一步思考,如果更新的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);

在这里插入图片描述

案例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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值