前言

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

```

ERROR1213(40001):Deadlockfound when trying togetlock;tryrestarting transaction

```

表锁和行锁

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

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

表锁

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

```

mysql>locktable userread(write);

# 分为读锁和写锁QueryOK,0rows affected(0.00sec)

mysql>select*fromuserwhereid=100;

# 成功mysql>select*fromrolewhereid=100;

# 失败，未提前获取该 role的读表锁

mysql>update usersetname='Tom'whereid=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 sesswhere 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#;

```