30 答疑文章二，用动态的观点看加锁

答疑文章二，用动态的观点看加锁

回顾加锁原则：

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

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

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁

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

CREATE TABLE `t30` (

  `id` int(11) NOT NULL,

  `c` int(11) DEFAULT NULL,

  `d` int(11) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `c` (`c`)

) ENGINE=InnoDB;

insert into t30 values(0,0,0),(5,5,5),

(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

等值查询和遍历的区别，为什么在不等查询里面，这个过程也有等值查询？

begin;

select * from t30 where id>9 and id<12 order by id desc for update;

利用上面的加锁规则，加锁的范围是主键id上(0,5],(5,10],和(10,15)，也就是说id=15这一行上面并没有加锁，

--用到了优化2，索引上的等值查询，向右遍历id=15不满足条件，所以next-key lock退化为间隙锁(10,15)

这里where条件是>,<，那等值查询是从哪里来的呢？在加锁过程中，加锁动作是发生在语句执行过程中的，所以在分析加锁行为的时候，要从索引数据结构上开始。

1 首先这个查询的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到id<12的值

2 这个过程是通过索引树的搜索过程等到的，在innodb 引擎内部，其实要找到id=12的值是没有找到的，只是最终没找到，但找到了(10,15)这个间隙

3 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5的行，所以会加一个(0,5]的next-key lock

也就是说，在执行过程中，通过树搜索的方式等位记录的时候，是用的”等值查询”的方法。

等值查询的过程

begin;
select id from t30 where c in(5,20,10) lock in share mode;
查看其执行计划
(system@127.0.0.1:3306) [test]> explain select id from t30 where c in(5,20,10) lock in share mode;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t30   | range | c             | c    | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

可以看到，这个in语句使用了index c并且rows=3，说明这3个值都是通过索引树搜索定位的。

在查找c=5的时候，先锁住了(0,5]，但是因为c不是唯一索引，为了确认还有没有c=5的值，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)

同样的，执行c=10这个逻辑的时候，加锁范围是(5,10]和(10,15),执行c=20这个逻辑的时候，加锁的范围是(15,20],(20,25)。

通过这个分析，我们知道，这条语句在索引c上加的三个记录锁的顺序是c=5,10,20

加锁的过程是，在执行过程中一个一个加的，而不是一次性加上的。

分析死锁

同时另外一个语句执行

select id from t30 where c in(5,20,10) order by c desc for update;

此时，加锁的范围是什么呢？

我们知道间隙锁是互不锁的，但是这两条语句都会在索引c上c=5,10,20加3个记录锁，

这里需要注意，由于语句里面是order by c desc，这3个的加锁顺序是c=20,10,5的加锁顺序。

也就是说，这2个语句要加锁相同的资源，但是加锁顺序相反，这2个语句并发执行的时候，就可能出现死锁

查看死锁日志

(system@127.0.0.1:3306) [(none)]> show ENGINE innodb STATUS\G;

看几个关键信息

1 (1) TRANSACTION,第一个事务的信息

   (2) TRANSACTION，第二个事务的信息

  WE ROLL BACK TRANSACTION(1),最终的处理结果是回滚事务1。

2 第一个事务的信息中：

 --WAITING FOR THIS LOCK TO BE GRANTED，表示这个事务等待的信息

 -- index c of table test.t ，说明在等的是表t的索引c上面的锁

 --lock mode S waiting 表示这个语句要加一个自己的读锁，当前的状态时等待中

 --Record lock说明这是一个记录锁

 --n_fields 2表示这个记录是两列，也就是字段c和pk字段id

 --0：len4；hex 0000000a;asc;;是第一个字段，也就是c，值是十六进制a，也就是10

 --1：len4；hex 0000000a;asc;;是第二个字段，就是id，值也是10

 --这两行里面的asc表示的是，接下来要打印出里面的”可打印字符”，但10不是可打印字符，因此显示空格

 --第一个事务信息就只显示了等锁的状态，在等待(c=10,id=10)这一行的锁

 --既然出现了死锁，就表示这个事务也占有了别的锁，但是没有显示出来。

3 第二个事务显示的信息要多一些：

 --HOLDS HTE LOCK(S)用来显示这个事务持有那些锁

 --index c of table test.t 表示锁是在表t的索引c上

 --hex 0000000a和hex 00000014表示这个事务持有c=10和c=20这两个记录锁

 --WAITING FOR THIS LOCK TO BE GRANTED，表示在等待锁(c=5,id=5)的记录锁

从上面这些信息中，我们知道：

　　1 lock in share mode这个语句，持有了c=5的记录锁，在等c=10的锁

　　2 for update这个语句，持有了c=20和c=10的记录锁，在等c=5的锁

因此这里导致了死锁，得出2个结论：

1 由于锁是一个个的加，要避免死锁，对同一组资源，要按照尽量相同的顺序访问

2 在发生死锁的时刻，for update这条语句占有的资源更多，回滚成本更大，所以innodb选择了回滚成本更小的lock in share mode语句，来回滚。

 

怎么查看锁等待？

SESSION A	SESSION B
begin; select * from t30 where id>10 and id<=15 for update;
	delete from t30 where id=10;(query ok) insert into t30 values(10,10,10);(blocked)

 

 

---TRANSACTION 20857539, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 656316, OS thread handle 0x7f23c233d700, query id 19729617 127.0.0.1 system update
insert into t30 values(10,10,10)
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1334 page no 3 n bits 80 index `PRIMARY` of table `test`.`t30` trx id 20857539 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000f; asc     ;;
 1: len 6; hex 0000013da4cb; asc    =  ;;
 2: len 7; hex d30000016c0137; asc     l 7;;
 3: len 4; hex 8000000f; asc     ;;
 4: len 4; hex 8000000f; asc     ;;

 

查看几个关键信息：

--index `PRIMARY` of table `test`.`t30` 表示这个语句被锁住是因为表t30主键上的某个锁

-- lock_mode X locks gap before rec insert intention waiting

 ---insert intention 表示当前线程准备插入一条记录，是一个插入意向锁

 ---gap before rec 表示这是一个间隙锁，不是记录锁

--那么这个gap是在那个记录之前的呢？

--n_fields 5表示这个记录有5列：

 ---0: len 4; hex 8000000f; asc     ;; 第一列是主键id字段，十六进制f就是id=15，所以这个间隙是id=15之前的，因为id=10已经不存在，就表示了(5,15)

 ---1: len 6; hex 0000013da4cb; asc    =  ;;第二列是长度为6个字节的事务id，表示最后修改一行的trx_id为0000013da4cb--20817099--SELECT conv( '0000013da4cb', 16, 10 );

 ---2: len 7; hex d30000016c0137; asc     l 7;;第三列是长度为7字节的回滚段信息

 --后面2列式c和d的值，都是15

因此，我们知道了，由于delete操作把id=10这一行删除，原来的间隙(5.10),(10,15)变成了一个(5,15)

1 session a执行完select后，什么都没做，但它加锁的范围突然增加了

2 当我们执行select *from t where c>15 and c<=20 order by c desc lock in share mode，向左扫描到c=10的时候，要把(5,10]锁起来，也就是说，所谓”间隙”，其实跟本是有”这个间隙右边的那个记录”定义的。

Update的例子

SESSION A	SESSION B
begin; select c from t30 where c>5 lock in share mode;
	update t30 set c=1 where c=5; (query ok) update t30 set c=5 where c=1;(blocked)

---TRANSACTION 20875696, ACTIVE 5 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1
MySQL thread id 656316, OS thread handle 0x7f23c233d700, query id 19764740 127.0.0.1 system updating
update t30 set c=5 where c=1
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1334 page no 4 n bits 80 index `c` of table `test`.`t30` trx id 20875696 lock_mode X locks gap before rec insert intention waiting
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 8000000a; asc     ;;
Session A的加锁范围是索引c上的(5,10],(10,15],(15,20],(20,25],(25,supremum]

--根据c>5查到的第一个记录是c=10，因此不会加(0,5]这个next-key lock

之后session b的第一个update语句，把c=5改成c=1，可以理解为两步

--1 插入(c=1,id=5)这条记录

--2 删除(c=5,id=5)这条记录

索引c上(5,10)间隙由这个间隙右边的记录，也就是c=10定义的， 所以通过这个操作，session a的加锁范围变成了

接下来，session b要执行update t30 set c=5 where c=1这个语句，分成两步

--1 插入(c=5,id=5)这个记录

--2 删除(c=1,id=5)这个记录

第一步试图在已经加了间隙锁的(1,10)中插入数据，所以被堵塞了。