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

quniMdejiangyou

2018-04-12

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中的锁的机制原理，那么再解决死锁或者避免死锁就会很容易！

mysql mysql update语句 innodb 共享锁 mysql事务 mysql索引 oracle死锁索引事务

安科网

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

quniMdejiangyou

quniMdejiangyou

相关推荐

MySQL中使用binlog时格式该如何选择

MySQL数据操作-DML语句的使用

详解 MySQL中count函数的正确使用方法

MySQL中的undo日志

MySql索引使用策略分析

导致MySQL索引失效的一些常见写法总结

MySQL数据类型优化原则

Uber为什么放弃Postgres选择迁移到MySQL？

Golang操作MySql数据库的完整步骤记录

MySQL主从复制原理以及需要注意的地方

Mysql联表update数据的示例详解

专业级的MySQL开发设计规范及SQL编写规范

Mysql 查询JSON结果的相关函数汇总

Mysql 实现字段拼接的三个函数

浅谈MySQL中的自增主键用完了怎么办

mysql 如何动态修改复制过滤器

MySQL ddl语句的使用

mysql 8.0.22 安装配置图文教程

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

MySQL 基于时间点的快速恢复方案

quniMdejiangyou