InnoDB隔离级别、锁

2019-08-04  本文已影响0人  Harri2012

一、锁相关的知识

1.1 MVCC

Innodb中是基于MVCC实现的，MVCC(Multi-Version Concurrency Control) ，多版本并发控制协议。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。MVCC中分为快照读和当前读。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

1.1.1 快照读

1.1.2 当前读

1.2 锁模式
讨论锁的话，先确定几点。锁存在的前提是事务开启或者AUTOCOMMIT=0，否则都不存在锁。锁是加在索引上，如果表没有设置任何索引，最终会锁在InnoDb自带的隐式主键。索引的实现不在这里细说。

讲锁之前，先抛一个重要的论点，用大白话讲，锁就是锁住一些满足条件的资源，防止其他操作改变这些资源的状态。记录锁分为S锁(共享锁)和X锁(排他锁)

1.2.1 S锁

共享锁，允许事务读一行数据。一个事务拥有了某条记录的共享锁之后，其他事务也可以申请这条记录的共享锁。

1.2.2 X锁

排他锁，允许事务修改一行数据。一个事务拥有某条记录排它锁后，其他事务不能申请任何锁。排它锁与任何锁都是互斥的。

1.2.3 锁的兼容性

S锁 X锁
S锁 兼容 不兼容
X锁 不兼容 不兼容

1.3 意向锁

如果只有X锁和S锁的话，会存在性能问题。考虑以下场景：

事务B先对表dealbasic中某一行n加了X锁，事务A再向表dealbasic加X锁，

那么这种情况下事务A即使持有了dealbasic的表锁，但是表中的数据行n仍然会被其他事务所修改，同理事务B即使持有了行n的X锁，但是行n的数据仍然会被其他事务所修改。

为了防止这种情况，事务A在向表加X锁前就必需检查表中的每一行是否有被其他事务加锁，这一过程十分耗性能。

因此innoDB中提供了意向锁机制，意向锁仅有表级别的锁粒度，innoDB中提供了两种意向锁：意向共享锁(IS) 和 意向排他锁(IX)，意向锁与X锁及S锁的兼容性如下：

IS IX S锁 X锁
X锁 不兼容 不兼容 不兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S锁 兼容 不兼容 兼容 不兼容
IS 兼容 兼容 兼容 不兼容

上述场景中，事务B对dealbasic加行级X锁前需要先对表加IX锁，之后事务A再向dealbasic加X锁时由于X锁与IX锁不兼容，因此加锁不会成功

1.4 锁算法

以下以first表为例，表结构为 first( primary id, uniq un, key num, name);

CREATE TABLE `first` (
  `id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '主键',
  `name` varchar(40) DEFAULT NULL COMMENT '名字',
  `num` int(10) unsigned DEFAULT '0',
  `un` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un` (`un`),
  KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='第一张表'

表中记录如下:


122919469.png

1.4.1 record lock

记录锁，就是某一条记录上的锁，分为S锁和X锁。 比如说上图中id=20那一行


122898795.png 
SELECT * FROM first WHERE id = 20 --不会加任何锁
SELECT * FROM first WHERE id = 20  LOCK IN SHARE MODE -- 对id=20加record lock S锁
SELECT * FROM first WHERE id = 20 FOR UPDATE -- 对id=20加record lock X锁

1.4.2 gap lock

间歇锁，是锁住一个范围，不允许新插入数据。目标是保证不能插入新的满足查询条件的数据。间歇锁不是X锁，可以同一区间的多个间歇锁共存。如上图中16到20这个区间，不包括16和20这两条记录


122954129.png

间隙锁只有在事务级别是RR时存在，间隙锁存在的所有目的都是为了防止幻读

如 SELECT * FROM first WHERE id > 16 AND id < 20 FOR UPDATE 会加上述(16,20)的gap lock

其他事务无法在这个范围内插入数据，因此保证同一事务再次执行上述SQL时不会幻读

1.4.3 next key lock

N锁，gap lock + record lock，锁住一个范围，并且锁定记录本身。如上面两个例子的结合。所谓next是指满足条件的记录范围内最右记录加上记录锁，左开右闭。上图中可以产生的N锁有(6,15] (15,16] (16,20]。和next key lock对应的是previous key lock，左闭右开。[6,15) [15,16) [16,20)。 InnoDB用的是N锁

1.5 事务、锁相关的表

mysql> select * from INNODB_TRX \G
*************************** 1. row ***************************
                    trx_id: 44877
                 trx_state: LOCK WAIT
               trx_started: 2019-01-04 10:39:40
     trx_requested_lock_id: 44877:109:3:2
          trx_wait_started: 2019-01-04 10:39:40
                trx_weight: 2
       trx_mysql_thread_id: 13
                 trx_query: select * from first where num < 13 for update
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 44876
                 trx_state: RUNNING
               trx_started: 2019-01-04 10:38:48
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 12
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 26
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.01 sec)
字段 说明 备注
trx_requested_lock_id 等待锁资源的锁id 不等待的为NULL
trx_wait_started 事务等待起始时间 不等待的为NULL
trx_weight 事务权重 回滚时优先选择权重低的
trx_mysql_thread_id 线程id show processlist会显示这个id
trx_operation_state 操作的状态
trx_query 事务等待的sql语句
trx_started 事务开始时间
trx_state 事务状态
trx_id 事务id 
select * from INNODB_LOCKS \G

mysql> select * from INNODB_LOCKS\G;
*************************** 1. row ***************************
    lock_id: 44877:109:3:2
lock_trx_id: 44877
  lock_mode: X
  lock_type: RECORD
 lock_table: `zkl`.`first`
 lock_index: PRIMARY
 lock_space: 109
  lock_page: 3
   lock_rec: 2
  lock_data: 1
*************************** 2. row ***************************
    lock_id: 44876:109:3:2
lock_trx_id: 44876
  lock_mode: X
  lock_type: RECORD
 lock_table: `zkl`.`first`
 lock_index: PRIMARY
 lock_space: 109
  lock_page: 3
   lock_rec: 2
  lock_data: 1
2 rows in set, 1 warning (0.00 sec)
字段 说明 备注
lock_id 锁id
lock_trx_id 事务id
lock_mode 锁模式
lock_type 锁类型 表锁或者行锁
lock_table 锁的表
lock_index 锁住的索引
lock_space 锁对象的spaceid
lock_page 锁定页的数量
lock_rec 锁定行的数量
lock_data 锁定对象的主键值 
mysql> select * from INNODB_LOCK_WAITS\G;
*************************** 1. row ***************************
requesting_trx_id: 44877
requested_lock_id: 44877:109:3:2
blocking_trx_id: 44876
blocking_lock_id: 44876:109:3:2
1 row in set, 1 warning (0.00 sec)
字段 说明 备注
requesting_trx_id 申请锁资源的事务id 被阻塞的事务id
requested_lock_id 申请锁资源的锁id 被阻塞的锁id
blocking_trx_id 阻塞事务id
blocking_lock_id 阻塞锁id

二、InnoDB隔离级别、锁解决的问题

2.1 MySQL/InnoDB定义的4种隔离级别

Read Uncommited
可以读取未提交记录。此隔离级别，不会使用，忽略。

Read Committed (RC)
快照读忽略，本文不考虑。
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。

Repeatable Read (RR)
快照读忽略，本文不考虑。

针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。

Serializable

从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

2.2 RR级别下幻读解决

幻读是什么？事务中同一查询语句查出的结果不一致或者插入不存在记录时写入冲突的现象

InnoDB下业务可以通过加锁避免幻读现象，并不是InnoDB本身解决了这个问题

普通select由于是快照读，两次查询结果是一致的

当前读的话，会对满足条件的记录和范围加N锁，这种情况下其他事务插入不了数据，从而避免了幻读

三、具体sql和锁分析

分析sql产生的锁之前需要先确定一些条件，如隔离级别、sql中用到的查询条件是什么索引，以下都以RR级别为例分析当前读，快照读无需分析

3.0 加锁说明

InnoDB下使用的next key lock，会对满足条件的范围加gap锁，记录加record lock

InnoDB下也做了优化，如果查询的索引含有唯一属性时，会降级为record lock，只对满足条件的记录加record lock

当InnoDB引擎查找不到满足条件的记录时，只会加gap lock，gap lock不是X锁，多个gap lock可以共存

3.1 主键索引

根据主键索引当前读查询时，因为主键对应的记录只有一条记录，那么只会对主键索引加record lock，其他事务对该记录当前读时会被阻塞，但不影响对其他记录的当前读

select * from first where id = 20 for update \G
122919469.png 122898795.png
122968805.png

3.2 唯一索引

根据唯一索引当前读时，会对唯一索引加record lock，同时也会对该记录的主键索引加record lock

select * from first where un = 20 for update \G
122969572.png

3.3 普通索引

根据普通索引当前读时，除了对查询记录的普通索引加record lock，还会在普通索引前后加gap lock，另外也会对查询记录上主键索引加record lock, 唯一索引上加uniq key

select * from first where num = 15 for update;

这条语句加的锁有 num=15的record lock，id=15的record lock，un=15的record lock，num处于(0，15)的gap lock，num处于(15，16)的gap lock


123024868.png

3.4 非索引

非索引的情况下，会对全表做扫描

select  * from first where name = '20'

此时会对所有的记录加X锁，对所有间隙加gap锁，本例中共25个X锁和26个gap锁，相当于锁表。

3.5 记录不存在vs记录存在

3.5.1 主键索引不存在

会对主键索引值所在的前后区间加一个gap锁

select * from first where id = 14 for update;   -- 产生的gap区间是(6,15)
123025619.png

3.5.2 唯一索引、普通索引等
大家自行测试下

3.6 查询范围sql
3.6.1 主键索引查询
会对满足条件的记录都会加上record lock，同时会在各个记录之间加上gap lock，满足条件的记录之前和之后都加上gap锁。 甚至第一个不满足条件的记录还会被加上record lock

select * from first where id > 14 for update;  -- 所有查询出记录的record lock， gap lock，(6, 15) （52，+∞)  id = 6 没有加上record lock
123006501.png 
select * from first where id < 14 for update; 
 -- 产生的锁有1、2、3、4、5、6主键索引的record lock，(0，1）（6，15）  id = 15也加上了record lock, why?
123080092.png

3.6.2 唯一索引、普通索引查询等

大家自行测试下

3.7 多条件的复杂sql

分析时需要对where的条件进行拆分，得出用哪个条件进行查询，哪个条件进行筛选。会对InnoDB引擎根据查询条件找到的记录加record lock，即使最终可能会被筛选条件过滤掉。where条件拆分可以参考这位大神的博客 http://hedengcheng.com/?p=577

select * from first where id > 16 and id < 20 for update;
122994581.png

四、死锁

死锁的原因是，两个事务中加锁的顺序不当，导致两个事务互相等待，无外力作用，无法进行下去的情况。最终InnoDB会回滚其中一个，而让另外一个可以正常执行

上一篇下一篇

猜你喜欢

热点阅读