湖北省和建设厅网站,永久云服务器,wordpress 评论优化,品牌网站建设3a小蝌蚪catalog加锁规则等值查询间隙锁非唯一索引等值锁主键索引范围锁非唯一索引范围锁唯一索引范围锁 bug非唯一索引上存在等值的例子limit语句加锁关于死锁总结 1、查询过程中访问到的对象才会加锁#xff0c;而加锁的基本单位是next-key lock#xff08;前开后闭等值的例子limit语句加锁关于死锁 总结 1、查询过程中访问到的对象才会加锁而加锁的基本单位是next-key lock前开后闭 2、等值查询上MySQL的优化索引上的等值查询如果是唯一索引next-key lock会退化为行锁如果不是唯一索引需要访问到第一个不满足条件的值此时next-key lock会退化为间隙锁 3、范围查询无论是否是唯一索引范围查询都需要访问到不满足条件的第一个值为止 加锁规则 默认这里的隔离级别是可重复读。 原则1加锁的基本单位是next-key lock。该锁的区间是前开后闭 原则2查找过程中访问到的对象才会加锁 优化1索引上的等值查询给唯一索引加锁的时候next-key lock退化为行锁 优化2索引上的等值查询向右遍历时且最后一个值不满足等值条件的时候next-key lock退化为间隙锁 bug唯一索引上的范围查询会访问到不满足条件的第一个值为止。 建表语句
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);等值查询间隙锁 非唯一索引等值锁 session A给索引c上c5这一行加上读锁。
根据原则1加锁单位是next-key lock 因此会给(0,5]加上next-key lock.
c是普通索引因此访问c5后还需要向右遍历查到c10才放弃。
根据原则2访问到的都要加锁因此要给(5,10]加上next-key lock;
同时符合优化2等值判断向右遍历最后一个值不满足c5于是退化为间隙锁(5,10);
(前面分析的(0, 5]间隙锁还是存在的合起来存在(0, 5]和(5, 10)两个间隙锁 )
根据原则2只有访问到的对象才会加锁这个查询使用的是覆盖索引并不需要访问主键索引所以主键索引上没有加任何锁所以session B的update语句可以执行完成。
**访问到的对象才会加锁这个“对象”指的是列不是 记录行。 补充一下 加锁是加在索引上的。 列上有索引就加在索引上 列上没有索引就加在主键上 **
session C要插入(7,7,7)记录会被session A 的间隙锁(5,10)锁住。
lock in share mode 只锁覆盖索引for update 会顺便给主键索引上满足条件的行加上行锁。
总结
锁是加在索引上的
用lock in share mode 来给行加读锁避免数据被更新的话就必须绕过覆盖索引的优化在查询字段中加入索引中不存在的字段。
如将session A的语句
select id from t where c5 lock in share mode; 修改为
select d from t where c 5 lock in share mode;数据行加读锁如果查询字段使用了覆盖索引访问到的对象只有普通索引并没有访问到主键索引则不会锁主键索引。如果没有使用覆盖索引且当前查询是for update ,update 和 delete 都是当前读则会回表查询访问到主键索引这样主键索引也会加锁。
主键索引范围锁
对于表t有两个查询语句,这两个语句加锁范围不同
select * from t where id 10 for update;
select * from t where id 10 and id 11 for update;这两句话逻辑上等价但是加锁规则不一样。 执行流程
1、找到第一个id10的行因此本该是next-key lock (5,10]。根据优化1主键id上的等值条件退化成行锁只加id 10这一行的行锁
2、范围查找继续往后找找到id15这一行停下来因此需要加next-key lock (10,15].
所以session A这时候锁的范围就是主键索引上行锁id 10和next-key lock(10,15]。 因为是范围查询不是等值查询所以不会进行优化2 所以会出现B C的block情况。
需要注意 首次session A 定位查找id10的行时候是当作等值查询来判断的而向右扫描到id15的时候用的是范围查询判断。
--引用
1. 先走主键id索引, 拿出id10的那一行, (注意这里是等值查询) 2. 再从id10的那一行开始, 不断地往右遍历拿出每一行, 直到它的 id 不满足 大于等于10, 小于11 这个条件后, 再停止 (注意这里就是范围查询) 根据一开始的Creae table/ insert values等语句(10后面就是15), 还有再根据加锁规则(原则1, 原则2, 优化1, 优化2, bug5): 执行步骤1时, 因为是等值查询, 主键索引又是唯一索引, 根据原则1, 原则2, 优化1, 最终只加行锁10; 执行步骤2时, 因为是范围查询, 主键索引又是唯一索引, 根据原则1, 原则2, Bug5, 而不满足条件的第一个值就是15, 所以最终要加锁(10, 15]; 这一块相对还是比较繁琐的非唯一索引范围锁 唯一索引范围锁 bug 非唯一索引上存在等值的例子
给表t插入一条新纪录
insert into t values(30,10,30);此时表里面就有了两个c10的行。 因为主键是唯一的 所以不存在完全相同的两行 此时的索引c为 两个c10但是主键id不同(分别为10和30)因此这两个c10的记录之间也是有间隙的。 这里使用delete语句来验证。delete原则和之前update原则一样。 session A遍历的时候先访问第一个c10的记录。根据原则1这里加的是
(c 5,id 5) 到(c 10,id 10)这个next-key lock.
然后session A向右查找直到碰到(c15,id15)这一行循环才结束。根据优化2这是一个等值查询向右查找到了不满足条件的行所以会退化成(c10,id10)到(c15,id15)的间隙锁。
也就是说这个delete语句在索引c上的加锁范围如下 注意(c5,id5)和(c15,id15)这两行都没有锁。
limit语句加锁 关于死锁
next-key lock实际上是间隙锁和行锁加起来的结果。 分析流程
1、session A启动事务执行查询语句加lock in share mode在索引c上加了next-key lock(5,10]和间隙锁(10,15);
2、session B的update语句在索引c上加next-key lock(5,10]进入锁等待
3、然后session A要再插入(8,8,8)这一行被session B的间隙锁锁住。由于出现了死锁InnoDB让session B回滚。
我们认为session B的加锁还没申请成功。
但是其实session B的加next-key lock(5,10]操作实际上分成了两步先是加(5,10)的间隙锁加锁成功然后加c10的行锁第二步才被锁住。
也就是说我们分析加锁的具体步骤时需要分成间隙锁和行锁两段来执行。
