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

达芬奇密码2018-06-15 12:31

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 * FROM child WHERE id = 100;

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

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

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

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

还有使用READ COMMITTED隔离级别或启用innodb_locks_unsafe_for_binlog,在MySQL对该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;

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