@transactional 多次插入数据报错_服务端（后端）| 谈谈数据库中的行锁(二)：由一次项目中的死锁说起...

| 场景复现 |

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

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

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

1、事务型的数据库保存方法 (带@Transactional注解的方法)，涉及数据库多个表的操作，其中包括表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:有效 2:无效',   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');

2、每次执行上述保存操作过程中， 会先将原来的值逻辑删除，即使用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_invalid = 1     *  where requirement_id = #{requirementId} and type = #{type}     */      configurationBaseDynamicDao.delete(base.getRequirementId(), BaseDynamicTypeEnum.DESTINATION);      log.info("完成删除单号{} DESTINATION数据",base.getRequirementId());      List types = JsonUtil.toList(base.getDestinationInfo(), Type.class);      // 将type转换为ConfigurationBaseDynamic对象格式      List 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.tuniu.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.tuniu.crm.req.dao.impl.ConfigurationBaseDynamicDaoImpl.insert(ConfigurationBaseDynamicDaoImpl.java:32)   at com.tuniu.crm.req.dao.impl.ConfigurationBaseDynamicDaoImpl.insertBatch(ConfigurationBaseDynamicDaoImpl.java:40)   at com.tuniu.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的死锁检测分析详细死锁成因

执行如下命令，方便打印出具体锁定信息

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 updateinsert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `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 table  `tusiji`.`configuration_base_dynamic` trx id 19948 lock_mode X insert intention 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 update     insert into `configuration_base_dynamic` (`requirement_id`, `type`, `code`, `value`, `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 table     `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 table     `tusiji`.`configuration_base_dynamic` trx id 19949 lock_mode X insert intention 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的InnoDB中的行锁

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阻止。

如何能够保护比现有表中现有的最大记录到正无穷之间的间隙，以及最小记录到负无穷之间的间隙，引入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个行锁，如下为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))

不妨来验证一下上述我们分析的加锁结果，另起一个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  Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 6; hex 00000000532a; asc     S*;;  2: len 7; hex 1a000001c31f5b; asc       [;;  3: len 4; hex 80000006; asc     ;;  4: len 4; hex 80000065; asc    e;;  -- 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  Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 6; hex 00000000532c; asc     S,;;  2: len 7; hex 1b000001c43099; asc      0 ;;  3: len 4; hex 80000006; asc     ;;  4: len 4; hex 80000065; asc    e;;  -- 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     ;;  1: len 6; hex 000000005146; asc     QF;;  2: len 7; hex b100000163012a; asc     c *;;  3: len 4; hex 8000000a; asc     ;;  4: len 4; hex 800000c8; asc     ;;  -- 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`Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 4; hex 80000005; asc     ;;  1: len 4; hex 8000000a; asc     ;;  --说明：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  Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 6; hex 000000005336; asc     S6;;  2: len 7; hex 1f000001a326a0; asc      & ;;  3: len 4; hex 80000005; asc     ;;  4: len 4; hex 80000001; asc     ;;  --说明：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  Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 4; hex 80000014; asc     ;;  --说明：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  Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 4; hex 80000005; asc     ;;  1: len 4; hex 8000000a; asc     ;;  Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 4; hex 80000014; asc     ;;  --说明: 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  Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 4; hex 8000000a; asc     ;;  1: len 6; hex 000000005333; asc     S3;;  2: len 7; hex 1e000001a21631; asc       1;;  3: len 4; hex 80000005; asc     ;;  4: len 4; hex 80000001; asc     ;;  Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 4; hex 80000014; asc     ;;  1: len 6; hex 000000005146; asc     QF;;  2: len 7; hex b100000163012a; asc     c *;;  3: len 4; hex 8000000a; asc     ;;  4: len 4; hex 800000c8; asc     ;;  --说明: record-lock id=10,id=20  

案例3

等值查询间隙锁

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  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

| END |

扫码关注我们

途牛技术中心

期待与你相逢