Py学习  »  DATABASE

Mysql加锁与实践

雨季不再来 • 5 年前 • 235 次点击  
阅读 14

Mysql加锁与实践

1. 锁分类

innodb中的锁分为S锁,即共享锁,另一种为X锁,排它锁,比如:

共享锁(S)

select * from supplier where id=5 lock in share mode;
复制代码

排他锁(X)

select * from supplier where id=5 for update;
复制代码

或者insert,delete,update语句,这都是排他锁

兼容性

这两种锁的兼容如下:

X S
X N N
S N Y

意向锁

可以理解为属于S锁和X锁的父节点,即要获取S锁或者X锁的话,必须先提前获取意向锁,即IS或者IX锁,那综合起来看下,这两类锁的兼容情况如下

X S IX IS
X N N N N
S N Y N Y
IX N N Y Y
IS N Y Y Y

隐式锁

也是醉了,为什么要搞这么多概念。。这种锁,可以认为不冲突的时候不加锁,这个时候的锁就是隐式锁,等遇到冲突了,该锁会升级为显示锁,还是通过一个例子来说明吧:

事务1(其中age是普通索引)

select * from test01 where age=21 for update;
复制代码

我们看到mysql中其实是没有找到锁的:

事务2

insert into test01(id,name,age) values(8,'zzh',22);
复制代码

这个时候由于[21,23)有gap锁,所以事务2会被阻塞住,这个时候再看mysql中的锁记录

可以看到一开始事务1虽然使用的是...for update,但是由于没有冲突所以加的是隐式锁,等到事务2开始导致有冲突存在,所以事务1的锁改为现实锁。

2.加锁分析与实践

具体在事务的不同隔离级别下,不同的场景加锁分析,在hedecheng加锁分析这篇文章已经讲解的非常详细了,这里就不再多说了。主要说下其他情况下总结的一些加锁分析。

2.1 插入意向锁(Insert Intention Locks)

先来看官网对该锁的解释

我们重点关注下红色的区域:在遇到冲突的时候,会在该索引所在的位置加S锁,而该共享锁又很容易导致死锁,官网中就有个例子来专门说明这种共享锁导致的死锁,即三个事务同事执行一条插入语句其中一个事务回滚导致死锁:

我们可以简单的把这种情况模仿一遍:

事务1

 insert into test01(id,name,age)values(12,"zzh",33);
复制代码

事务2

 insert into test01(id,name,age)values(12,"zzh",33);
复制代码

事务3

 insert into test01(id,name,age)values(12,"zzh",33);
复制代码

如下图操作:

我们看下Mysql中锁的记录

其中这个就印证了在意向锁冲突的时候,请求加的是S锁,然后我们回滚事务1

事务1

rollback
复制代码

这个时候我们看到事务2成功了,事务3出现死锁

我们可以从死锁日志中看到:

  • 事务2:等待锁模式为X的插入意向锁
  • 事务3:等待锁模式为X的插入意向锁;拥有锁模式为S的record lock
  • 暗含条件:事务2也拥有锁模式为S的record lock,这才导致了死锁,死锁日志会把最终成功获取锁了的事务已经拥有的锁不会打印出来

所以存在 事务2->事务3,事务3->事务2 死锁发生

官网中还要另一个类似的例子,这里就不多做分析了,原因类似。

下面我们看下另一种情况:

事务1(事务id=2944)

select * from test01 where age=21 for update;
复制代码

事务2(事务id=2945)

insert into test01(id,name,age)values(2,"zzh",22);
复制代码

事务3(事务id=2946)

select * from test01 where age=21 lock in share mode;
复制代码

执行如下

mysql中锁记录

我们可以看到

  • 事务1:在primary key的(X,RECORD LOCK),在age_idx的(X,RECORD LOCK)
  • 事务2:在primay key 的(S,RECORD LOCK),这个就是我们前面讲到的,事务2在获取插入意向锁出现冲突,所以阻塞在了要获取(S,RECORD LOCK)
  • 事务3:在age_idx的(S,RECORD LOCK),

然后我们提交事务1 事务1

commit;
复制代码

  • 事务2:加S锁成功,但是出现唯一键冲突,直接报错
  • 事务3:获取S锁成功,但是是在age_idx上,跟事务2不一样。

我们再重新执行事务1

事务1(事务id=29467)

select * from test01 where age=21 for update;
复制代码

执行结果被阻塞:

我们看下mysql中的锁记录:
可以看到事务3拥有在age_idx的S锁,阻塞了事务1要获取的X锁,那这个时候我们提交事务3 事务3 commit;

我们发现事务1还处于阻塞的状态,看下mysql中的锁记录

这个时候明白了,事务2虽然执行失败,但是其由于插入意向锁冲突所加的S锁并未释放,所以会导致事务1还处于阻塞中。只有当我们提交了事务2,事务1才会真正执行

2.2 select...for update

该语句加锁分为两种情况

  • 记录存在:如果是RC模式下,加的是(X,RECORD LOCK),RR 模式下,加的是(X,NEXT-KEY LOCK),该锁相互不兼容
  • 记录不存在:加(X,GAP)锁,并且该锁是兼容的,但是与Insert Intention Locks 不兼容,这个很容易导致死锁

2.2.1 不同索引之间等待出现死锁

事务1

select * from test01 where age=21 for update;
复制代码

事务2

insert into test01(id,name,age)values(3,"zzh",22);
复制代码

事务1

insert into test01(id,name,age)values(3,"zzh",100);
复制代码

按如上顺序执行结果:

我们发现出现死锁,事务2被mysql回滚之后事务1执行成功。我们看下具体的死锁日志

通过死锁日志我们可以分析出来

  • 事务1: 拥有age_idx(X,NEXT-KEY LOCK),加插入意向锁存在冲突,所以等待id=3位置的(S,RECORD LOCK)
  • 事务2: 等待age_idx的插入意向锁(插入意向锁不只是在primary key上才有)
  • 暗含条件:事务2虽然在age_idx上等待插入意向锁,但是在id=3位置上加插入意向锁成功了,这才有了事务1在该位置加插入意向锁存在冲突

2.2.2 记录不存在导致的死锁

事务1(事务id=29684)

select * from test01 where age=21 for update;
复制代码

事务2(事务id=29683)

select * from test01 where age=21 for update;
复制代码

事务1

insert into test01(id,name,age)values(3,"zzh",22);
复制代码

执行结果如下

可以看到

  • 事务1和事务2同时持有了age=22这个记录的gap锁,由于记录不存在,所以此时的gap锁兼容
  • 但是记录不存在兼容的gap锁和插入意向锁兵不兼容,所以事务1向age=22索引所请求的插入意向锁会等待。

事务2

insert into test01(id,name,age)values(3,"zzh",22);
复制代码

由上面的分析同样得出事务2的等待有两种情况:

(1) 和事务1一样,等待age=22的插入意向锁,此时发现已经有事务1在等待该位置的插入意向锁,那就等待该位置的S锁

(2) 事务1虽然在age=22的插入意向锁等待,但是id=3的插入意向锁是加成功了,所以如果事务2在id=3这里等待插入意向锁的话,也会有冲突,那就等待该位置的S锁

所以,有这两种情况都会导致事务出现死锁,我们具体看下死锁日志:

我们看到:

  • 事务1在等待age=22位置的插入意向锁
  • 事务2在等待 id=3位置的S锁,也就是我们分析的第二种情况,同时事务2拥有age=22位置的gap锁

我们上面有分析,事务2的等待应该是有两种情况,而出现这两种情况的可能就是事务2的这条语句

insert into test01(id,name,age)values(3,"zzh",22);
复制代码

我们了解到在记录不存在的时候,如果对此记录进行select...for update语句,该语句会对空位置加gap锁,这个会比较危险,如果我们的表用的是自增主键,而此时如果查询一个不存在的记录,那会把未来要插入的所有的空隙都加了gap锁,会导致以后表中无法在插入任何数据,这个极其危险。

3. 小结

本篇,我们介绍了mysql中的锁以及在实践中可能遇到的一些死锁,专门通过几个demo集中分析了一下,对于加锁的分析,在文中所提到的hedecheng的文章中,已经有很深的讲解,所以本文并没有对此总结。重点还是通过一些实际中用到的例子进行实际的分析一下整个过程。重点是插入意向锁和select...for update中的一些加锁的分析。下一篇来专门介绍下mysql的MVCC机制


今天看啥 - 高品质阅读平台
本文地址:http://www.jintiankansha.me/t/5YkZ3ny2WB
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/25259
 
235 次点击