mysql 索引间隙锁_关于mysql innodb间隙锁的一些思考

innodb锁机制

在mysql当中,关于innodb的锁类型总共可以分为四种,包含了行锁和表锁,分别是基本锁(Shared Locks：S锁和排它锁(Exclusive Locks：X锁))、意向锁(intention lock,分为意向共享锁(IS锁)和意向排他锁(IX锁))、行锁(record Locks、gap locks、next-key locks、Insert Intention Locks)、自增锁(auto-inc locks)

共享锁、意向锁、排他锁的兼容矩阵如下:

行锁的兼容矩阵如下:

本文主要想介绍下间隙锁的一些相关知识，以及出现问题的一些解决方案

间隙锁官方解释

间隙锁是在索引记录之间的间隙上的锁定，或在最后一个索引记录之前或之后的间隙上的锁定。

间隙可能跨越单个索引值，多个索引值，甚至为空。

间隙锁是性能和并发性之间权衡的一部分，并且在一些事务隔离级别中使用。

使用唯一索引锁定行来搜索唯一行的语句不需要间隙锁定。(这不包括搜索条件包含多列唯一索引的某些列的情况;在这种情况下，会发生间隙锁定)。例如，如果id列具有唯一索引，则以下语句会使用id值为100 的行的索引记录锁定，其他会话是否在前面的间隙中插入行无关紧要：

SELECT*FROMchildWHEREid=100;

如果id没有索引或具有非唯一索引，则该语句会锁定上述间隙。

事务A可以在间隙上持有一个共享间隙锁(gap S-lock),与此同时，事务B在同一间隙上持有一个排他间隙锁(gap X-lock)。允许两个间隙锁的原因是，如果从索引中清除记录，则必须合并不同事务对记录持有的间隙锁。

间隙锁在InnoDB中只是一种“ 纯粹的抑制 ",它们只是阻止其他事务插入到当前间隙当中,但是它们不会阻止不同的事务在同一间隙上获得间隙锁。因此，共享间隙锁具有与排他间隙锁相同的效果。

间隙锁可以被明确禁用。就是将事务隔离级别更改为READ COMMITTED或启用 innodb_locks_unsafe_for_binlog 系统变量(现在已被弃用)。在这种情况下，针对搜索和索引扫描禁用间隙锁，仅用于外键约束检查和重复键检查。

还有使用READ COMMITTED隔离级别或启用WHERE条件进行评估后，将释放非匹配行的记录锁。对于 UPDATE语句，InnoDB 做一个“ 半一致 ”的读取，将最新的提交版本返回给MySQL，以便MySQL可以确定该行是否符合UPDATE的where条件。

可能会出现的问题

当高并发访问时执行delete操作和insert操作时，如

delete from user u where u.id = 2; insert into user u values(2);

delete from user u where u.id = 3; insert into user u values(3);

执行过程中可能会出现乱序，所以当执行delete from user u where u.id = 2和delete from user u where u.id = 3时就会抛出死锁。这时数据库会扫描索引，数据库会向左扫描扫到第一个比给定参数小的值， 向右扫描扫描到第一个比给定参数大的值， 然后以此为界，构建一个区间，最终会锁住整个区间内的数据，这就是间隙锁的死锁，这种情况就得修改代码逻辑， 存在才删除，尽量不去删除不存在的记录。

解决方案

mysql默认采用的是可重复读(即repeatable read)隔离级别,并且会以Next-Key Lock的方式(即record lock和gap lock的结合)对数据行进行加锁，这样可以有效防止幻读的发生,但是在平时开发过程中,一些不正确的行为可能会导致间隙锁锁定范围变得很大，线程间互相等待从而导致死锁,下面将提供一些解决思路供大家参考:

1.将事务隔离级别改为读提交(即read committed)，binlog格式改为row或mixed,但可能会产生脏读

2.避免删除不存在的数据，这样会导致间隙锁锁定范围变得很大，可能会导致死锁,一般采用的方法是先读取数据，如果存在，再进行删除

3.对于select from where for update操作，一定要保证where条件字段的值一定存在，不然可能会导致间隙锁锁定的范围变得很大，阻塞别的事务，也有可能会导致死锁

4.将一个较长的事务分解成几个较小的事务

5.采用序列化(即serializable)隔离级别

6.避免在同一个事务中对同一张表的数据同时进行快照读和当前读

总结

在日常的开发过程中,虽然很少会遇到像mysql表死锁的一些问题，但是了解常用的存储引擎的一些特性和组成结构还是非常有必要的,便于在遇到问题的时候能够更快的查出问题并制定出解决方案。另外，在开发的过程中如果遇到一些长事务或者是多个数据插入、更新或删除操作的时候多考虑一下，采取正确的做法也许就可以避免一些不必要线上问题的发生。

补充点(供排查问题的时候使用)

1. 查看事务隔离级别

SELECT @@global.tx_isolation;

SELECT @@session.tx_isolation;

SELECT @@tx_isolation;

2. 设置隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

例如：set session transaction isolation level read uncommitted;

3. 查看表状态

show table status like '表名';

4. 查看SQL性能

set profiling=1;

show profiles

或

show profile for query 1;(show profiles查询出来的id)

5. 查看当前最新事务ID

每开启一个新事务，记录当前最新事务的id，可用于后续死锁分析。

show engine innodb status;

6. 查看事务锁等待状态情况

select * from information_schema.innodb_locks;

select  *from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

7. 查看innodb状态(包含最近的死锁日志)

show engine innodb status;

本文来自网易实践者社区，经作者姚成授权发布。