MySQL——锁的类型以及加锁原理、死锁

前言

使用 insert into on duplicate key update 语句进行插入去重，但是在测试过程中发现了死锁现象:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

表锁和行锁

首先来了解一下表锁和行锁：表锁是指对一整张表加锁，一般是 DDL 处理时使用；而行锁则是锁定某一行或者某几行，或者行与行之间的间隙。

表锁由 MySQL Server 实现，行锁则是存储引擎实现，不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁，而 MyISAM 则只能使用 MySQL Server 提供的表锁。

表锁

表锁由 MySQL Server 实现，一般在执行 DDL 语句时会对整个表进行加锁，比如说 ALTER TABLE 等操作。在执行 SQL 语句时，也可以明确指定对某个表进行加锁。

mysql> lock table user read(write); # 分为读锁和写锁
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where id = 100; # 成功
mysql> select * from role where id = 100; # 失败，未提前获取该 role的读表锁
mysql> update user set name = 'Tom' where id = 100; # 失败，未提前获得user的写表锁

mysql> unlock tables; # 显示释放表锁
Query OK, 0 rows affected (0.00 sec)

表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过 unlock tables 释放所有表锁。

除了使用 unlock tables 显示释放锁之外，会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁；会话持有其他表锁时执行 start transaction 或者 begin 开启事务时，也会释放之前持有的锁。

行锁

不同存储引擎的行锁实现不同，后续没有特别说明，则行锁特指 InnoDB 实现的行锁。

在了解 InnoDB 的加锁原理前，需要对其存储结构有一定的了解。InnoDB 是聚簇索引，也就是 B+树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值，所以通过二级索引查询数据时，还需要拿对应的主键去聚簇索引中再次进行查询。关于 InnoDB 和 MyISAM 的索引的详细知识可以阅读《Mysql探索(一):B+Tree索引》一文。

下面以两条 SQL 的执行为例，讲解一下 InnoDB 对于单行数据的加锁原理。

update user set age = 10 where id = 49;
update user set age = 10 where name = 'Tom';

第一条 SQL 使用主键索引来查询，则只需要在 id = 49 这个主键索引上加上写锁；第二条 SQL 则使用二级索引来查询，则首先在 name = Tom 这个索引上加写锁，然后由于使用 InnoDB 二级索引还需再次根据主键索引查询，所以还需要在 id = 49 这个主键索引上加写锁，如上图所示。

也就是说使用主键索引需要加一把锁，使用二级索引需要在二级索引和主键索引上各加一把锁。

根据索引对单行数据进行更新的加锁原理了解了，那如果更新操作涉及多个行呢，比如下面 SQL 的执行场景。

update user set age = 10 where id > 49;

上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录，然后 InnoDB 引擎会将第一条记录返回并加锁，接着 MySQL Server 发起更新改行记录的 UPDATE 请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。

这种场景下的锁的释放较为复杂，有多种的优化方式，我对这块暂时还没有了解，还请知道的小伙伴在下方留言解释。

下面主要依次介绍 InnoDB 中锁的模式和类型，锁的类型是指锁的粒度或者锁具体加在什么地方；而锁模式描述的是锁的兼容性，也就是加的是什么锁，比如写锁或者读锁。

行锁的模式

行锁的模式有：读意向锁，写意向锁，读锁，写锁和自增锁(auto_inc)，下面我们依次来看。

读写锁

• 读锁：又称共享锁（Share locks，简称 S 锁），加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。

• 写锁：又称排他锁（Exclusive locks，简称 X 锁），或独占锁，对记录加了排他锁之后，只有拥有该锁的事务可以读取和修改，其他事务都不可以读取和修改，并且同一时间只能有一个事务加写锁。

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当你要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL 引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，也可分为读意向锁（IS 锁）和写意向锁（IX 锁）。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。

意向锁之间是不会产生冲突的，也不和 AUTO_INC 表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。

自增锁

AUTO_INC 锁：又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTO_INCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTO_INC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的。

AUTO_INC 锁具有如下特点：

• AUTO_INC 锁互不兼容，也就是说同一张表同时只允许有一个自增锁；
• 自增值一旦分配了就会 +1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。

显然，AUTO_INC 表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL 从 5.1.22 版本开始，引入了一种可选的轻量级锁（mutex）机制来代替 AUTO_INC 锁，可以通过参数 innodb_autoinc_lock_mode 来灵活控制分配自增值时的并发策略。具体可以参考 MySQL 的 AUTO_INCREMENT Handling in InnoDB 一文，链接在文末。

不同模式锁的兼容矩阵

下面是各个表锁之间的兼容矩阵。

总结起来有下面几点：

• 意向锁之间互不冲突；
• S 锁只和 S/IS 锁兼容，和其他锁都冲突；
• X 锁和其他所有锁都冲突；
• AI 锁只和意向锁兼容；

行锁的类型

根据锁的粒度可以把锁细分为表锁和行锁，行锁根据场景的不同又可以进一步细分，依次为 Next-Key Lock，Gap Lock 间隙锁，Record Lock 记录锁和插入意向 GAP 锁。

不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-Key Lock 则所属记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。

下面我们来依次了解一下不同的类型的锁。

记录锁

记录锁是最简单的行锁，并没有什么好说的。上边描述 InnoDB 加锁原理中的锁就是记录锁，只锁住 id = 49 或者 name = ‘Tom’ 这一条记录。

当 SQL 语句无法使用索引时，会进行全表扫描，这个时候 MySQL 会给整张表的所有数据行加记录锁，再由 MySQL Server 层进行过滤。但是，在 MySQL Server 层进行过滤的时候，如果发现不满足 WHERE 条件，会释放对应记录的锁。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。

所以更新操作必须要根据索引进行操作，没有索引时，不仅会消耗大量的锁资源，增加数据库的开销，还会极大的降低了数据库的并发性能。

间隙锁

还是最开始更新用户年龄的例子，如果 id = 49 这条记录不存在，这个 SQL 语句还会加锁吗？答案是可能有，这取决于数据库的隔离级别。这种情况下，在 RC 隔离级别不会加任何锁，在 RR 隔离级别会在 id = 49 前后两个索引之间加上间隙锁。

间隙锁是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。

值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入，所以加间隙 S 锁和加间隙 X 锁没有任何区别。

Next-Key 锁

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含15、18、20 ，30，49，50 这几个值，可能的 Next-key 锁如下：

(-∞, 15]，(15, 18]，(18, 20]，(20, 30]，(30, 49]，(49, 50]，(50, +∞)

通常我们都用这种左开右闭区间来表示 Next-key 锁，其中，圆括号表示不包含该记录，方括号表示包含该记录。前面四个都是 Next-key 锁，最后一个为间隙锁。
和间隙锁一样，在 RC 隔离级别下没有 Next-key 锁，只有 RR 隔离级别才有。还是之前的例子，如果 id 不是主键，而是二级索引，且不是唯一索引，那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)

此时如果插入一条 id = 31 的记录将会阻塞住。之所以要把 id = 49 前后的间隙都锁住，仍然是为了解决幻读问题，因为 id 是非唯一索引，所以 id = 49 可能会有多条记录，为了防止再插入一条 id = 49 的记录。

插入意向锁

插入意向锁是一种特殊的间隙锁（简写成 II GAP）表示插入的意向，只有在 INSERT 的时候才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是两个完全不同的概念，不要搞混了。

插入意向锁和插入意向锁之间互不冲突，所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中，id = 30 和 id = 49 之间如果有两个事务要同时分别插入 id = 32 和 id = 33 是没问题的，虽然两个事务都会在 id = 30 和 id = 50 之间加上插入意向锁，但是不会冲突。

插入意向锁只会和间隙锁或 Next-key 锁冲突，正如上面所说，间隙锁唯一的作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

不同类型锁的兼容矩阵

不同类型锁的兼容下如下图所示。

其中，第一行表示已有的锁，第一列表示要加的锁。插入意向锁较为特殊，所以我们先对插入意向锁做个总结，如下：

• 插入意向锁不影响其他事务加其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务是没有任何影响的；
• 插入意向锁与间隙锁和 Next-key 锁冲突。也就是说，一个事务想要获取插入意向锁，如果有其他事务已经加了间隙锁或 Next-key 锁，则会阻塞。

其他类型的锁的规则较为简单：

• 间隙锁不和其他锁（不包括插入意向锁）冲突；
• 记录锁和记录锁冲突，Next-key 锁和 Next-key 锁冲突，记录锁和 Next-key 锁冲突；

死锁

在解决Mysql 死锁的问题之前，还是先来了解一下什么是死锁。

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等的进程称为死锁进程。

死锁的表现

死锁的具体表现有两种：
Mysql 增改语句无法正常生效，使用Mysql GUI 工具编辑字段的值时，会出现异常。 如何避免死锁

阻止死锁的途径就是避免满足死锁条件的情况发生，为此我们在开发的过程中需要遵循如下原则：

• 1、尽量避免并发的执行涉及到修改数据的语句。

• 2、要求每一个事务一次就将所有要使用到的数据全部加锁，否则就不允许执行。

• 3、预先规定一个加锁顺序，所有的事务都必须按照这个顺序对数据执行封锁。如不同的过程在事务内部对对象的更新执行顺序应尽量保证一致。

查看死锁

Mysql 查询是否存在锁表有多种方式，这里只介绍一种最常用的。

• 1、查看正在进行中的事务

SELECT * FROM information_schema.INNODB_TRX

• 2、查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

• 3、查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

• 4、查询是否锁表

SHOW OPEN TABLES where In_use > 0;

在发生死锁时，这几种方式都可以查询到和当前死锁相关的信息。

• 5、查看最近死锁的日志

show engine innodb status

解除死锁

如果需要解除死锁，有一种最简单粗暴的方式，那就是找到进程id之后，直接干掉。

查看当前正在进行中的进程

show processlist

// 也可以使用
SELECT * FROM information_schema.INNODB_TRX;

这两个命令找出来的进程id 是同一个。

杀掉进程对应的进程 id

kill id

验证（kill后再看是否还有锁）

SHOW OPEN TABLES where In_use > 0;

Oracle查看锁表sql

查看锁表进程SQL语句1：

select sess.sid,
    sess.serial#,
    lo.oracle_username,
    lo.os_user_name,
    ao.object_name,
    lo.locked_mode
    from v$locked_object lo,
    dba_objects ao,
    v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

查看锁表进程SQL语句2：

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

杀掉锁表进程：

如有记录则表示有lock，记录下SID和serial# ，將记录的ID替換下面的738,1429，即可解除LOCK

alter system kill session '738,1429';

查看导致锁表的sql语句是那一条

select l.session_id sid, 
    s.serial#, 
    l.locked_mode, 
    l.oracle_username, 
    s.user#, 
    l.os_user_name, 
    s.machine, 
    s.terminal, 
    a.sql_text, 
    a.action 
    from v$sqlarea a, v$session s, v$locked_object l 
where l.session_id = s.sid 
    and s.prev_sql_addr = a.address 
    order by sid, s.serial#;

参考：
https://www.mscto.com/data/459802.html

https://www.mscto.com/mysql/647739.html

https://www.iteye.com/blog/xiarilian12-571315