MySQL的死锁系列之锁的种类及加锁原理疫情期间在家办公，同事使用insertintoonduplicatekeyupdate语句进行插入和去重，但是在测试的时候出现了死锁：ERROR1213(40001):Deadlockfoundwhentrying获得锁定；tryrestarttransaction由于开发任务紧急，只是暂时避免了，但是对触发死锁的原因和相关原理不是很了解，所以这几天一直在查阅相关资料，总结了一系列的文章供您参考。模式或类型的基本知识。后续会讲解常见语句的加锁情况，并通过MySQL死锁日志分析死锁原因。由于本文涉及到很多MySQL的基础知识，大家可以阅读我之前的MySQL系列文章《MySQL探秘》中的相应章节（系列文章可以从公众号菜单进入）。表锁和行锁先来了解一下表锁和行锁：表锁是指锁定整个表，一般在DDL处理时使用；行锁锁定某一行或几行，或者行锁和行之间的间隙。表锁由MySQLServer实现，行锁由存储引擎实现。不同的引擎实现不同。MySQL常用引擎中，InnoDB支持行锁，而MyISAM只能使用MySQLServer提供的表锁。表锁表锁由MySQLServer实现。一般在执行DDL语句时，如ALTERTABLE等操作，全表都会被锁住。在执行SQL语句时，也可以显式指定锁定某个表。mysql>locktableuserread(write);#分为读锁和写锁QueryOK,0rowsaffected(0.00sec)mysql>select*fromuserwhereid=100;#成功mysql>select*fromrolewhereid=100;#失败，没有获取到read的roleinadvanceTablelockmysql>updateusersetname='Tom'whereid=100;#失败，没有提前获取用户的写表锁mysql>unlocktables;#显示释放表锁QueryOK,0rowsaffected(0.00sec)表锁使用一次性lock技术，也就是在session开始的时候使用lock命令，把后面需要用到的所有表都加锁。在释放表之前，只能访问这些锁定的表，其他表是不能访问的，直到最后所有表都通过unlocktables释放。表锁。除了使用unlocktables来显示和释放锁外，在session持有其他表锁时执行locktable语句会释放session之前持有的锁；当session持有其他表锁执行starttransaction或beginopentransaction时，也会提前释放。持有锁。行锁在不同的存储引擎中的实现是不同的。后续如无特别说明，行锁均指InnoDB实现的行锁。在了解InnoDB的加锁原理之前，需要对其存储结构有一定的了解。InnoDB是聚集索引，即B+树的叶子节点既存储主键索引又存储数据行。InnoDB的二级索引的叶子节点存储的是主键值，所以通过二级索引查询数据时，需要在聚簇索引中再次查询对应的主键。关于InnoDB和MyISAM索引的详细知识，可以阅读文章《Mysql探索(一):B+Tree索引》。下面以执行两条SQL为例，讲解InnoDB对单行数据的加锁原理。updateusersetage=10whereid=49;updateusersetage=10wherename='Tom';第一条SQL使用主键索引查询，所以只需要在id=49的主键索引上加写锁即可；第二条SQL使用二级索引查询，先在索引name=Tom上加写锁，然后再使用InnoDB二级索引基于主键索引查询，所以还需要加写锁主键索引id=49，如上图所示。也就是说，使用主键索引需要加锁，使用二级索引需要在二级索引和主键索引上加锁。了解了根据索引更新单行数据的加锁原理，但是如果更新操作涉及到多行怎么办，比如下面的SQL执行场景。updateusersetage=10whereid>49;上述SQL的执行过程如下图所示。MySQLServer会根据WHERE条件读取到第一条满足条件的记录，然后InnoDB引擎会返回并锁定第一条记录，然后MySQLServer会发起一个UPDATE请求去更新被改变的记录来更新这条记录。一条记录操作完成后，再读取下一条记录，直到没有匹配的记录为止。这种场景下锁的释放比较复杂，优化的方式有很多。这个我还不太了解，有知道的请在下方留言说明。下面主要依次介绍InnoDB中锁的模式和类型。锁的类型指的是锁的粒度或者加锁的地方；锁模式描述了锁的兼容性，即加了什么样的锁，比如写锁还是读锁。内容基本来自MySQL的技术文档innodb-lock章节。感兴趣的同学可以直接阅读原文。原文地址在文末。行锁模式锁模式有：读意向锁、写意向锁、读锁、写锁和自增锁（auto_inc），我们依次来看。读写锁读锁，又称共享锁（Sharelocks，简称S锁），加读锁记录，所有事务都可以读，但不能修改，多个事务可以在记录上加读锁同时。写锁，又称排他锁（简称X锁），或独占锁，在记录上加排他锁后，只有拥有该锁的事务才能读取和修改，其他事务不能读取和修改。并且同一时间只能有一个事务加写锁。读写意向锁相互冲突是因为表锁和行锁的锁作用域不同。所以当要加表锁的时候，首先要遍历表中的所有记录，判断是否有排他锁。这种遍历检查的方法显然是一种低效的方式。MySQL引入意向锁来检测表锁和行锁之间的冲突。意向锁也是表级锁，也可以分为读意向锁（IS锁）和写意向锁（IX锁）。当一个事务要对一条记录加读锁或写锁时，必须先给表加一个意向锁。这样判断表中是否有记录锁就很简单了，看下表是否有意向锁就可以了。意向锁之间不会有冲突，也不会和AUTO_INC表锁冲突。它只会阻塞表级读锁或表级写锁。另外，意向锁不会和行锁冲突。Rowlocks只会和Rowlockconflict发生冲突。自增锁AUTOINC锁，又称自增锁（一般简称为AI锁），是当表中存在自增列（AUTOINCREMENT）时产生的表锁。当插入表中有自增列时，数据库需要自动生成自增值。它首先会在表上加一个AUTOINC表锁，以阻止其他事务的插入操作，从而保证生成的自增值必须是唯一的。AUTOINC锁有以下特点：AUTO_INC锁是互不兼容的，也就是说同一张表在同一时刻只允许有一个自增锁；自增值一旦分配就+1，如果事务回滚，自增值不会减回来。所以自增可能会中断。显然，AUTOINC表锁会降低并发插入的效率。为了提高插入的并发性，MySQL从5.1.22版本开始引入了可选的轻量级锁（mutex）机制来替代AUTOINC锁。你可以通过参数innodbautoinclockmode来灵活控制赋自增值时的并发策略。具体可以参考文末链接的MySQL在InnoDB中的AUTOINCREMENT处理。不同模式锁的兼容性矩阵下面是各种表锁之间的兼容性矩阵。总结起来有以下几点：意向锁之间不冲突；S锁只兼容S/IS锁，与其他锁有冲突；X锁与所有其他锁冲突；AI锁只兼容意向锁；行锁根据锁的粒度，锁可以细分为表锁和行锁。行锁可以根据不同的场景进一步细分，分别是Next-KeyLock、GapLock、RecordLock、记录锁、插入意向GAP。锁。不同的锁有不同的锁定位置。比如记录锁只锁对应的记录，而间隙锁锁的是记录和记录之间的间隙，Next-KeyLocks属于记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。让我们依次看看不同类型的锁。记录锁记录锁是最简单的行锁，没什么好说的。上面介绍的InnoDB加锁原理中的锁是记录锁，只锁住id=49或者name='Tom'的记录。当SQL语句不能使用索引时，会进行全表扫描。这时MySQL会对全表的所有数据行加记录锁，然后MySQLServer层对其进行过滤。但是在MySQLServer层过滤时，如果发现不满足WHERE条件，就会释放相应记录的锁。这样可以保证最后只持有满足条件的记录上的锁，但是不能省略对每条记录的加锁操作。所以更新操作必须根据索引进行。没有索引，不仅会消耗大量的锁资源，增加数据库的开销，还会大大降低数据库的并发性能。间隙锁仍然是更新用户年龄的第一个例子。如果记录id=49不存在，这条SQL语句还会加锁吗？答案是肯定的，这取决于数据库的隔离级别。在这种情况下，RC隔离级别不会加锁，在RR隔离级别id=49前后的两个索引之间加gap锁。间隙锁是在两个索引之间添加的锁，或者在第一个索引之前或最后一个索引之后的间隙上。这个间隙可以跨越一个索引记录，多个索引记录，甚至可以是空的。使用间隙锁可以防止其他事务插入或修改这个范围内的记录，保证这个范围内的记录在两次读取时不会发生变化，从而不会出现幻读。值得注意的是，间隙锁和间隙锁之间没有冲突。间隙锁的唯一作用就是防止其他事务的插入，所以加间隙S锁和加间隙X锁没有区别。Next-KeylockNext-keylock是recordlock和gaplock的组合，指的是加在一条记录和这条记录前面的gap上的锁。假设一个索引包含15,18,20,30,49,50，可能的Next-key锁如下：(-∞,15],(15,18],(18,20],(20,30],(30,49],(49,50],(50,+∞)通常我们用这种左开右闭的区间来表示Next-keylock，其中括号表示不包含记录,和方括号表示包含记录，前四个是Next-key锁，最后一个是gap锁，和gap锁一样，RC隔离级别下没有Next-key锁，只有RR隔离级别，还是之前的例子，如果id不是主键，而是二级索引，不是唯一索引，那么这条SQL会在RR隔离级别下加如下Next-key锁(30,49](49,50)这时候如果有一条id=31的记录会被阻塞。之所以锁住id=49前后的间隙，还是为了解决幻读的问题，因为id是非唯一索引，所以id=49可能有多条记录，为了防止furtherInsertarecordwithid=49.InsertintentionlockInsertintentionlock是一种特殊的gaplock（简称IIGAP），表示insert的意图，只有在INSERT时才会有这个锁。注意，这个锁虽然也叫意向锁，但是和上面介绍的表级意向锁是完全不同的两个概念，不要混淆。插入意向锁和插入意向锁之间没有冲突，所以多个事务可以同时在同一个间隙插入不同索引的记录。比如上面的例子，id=30和id=49之间有两个事务，同时插入id=32和id=33是没有问题的，虽然两个事务都会在id=30和id=50加上insertintentlock，但是不会有冲突。Insertintentlock只会与gaplock或Next-keylock发生冲突。上面说过，间隙锁的唯一作用是防止其他事务插入记录正是因为执行INSERT语句时需要加上插入意向锁，而插入意向锁与间隙锁发生冲突而造成幻读，从而阻止插入操作的执行。不同类型锁的兼容性矩阵不同类型锁的兼容性如下图所示，其中，第一行表示已有的锁，第一列表示需要添加的锁。插入意向锁比较特殊，所以先对插入意向锁做一个总结，如下：插入意向锁不影响其他事务加上其他任何锁。也就是说，一个事务已经获取了插入意向锁，对其他事务没有影响；插入意图锁与间隙锁和Next-key锁冲突。也就是说，如果一个事务要获取插入意向锁，如果其他事务已经加了间隙锁或者Next-key锁，就会被阻塞。其他类型锁的规则比较简单：间隙锁不与其他锁冲突（插入意向锁除外）；记录锁与记录锁冲突，Next-key锁与Next-key锁冲突，记录锁与Next-key锁冲突；
