东莞市盛裕绒艺玩具有限公司

东莞市盛裕绒艺玩具有限公司

德赢vwin

18808122512
联系方式
全国服务热线: 18808122512

咨询热线:15328380324
联系人:辛春艳
地址:台湾省高雄市新興區文橫二路167巷29-1

1-2 【包子mysql系列】, 对mysql的innoDB加锁分析

来源:德赢vwin   发布时间:2019-11-05   点击量:278

innoDB的事务,是基于锁来实现的,用到事务不自然就会用到锁,而如果对锁理解的不通透,很容易造成线上问题。

数据库加锁的分析,和事务的引擎,隔离级别,索引,主键索引都有关系,

如果去考虑引擎和各种隔离级别的话,就会很复杂了,所以下面都是基于innoDB和RR的隔离级别进行分析:

 

表结构:

内容:

 

 

1 , 根据主键更新

 

如果根据主键来行数

 

事务A

事务B

 

update user set name="ce1" where id="1";

update user set name="ce3" where Id="3";

同时执行,都成功

update user set name="ce1" where id="1";

update user set name="ce3" where userId="10003";

B更新失败,直至:Lock wait timeout

 

结论,如果根据非主键来更新,会把整个表进行锁定,无法 进行更新操作。

 

注:只要是根据主键索引来更新,哪怕事务A没命中主键,也不会锁定整个表

 

 

2,根据非索引非主键更新

 

事务A

事务B

 

update user set name="ce1" where userId="10001";

update user set name="ce3" where Id="3";

或者

update user set name="ce3" where userId="10003"

都会失败,如果非索引,直接锁表

 

3, 如果在userId 列上加入普通唯一索引

 

修改成

再更新

事务A

事务B

 

update user set name="ce1" where userId="10001";

update user set name="ce3" where Id="3";

或者

update user set name="ce3" where userId="10003"

都会成功,如果有唯一索引,也是能成功行数,互相不影响

 

4, 如果在userId 列上加入普通非唯一索引 (重点探讨)

把userId改成非唯一索引:

 

记录内容如下:

+----+--------+------+

| id | userId | name |

+----+--------+------+

| 1 | 10001 | ce1 |

| 2 | 10002 | ce2 |

| 3 | 10001 | ce3 |

| 4 | 10004 | ce4 |

+----+--------+------+

再相同操作

事务A

事务B

 

update user set name="ce1" where userId="10001";

update user set name="ce3" where Id="3";

B失误执行失败,显然id=3的这行也被锁住了

其实最终还是按主键锁住的记录 id=1和id=3的记录

非唯一索引与普通索引,更一步的区别是GAP锁

gap锁是用于解决幻读的存在,演示

把记录修改成,如:

id为pk. userId为Normal key

A事务

B事务

结果

begin;

 

update user set name="ce22" where userId="100020";

  
 

insert into user (userId,name) values("100021","tttt");

直至事务失败超时

1, 首先GAP锁针对的是insert操作

2, 当更新userId="100020"时,会锁住两边的记录区间,防止幻读的存在。

3, 锁是作用在普通索引上,但由于索引是由B+树存储,那么锁住的是两边的区间,防止insert

GAP锁为什么不是锁住一条记录,而是锁住一个区间呢? 

附上疑问: https://www.oschina.net/question/867417_2289606

其实:

GAP锁是解决幻读存在的,如当 delete时就必须锁住区间了

A事务

B事务

 

begin;

 

delete from user where userId="888888";

  
 

insert into user (userId,name) values("100021","tttt");

OK, 可以插入

 

insert into user (userId,name) values("100041","tttt");

插入超时

可见,这个GAP锁,锁住的是100040~无穷大 的记录

死锁的产生分析

1, 两条语句产生的死锁

id = pk, userId= key

最简单的。两条语句互相更新等待

 

begin;

 

update user set name="ce1" where userId="100010";

begin;

 

update user set name="ce2" where userId="100020";

update user set name="ce2" where userId="100020";

update user set name="ce1" where userId="100010";

 

最简单的死锁

 

2, 由于gap锁,删除一台不存在的记录

 

如,先删除一条记录,然后插入一条记录, 如果记录GAP锁冲突,两个事务容易互为死锁。如:

 

A事务

B事务

begin;

delete from user where userId="100020";

(Query OK, 1 row affected)

begin;

 

 

delete from user where userId="565656";

insert into user (userId,name) values("100041","tttt");

 

 

insert into user (userId,name) values("100019","tttt");

 

结果直接抛出:

 

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

 

分析:

 

A事务 delete 加入gap锁【100010,100020】, 第二段【100020,100030】

B事务 delete加入gap 锁【100040,无穷大】

然后A事务插入,获取插入意向锁时B事务的GAP锁被阻塞

B事务插入,获取插入意向锁时时被A事务的GAP锁阻塞

结果死锁

 

相关产品

COPYRIGHTS©2017 德赢vwin ALL RIGHTS RESERVED 备案号:278