1.等值查询和遍历有什么区别
为什么在where条件中是不等号,过程中仍然有等值查询
比如如下的查询语句的加锁范围
begin;
select * from t where id>9 and id<12 order by id desc for update;
这个语句的加锁范围是(0,5],(5,10],(10,15)也就是id=15这一行,没有加上行锁
看起来是倒序查找导致的,但是一开始还是使用的向右遍历
找到了符合的值之后,才会进行向左遍历
所以加了(10,15]
这里用上了优化2,即索引上的等值查询,向右遍历不满足条件的时候,next-key-lock退化为了间隙锁(10,15)
然后因为是倒排
首先找到第一个id<12的,就是(10,15)这个间隙,但是遍历过程中,会找到5这一行,于是还是会加一个(0,5]
2.看如下的语句的加锁范围
begin;
select id from t where c in(5,20,10) lock in share mode;
解释计划如上
这个语句使用了索引c并且只查找了三行,说明这三个值都是通过B+树来定位的
在查找c=5的时候,锁住了nextkey lock (0,5]
但是因为c不是唯一索引,继续遍历,于是找到了c=10,先加上了间隙锁(5,10),接下来给c=10上锁
在之后,一直加锁 (10,15],(15,20] (20,25)
这个顺序总结为
先加上c=5的间隙锁,然后是c=10的间隙锁,c=20的间隙锁
那么由于整个加锁的范围是一个一个的加上去的
所以可能出现加锁过程中的死锁
比如同时执行如下的语句
select id from t where c in (5,20,10) order by c desc for update
那么这个语句也会加锁
这个语句的加锁顺序是颠倒的,也就是先锁c=20,然后c=10,最后c=5
于是可能出现死锁的问题
那么我们需要查看
show engine innodb status的命令
可以看出来上一次死锁的问题
在其中
1.WAITING FOR THIS LOCK TO BE GRANTED 是第一个事务的信息
2.TRANSACTION 是第二个事务的信息
WE ROLL BACK TRANSACTION 是最终的处理结果,表示回滚了一个事务
第一个事务中,WATTING FRO THIS LOCK TO BE GRANTED,表示事务在等待的锁信息
index c of table test.t 表示表t的索引c上的锁
lock mode S waiting 表示这个语句自己加一个读锁
Record lock表示是一个记录锁
n_field 2表示这个记录是两行,也就是字段c和主键字段id
0 len 4 hex 0000000a asc 表示第一个字段 10
1: len 4; hex 0000000a; asc ;; 是第二个字段，也就是主键 id，值也是 10；
第一个事务表示正在等待一行锁 (c=10,id=10)
第二个事务表示的信息更多
HOLDS THE LOCKS 表示这个事务持有哪些锁
index c of tables testt表示在表t的索引c上
hex 000000a和 hex 00000014表示持有c=10和c=20这两个记录锁
WAITING FOR THIS LOCK TO BE GRANTED表示在等(c=5,id=5)这个记录锁
导致了lock in share mode 这个语句,持有c=5的记录锁,在等c=10的锁
for update 持有 c=20和c=10,等待c=5的记录锁
接下来是一个锁等待的案例
本来seesionA并没有锁住c=10这个记录,只上了(5,10)这个间隙锁和(10,15]这个next key lock
于是删除id=10这一行是可以做到的,但是删除之后,整个锁合二为一了变为了(5,15]
那么再想者插入一行id=10的锁,是不可行的
图中
index PRIMARY of table test t 表示锁住是因为表t上的锁
lock mode X locks gap before rec insert intention waiting表示了
insert intention是一个插入动作,就是插入动作本身
gap before rec是一个间隙锁
接下来从0-4表示了gap是记录上面的
| 0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段，十六进制 f 就是 id=15。所以，这时我们就知道了，这个间隙就是 id=15 之前的，因为 id=10 已经不存在了，它表示的就是 (5,15)。1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id，表示最后修改这一行的是 trx id 为 1299 的事务。2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到，这里的 acs 后面有显示内容 (% 和 4)，这是因为刚好这个字节是可打印字符。后面两列是 c 和 d 的值，都是 15。 |
也就是间隙锁本身,是由间隙右边的记录决定的
比如select * from t where c>=15 and c<=20 order by desc lock in share mode就是在向左扫描,需要把(5,10]圈起来
3.update
上面的加锁范围是 (5,10] (10,15] (15,20] (20,25],(25,superNum]
由于将c=5的一行设置为了c=1改变了加锁范围
这个update语句可以理解为插入了(c=1,id=5)的记录
删除了(c=5,id=5)的记录,导致可以锁的范围变为了(1,10]
然后执行下一个update语句,插入了(c=5,id=5)的记录,删除了(c=1,id=5)的记录,导致在插入的时候就已经被阻塞了
遇到类似的问题的时候,可以考虑
以后在遇到问题的时候,可以考虑去show engine innodb status来查看事务信息和死锁
在整个日志文件中,
lock_mode X waiting是 next key lock
lock_mode X locks rec but not gap是行锁
locks gap before rec是只有间隙锁
对于一个空表,也是有间隙锁的
如果一个空表上执行如下的session
其加锁的范围就是next-key lock(-∞, supremum]