MySQL的innoDB锁机制以及死锁处理

177次阅读

共计 9125 个字符，预计需要花费 23 分钟才能阅读完成。

MySQL 的 nnoDB 锁机制

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，innodb 正常的 select ID from table where id=1；不会上任何锁，接下来详细讨论 InnoDB 的锁问题;

一：InnoDB 行锁的介绍。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁, 也就是我读取的行，你不能修改；

排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也就是我更新的行，不允许其他的事务读取和更新相同的行；

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

意向锁是 InnoDB 自动加的，不需用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE；

排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE；

InnoDB 行锁模式兼容性列表：

MySQL 的 innoDB 锁机制以及死锁处理

如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

二：关于 innodb 锁机制，实现原理：

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！索引分为主键索引和二级索引两种，如果一条 sql 语句操作了主键索引，MySQL 就会锁定这条主键索引; 如果一条语句操作了二级索引，MySQL 会先锁定该二级索引，再锁定相关的主键索引。

然后 innodb 行锁分为三种情形：

1）Record lock：对索引项加锁，即锁定一条记录。

2）Gap lock：对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身

3）Next-key Lock：锁定一个范围的记录并包含记录本身（上面两者的结合）。

注意：InnoDB 默认级别是 repeatable-read 级别，所以下面说的都是在 RR 级别中的。

Next-Key Lock 是行锁与间隙锁的组合，这样，当 InnoDB 扫描索引记录的时候，会首先对选中的索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。如果一个间隙被事务 T1 加了锁，其它事务是不能在这个间隙插入记录的

举例 1：

假设我们有一张表：

+—-+——+

| id | age |

+—-+——+

| 1 | 3 |

| 2 | 6 |

| 3 | 9 |

+—-+——+

表结构如下：

CREATE TABLE `liuhe` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `keyname` (`age`)

) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=gbk ;

这样我们 age 段的索引就分为

(negative infinity, 3],

(3,6],

(6,9],

(9,positive infinity)；

我们来看一下几种情况：

1）当事务 A 执行以下语句：

mysql> select * from liuhe where age=6 for update ;

不仅使用行锁锁住了相应的数据行，同时也在两边的区间，（3,6]和（6，9] 都加入了 gap 锁。

这样事务 B 就无法在这两个区间 insert 进新数据，同时也不允许 update liuhe set age=5 where id=1（因为这也类似于在（3,6]范围新增）, 但是事务 B 可以在两个区间外的区间插入数据。

实验如下：

事务 A:

mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from liuhe ;（age 上有索引）

+—-+——+

| id | age |

+—-+——+

| 1 | 3 |

| 2 | 6 |

| 3 | 9 |

+—-+——+

4 rows in set (0.00 sec)

mysql> select * from liuhe where age=6 for update ;

+—-+——+

| id | age |

+—-+——+

| 2 | 6 |

+—-+——+

1 row in set (0.00 sec)

事务 B，尝试 insert age= 5 的数据，确实有锁等待，说明确实（3,6]上区间锁，防止在这个区间插入；

mysql> insert into liuhe (id,age) values (5,5);

查看事务状态，发现确实是等待；

mysql> select * from INNODB_TRX\G;

*************************** 1. row ***************************

trx_id: 27162

trx_state:LOCK WAIT

trx_started: 2018-04-06 00:03:39

trx_requested_lock_id: 27162:529:4:3

trx_wait_started: 2018-04-06 00:03:39

trx_weight: 3

trx_mysql_thread_id: 46

trx_query: insert into liuhe (id,age) values (5,5)

trx_operation_state: inserting

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

trx_rows_modified: 1

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: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

如上说明：（3,6]和（6，9] 都加入了 gap 锁。这样事务 B 就无法在这两个区间 insert 进新数据, 但是事务 B 可以在两个区间外的区间插入数据

2）当事务 A 执行如下语句：

select * from fenye where age=7 for update ;

那么就会给 (6,9] 这个区间加锁，别的事务无法在此区间插入或更新数据。

3）当事务 A 执行:

select * from fenye where age=100 for update ;

那么加锁区间就是(9,positive infinity)，别的事务无法在此区间插入新数据同时也不允许更新已有的数据到这个区间，也就是 update liuhe set age=19 where id= 1 是不允许的（因为这也类似于新增）。

整个举例 1 说明：

行锁防止别的事务修改或删除，GAP 锁防止别的事务新增(防止新增包括 insert 和 update 已有数据到这个范围中），行锁和 GAP 锁结合形成的的 Next-Key 锁共同解决了 RR 级别在写数据时的部分幻读问题，一定注意只是部分幻读问题；

举例 2：

假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,…,100,101，下面的 SQL：

Select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对 empid 大于 101（这些记录并不存在）的“间隙”加锁，这样其他事务就不能在 empid > 100 范围 insert 数据了。

InnoDB 使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了 empid 大于 100 的任何记录，那么本事务如果再次执行上述语句，就会发生幻读

举例 3

假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,5,7,9,10,19，那么下面的 sql:

select * from emp where empid >2 and empid <16 for update ;

那么 InnoDB 不仅会对符合条件的 empid 值为 5,7,9,10 的记录加锁，也会对（2，16）这个区间加“间隙”加锁，这样其他事务就不能在（2，16)范围 insert 数据了, 并且也不允许更新已有的数据到这个区间；

三：关于 innodb 锁机制需要注意的是：

1）InnoDB 行锁是通过给索引项加锁实现的，如果没有索引，InnoDB 会通过隐藏的聚簇索引来对记录加锁。也就是说：如果不通过索引条件检索数据，那么 InnoDB 将对表中所有数据加锁，实际效果跟表锁一样。

2）由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。说白了就是，where id=1 for update 会锁定所有 id= 1 的数据行，如果是 where id=1 and name=’liuwenhe’ for update, 这样会把所有 id= 1 以及所有 name=’liuwenhe’ 的行都上排它锁；

3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 优化器通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，或者饮食转换，或者 like 百分号在前等等，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。

四：查看 innodb 的相关锁；

1）查询相关的锁：

information_schema 库中增加了三个关于锁的表：

innodb_trx ## 当前运行的所有事务，还有具体的语句，

innodb_locks ## 当前出现的锁，只有

innodb_lock_waits ## 锁等待的对应关系

看一下表结构：

root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;

+————-+———————+——+—–+———+——-+

+————-+———————+——+—–+———+——-+

| lock_id | varchar(81) | NO | | | |# 锁 ID

| lock_mode | varchar(32) | NO | | | |# 锁模式

| lock_type | varchar(32) | NO | | | |# 锁类型

+————-+———————+——+—–+———+——-+

10 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;

+—————————-+———————+——+—–+———————+——-+

+—————————-+———————+——+—–+———————+——-+

| trx_weight | bigint(21) unsigned | NO | | 0 | |#

| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#

| trx_unique_checks | int(1) | NO | | 0 | |# 是否唯一性检查

| trx_foreign_key_checks | int(1) | NO | | 0 | |# 是否外键检查

| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#

| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#

+—————————-+———————+——+—–+———————+——-+

22 rows in set (0.01 sec)

mysql> show processlist; ## 可以看出来，

或者

mysql> show engine innodb status\G ## 也可以要看出相关死锁的问题

或者：

mysql> select ID,STATE from information_schema.processlist where user=’system user’;

mysql> select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’system user’;

+————————+

| concat(‘KILL ‘,id,’;’) |

+————————+

| KILL 3101; |

| KILL 2946; |

+————————+

2 rows in set (0.00 sec)

批量 kill 多个进程。

mysql>select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’root’ into outfile ‘/tmp/a.txt’;

Query OK, 2 rows affected (0.00 sec)

五：关于死锁：

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的 SQL 语句，绝大部分死锁都可以避免。

下面就通过实例来介绍几种避免死锁的常用方法。

（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

如果出现死锁，可以用 mysql> show engine innodb status\G 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

总结：MySQL innodb 引擎的锁机制比 myisam 引擎机制复杂，但是 innodb 引擎支持更细粒度的锁机制，当然也会带来更多维护的代价；然后 innodb 的行级别是借助对索引项加锁实现的，值得注意的事如果表没有索引，那么就会上表级别的锁，同时借助行级锁中 gap 锁来解决部分幻读的问题。只要知道 MySQL innodb 中的锁的机制原理，那么再解决死锁或者避免死锁就会很容易！

正文完

星哥玩云-微信公众号