Mysql Innodb 锁机制 select * from table where?insert?delete?update?3个insert的死锁2个update的死锁3个以上delete的死

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，因为记录duplicate-key error转而持有行的共享锁

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

2个update的死锁

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

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

3个以上delete的死锁

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