Mysql innodb行锁
2020-01-13 本文已影响0人
ugeek
1、什么时候加锁？
在InnoDb事务中，行锁是在需要的时候加上的，但并不是用完马上释放掉，而是等到事务结束时才释放。这就是两阶段锁协议。
最佳实践：在事务中如果需要加多个锁，尽量将影响并发度的锁往后放，以减少持有锁的时间。
2、InnoDB行锁算法有哪几种？
InnoDB中行锁有三种分别是，
record lock：单个行记录上的锁
gap lock：间隙锁，锁定一个范围，但不锁定记录本身。间隙锁只会阻塞间隙间的数据插入(insert)。多个间隙锁获取操作不冲突。
next-key lock：record lock+gap lock，前开后闭的区间。
3、行锁的实现
加锁的对象是事务。
- InnoDB通过给索引项上的索引加锁来实现行锁。这也意味着只有通过索引条件检索数据才会被加上行锁，否则InnoDB将使用表锁。
- 索引不仅包括主键索引，普通索引、唯一索引都可以使用行锁
- 只有真正执行计划用到的索引才会被加锁，条件里面包含索引列的语句在执行时不一定会用到索引，可使用explain分析具体的执行情况
4、InnoDB是如何加行锁的？
select语句显式加锁：
//共享锁（读锁） select ... lock in share mode
//互斥锁（写锁） select ... for update
更新操作（update）和插入（insert）操作会自动加锁
加锁规则：
1.加锁基本单位是next-key lock，即前开后闭区间
2.查找过程中访问到的对象才会加锁
3.索引上的等值查询，给唯一索引加锁时，next-key lock退化为行锁
4.索引上的等值查询，向右遍历时且最右一个值不满足等值条件的时候，next-key lock退化为间隙锁
示例：在innodb可重复读的隔离级别下
建表语句如下：
CREATE TABLE `t` (
`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 t values(0,0,0),(5,5,5), (10,10,10),(15,15,15),(20,20,20),(25,25,25);
1）示例1
begin；
update t set d=d+1 where id=7
加锁： id索引的（5,10）间隙锁
2)示例2
image1.png
读锁 索引c （0，5],(5,10)
分析：根据加锁规则2，查找过程中访问到的对象才会加锁，在c=5的等值条件下，会去找索引c， 在索引c上加锁,并不会在主键上加锁.因此sessionB走主键索引更新没问题。注意： 若改成for update，那么系统认为会更新数据，会在主键上满足条件的行也加上锁。
3）示例3
image2.png
加锁：主键索引：[10],(10,15] 扫描到10时按等值加锁，扫描到15按范围加锁
4）示例4
image3.png
索引c：（5，10],(10,15] 主键索引
id：对应行锁(写锁)
5）示例5
insert into t values(30,10,30);
image4.png
加锁：索引c,(5,10],(10,10] ，主键：id对应行锁
分析：加了limit2之后，在查找到第二条满足条件的c之后就停止向后搜索了。因此在在删除 数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全， 还可以减小加锁的范围。
5、不同隔离级别，加锁策略和一致性读区别？
6.png
7.png
6、死锁出现的场景？如何解决？
死锁（Deadlock）：死锁是指两个或多个事务执行过程中，因争夺锁资源而造成相互等待的现象。
解决方案：
- innodb_deadlock_detect：innodb配置开启死锁检测，默认开启。
- innodb_lock_wait_timeout：配置获取锁的最大等待时间，默认50s
- 减小锁的范围：在使用有锁的语句时，尽量减少锁的范围，加锁语句使用索引
- 减少锁的时间：获取锁的语句尽量放到事务后面执行，尽量只把必要的语句放进事务
- 多个事务尽量按照相同顺序获取锁
参考：1、https://zhuanlan.zhihu.com/p/29150809
2、林晓斌-MySQL实战45讲
