Mysql Innodb 锁机制

2018-02-05 07:53:32来源:cnblogs.com作者:magicsoar人点击

分享

latch与lock

latch 可以认为是应用程序中的锁,可以称为闩锁(轻量级的锁) 因为其要求锁定的时间必须要非常短,若持续时间长,则会导致应用性能非常差,在InnoDB存储引擎中,latch又可以分为mutex(互斥锁)和rwlock(读写锁),其目的用来保证并发线程操作临界资源的正确性,并且没有死锁检测的机制

 

在InnoDB存储引擎中的latch,可以通过命令SHOW ENGINE INNODB MUTEX 来进行查看

mysql > SHOW ENGINE INNODB MUTEX;

 

NewImage 

 

lock可以认为是数据库提供的锁,用来锁定的是数据库中的数据。并且一般lock对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同),lock是有死锁机制的。

在InnoDB存储引擎中的lock 可以通过show engine innodb status,information_schema.INNODB_LOCKS,INNODB_TRX,INNODB_LOCK_WATIS信息来查看

NewImage 

线程获取lock的流程:

在对数据加lock的时候会先对数据所在的页面添加latch,然后再对数据添加lock,添加完lock后再释放页面的Latch。

这种机制主要是为了保证线程获取的行数据的一致性和完整性.

如果lock被其他的线程占有,线程先释放页面latch,等待lock,待获取lock后会再次对页面添加latch,查看页面数据是否有改动,然后尝试再次获取对应的lock

 

 

共享锁与排他锁

innodb储存引擎提供了如下两种标准的行级锁

共享锁(S) 允许一个事务去读一行

排他锁(X) 允许获得排他锁的事务更新或删除数据

 

同时innodb储存引擎支持多粒度锁定,为了支持在不同的粒度上进行加锁操作,innodb支持另一种额外的锁方式,称之为意向锁

 

意向共享锁(IS)  事务想要获得一张表中某几行的共享锁

意向排他锁(IX)事务想要获得一张表中某几行的排他锁

 

在行锁的实现上

mysql提供了三种的行锁的算法

分别是

Record Lock 记录锁,单个记录上的锁

Gap Lock 间隙锁,锁定一个范围,但不包含记录本身

Next-key Lock Gap Lock + Record Lock 锁定一个范围,并且锁定记录本身

 

 

Mysql是如何加锁的

非特殊注明 默认在RR隔离级别下进行讨论

InnoDb的行锁是对索引加锁的,对扫描的行边扫描边加锁,如果走的是二级索引(非聚簇索引)除了需要对二级索引加锁外,还需要根据二级索引里面的主键信息扫描主键的聚簇索引,对主键加锁,

 加锁的数据行数会受到Mysql是否支持Index Condition PushDown而影响(Mysql 5.6支持ICP),加锁的数量可能远远大于满足条件的记录数量

这里需要加两次锁的原因是

如果

语句A 使用二级索引对记录X进行更新操作,

语句B使用聚簇索引对记录X进行更新操作,

如果A仅对二级索引进行加锁,那么并发的语句B将感受不到语句A的存在,违背了同一条记录上的更新/删除必须串行执行的约束

select * from table where?

RC级别下 : 无需加锁,一致性非锁定读,使用快照读,读取被锁定行的最新一份数据,因此会出现前后读取数据不一致的情况

RR级别下:无需加锁,一致性非锁定读,使用快照读,读取事务开始时的行数据版本,因此前后读到的数据是一样的

Serializable级别下:使用当前读,需要加锁,innodb内部将select语句转换为了select … lock in share mode

 

insert?

insert会对插入成功的行加上记录锁,不会阻止其他并发的事务往这条记录之前插入记录。在插入之前,会先在插入记录所在的间隙加上一个插入意向意向锁(并发的事务可以对同一个间隙加插入意向锁锁)。如果insert 的事务出现了duplicate-key error ,事务会对duplicate index record的记录加共享锁。这个共享锁在并发的情况下是会产生死锁的,比如有两个并发的insert都对要对同一条记录加共享锁,而此时这条记录又被其他事务加上了排它锁,排它锁的事务将这条记录删除后,两个并发的insert操作会发生死锁。

 

delete?

delete操作仅是将主键列中对对应的记录delete flag设置为1,记录并没有被删除,还是存在于B+树中

真正的删除操作被延迟了,最终在purge操作中完成

延迟到purge操作的原因是的innodb支持mvcc多版本控制,所以记录不能在事务提交时立即进行删除,只有当对应的行记录不被任何其他事务引用的时候,才可以由purge进行真正的删除

delete操作过程中:

找到满足条件的记录,并且记录有效,则对记录加X锁

找到满足条件的记录,但是记录无效(标识为删除),则对记录加next key锁、;

未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入;

 

update?

对满足条件的记录next-key锁,如果是等值匹配并且使用唯一索引或是聚簇索引,那么可以只添加记录锁

 

 

唯一索引中含NULL值的记录,将不会添加记录锁,转而为next-key锁 因为NULL不等于NULL,NULL和任何值比较均返回NULL,包括NULL本身,但是 NULL is NULL

 

死锁案例分析

create table `deadlocktest`

(

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`a` bigint(20) unsigned NOT NULL,

`b` bigint(20) unsigned NOT NULL,

`c` bigint(20) unsigned NOT NULL,

`d` bigint(20) unsigned NOT NULL,

`e` bigint(20) unsigned NOT NULL,

PRIMARY KEY(`id`),

UNIQUE KEY `I_a`(`a`),

KEY `I_b` (`b`),

KEY `I_c` (`c`)

)ENGINE=InnoDb ;

 

insert into deadlocktest (a,b,c,d,e)values(1,999,3,4,5);

insert into deadlocktest (a,b,c,d,e)values(2,998,4,5,6);

insert into deadlocktest (a,b,c,d,e)values(3,997,4,5,6);

insert into deadlocktest (a,b,c,d,e)values(4,996,3,4,5);

...

insert into deadlocktest (a,b,c,d,e)values(1000,1,3,4,5);

 

3个insert的死锁

事务A

事务B

事务C

begin;

begin;

begin;

insert into deadlocktest (a,b,c,d,e)values(4,996,3,4,5);

 

 

 

insert into deadlocktest (a,b,c,d,e)values(4,996,3,4,5);

 

 

 

insert into deadlocktest (a,b,c,d,e)values(4,996,3,4,5);

rollback;

 

 

 

1 row affected

Deadlock found when trying to get lock; try restarting transaction

事务A 获得排他锁,插入数据成功

事务B 事务C,因为记录duplicate-key error转而持有行的共享锁

事务A回滚,释放了持有的排他锁,事务B和事务C需要获得该行的排他锁,但是由于互相都持有对应行的共享锁,互相等待,造成死锁

 

2个update的死锁

事务A

事务B

begin;

begin;

update deadlocktest force index(I_b) set e = sleep(5) where b>0;

 

 

update deadlocktest force index(I_c) set e = sleep(5) where c>2;

Deadlock found when trying to get lock; try restarting transaction

Rows matched: 4  Changed: 4  Warnings: 0

两个update事务,加锁顺序不一样导致的死锁

InnoDb的行锁是对索引加锁的,对扫描的行边扫描边加锁,如果走的是二级索引(非聚簇索引)除了需要对二级索引加锁外,还需要根据二级索引里面的主键信息扫描主键的聚簇索引,对主键加锁

3个以上delete的死锁

事务A

事务B

事务B

begin;

begin;

 begin

delete from deadlocktest where a=550

 

 

 

delete from deadlocktest where a=550

 
 

 

 

delete from deadlocktest where a=550

commit;0 rows affected Deadlock found when trying to get lock; try restarting transaction

delete操作仅是将主键列中对对应的记录delete flag设置为1,实际的删除延迟到purge中

delete删除时如果找到满足条件的记录,但是记录无效(标识为删除),则对记录加next key锁、;

死锁日志

NewImage

3个delete的死锁比较难以复现,我是利用如下脚本完成的

MY_DB="mysql -hxxx -Pxxx -uxxx -pxxx"

while :
do
echo "use test;begin; delete from deadlocktest where a=499;rollback;" | $MY_DB
done

该类delete死锁的出现条件

1、针对唯一索引上等值查询的删除

2、有3个以上并发删除操作

3、事务的隔离级别是RR

4、INNODB储存引擎

参考文献

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

http://hedengcheng.com/?p=771#_Toc374698320

http://hedengcheng.com/?p=844

 

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台