疫情期间在家工作时，同事使用了insertintoonduplicatekeyupdate语句进行插入去重，但是在测试过程中发生了死锁现象:
ERROR1213(40001):Deadlockfoundwhentryingtogetlock;tryrestartingtransaction
表锁和行锁
我们首先来了解一下表锁和行锁：表锁是指对一整张表加锁，一般是DDL处理时使用;而行锁则是锁定某一行或者某几行，或者行与行之间的间隙。
表锁
表锁由My***实现，一般在执行DDL语句时会对整个表进行加锁，比如说ALTERTABLE等操作。在执行SQL语句时，也可以明确指定对某个表进行加锁。
mysql>locktableuserread(write);#分为读锁和写锁QueryOK,0rowsaffected(0.00sec)mysql>select*fromuserwhereid=100;#成功mysql>select*fromrolewhereid=100;#失败，未提前获取该role的读表锁mysql>updateusersetname='Tom'whereid=100;#失败，未提前获得user的写表锁mysql>unlocktables;#显示释放表锁QueryOK,0rowsaffected(0.00sec)
表锁使用的是一次性锁技术，也就是说，在会话开始的地方使用lock命令将后续需要用到的表都加上锁，在表释放前，只能访问这些加锁的表，不能访问其他表，直到最后通过unlocktables释放所有表锁。
除了使用unlocktables显示释放锁之外，会话持有其他表锁时执行locktable语句会释放会话之前持有的锁;会话持有其他表锁时执行starttransaction或者begin开启事务时，也会释放之前持有的锁。
行锁
不同存储引擎的行锁实现不同，后续没有特别说明，则行锁特指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的执行过程如下图所示。My***会根据WHERE条件读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回并加锁，接着My***发起更新改行记录的UPDATE请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有匹配的记录为止。
这种场景下的锁的释放较为复杂，有多种的优化方式，我对这块暂时还没有了解，还请知道的小伙伴在下方留言解释。
下面主要依次介绍InnoDB中锁的模式和类型，锁的类型是指锁的粒度或者锁具体加在什么地方;而锁模式描述的是锁的兼容性，也就是加的是什么锁，比如写锁或者读锁。
行锁的模式
锁的模式有：读意向锁，写意向锁，读锁，写锁和自增锁(auto_inc)，下面我们依次来看。
读写锁
读锁，又称共享锁(Sharelocks，简称S锁)，加了读锁的记录，所有的事务都可以读取，但是不能修改，并且可同时有多个事务对记录加读锁。
读写意向锁
由于表锁和行锁虽然锁定范围不同，但是会相互冲突。所以当你要加表锁时，势必要先遍历该表的所有记录，判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。
意向锁也是表级锁，也可分为读意向锁(IS锁)和写意向锁(IX锁)。当事务要在记录上加上读锁或写锁时，要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了，只要看下表上是否有意向锁就行了。
意向锁之间是不会产生冲突的，也不和AUTO_INC表锁冲突，它只会阻塞表级读锁或表级写锁，另外，意向锁也不会和行锁冲突，行锁只会和行锁冲突。
自增锁
AUTO_INC锁互不兼容，也就是说同一张表同时只允许有一个自增锁;
自增值一旦分配了就会+1，如果事务回滚，自增值也不会减回去，所以自增值可能会出现中断的情况。
显然，AUTOINC表锁会导致并发插入的效率降低，为了提高插入的并发性，MySQL从5.1.22版本开始，引入了一种可选的轻量级锁(mutex)机制来代替AUTOINC锁，可以通过参数innodbautoinclockmode来灵活控制分配自增值时的并发策略。具体可以参考MySQL的AUTOINCREMENTHandlinginInnoDB一文，链接在文末。
不同模式锁的兼容矩阵
下面是各个表锁之间的兼容矩阵。
总结起来有下面几点：
意向锁之间互不冲突;S锁只和S/IS锁兼容，和其他锁都冲突;X锁和其他所有锁都冲突;AI锁只和意向锁兼容;
行锁的类型
根据锁的粒度可以把锁细分为表锁和行锁，行锁根据场景的不同又可以进一步细分，依次为Next-KeyLock，GapLock间隙锁，RecordLock记录锁和插入意向GAP锁。
不同的锁锁定的位置是不同的，比如说记录锁只锁住对应的记录，而间隙锁锁住记录和记录之间的间隔，Next-KeyLock则所属记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。
下面我们来依次了解一下不同的类型的锁。
记录锁
记录锁是最简单的行锁，并没有什么好说的。上边描述InnoDB加锁原理中的锁就是记录锁，只锁住id=49或者name='Tom'这一条记录。
当SQL语句无法使用索引时，会进行全表扫描，这个时候MySQL会给整张表的所有数据行加记录锁，再由My***层进行过滤。但是，在My***层进行过滤的时候，如果发现不满足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的记录。
插入意向锁
插入意向锁是一种特殊的间隙锁(简写成IIGAP)表示插入的意向，只有在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锁冲突;
常见加锁场景分析
今天我们就从原理走向实战，分析常见SQL语句的加锁场景。了解了这几种场景，相信小伙伴们也能举一反三，灵活地分析真实开发过程中遇到的加锁问题。
如下图所示，数据库的隔离等级，SQL语句和当前数据库数据会共同影响该条SQL执行时数据库生成的锁模式，锁类型和锁数量。
下面，我们会首先讲解一下隔离等级、不同SQL语句和当前数据库数据对生成锁影响的基本规则，然后再依次具体SQL的加锁场景。
隔离等级对加锁的影响
MySQL的隔离等级对加锁有影响，所以在分析具体加锁场景时，首先要确定当前的隔离等级。
这里说明一下，RC总是读取记录的最新版本，而RR是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为快照读(SnapshotRead)
MySQL还提供了另一种读取方式叫当前读(CurrentRead)，它读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，又分成三种情况：
SELECT...LOCKINSHAREMODE：加共享(S)锁SELECT...FORUPDATE：加排他(X)锁INSERT/UPDATE/DELETE：加排他(X)锁
当前读在RR和RC两种隔离级别下的实现也是不一样的：RC只加记录锁，RR除了加记录锁，还会加间隙锁，用于解决幻读问题。
不同SQL语句对加锁的影响
不同的SQL语句当然会加不同的锁，总结起来主要分为五种情况：
SELECT...语句正常情况下为快照读，不加锁;SELECT...LOCKINSHAREMODE语句为当前读，加S锁;SELECT...FORUPDATE语句为当前读，加X锁;常见的DML语句(如INSERT、DELETE、UPDATE)为当前读，加X锁;常见的DDL语句(如ALTER、CREATE等)加表级锁，且这些语句为隐式提交，不能回滚。
其中，当前读的SQL语句的where从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。
当前数据对加锁的影响
SQL语句执行时数据库中的数据也会对加锁产生影响。
比如一条最简单的根据主键进行更新的SQL语句，如果主键存在，则只需要对其加记录锁，如果不存在，则需要在加间隙锁。
至于其他非唯一性索引更新或者插入时的加锁也都不同程度的受到现存数据的影响，后续我们会一一说明。
具体场景分析
我们使用下面这张book表作为实例，其中id为主键，ISBN(书号)为二级唯一索引，Author(作者)为二级非唯一索引，score(评分)无索引。
UPDATE语句加锁分析
聚簇索引，查询命中
聚簇索引就是InnoDB存储引擎下的主键索引，具体可参考《MySQL索引》。
下图展示了使用UPDATEbookSETscore=9.2WHEREID=10语句命中的情况下在RC和RR隔离等级下的加锁，两种隔离等级下没有任何区别，都是对ID=10这个索引加排他记录锁。
聚簇索引，查询未命中
下图展示了UPDATEbookSETscore=9.2WHEREID=16语句未命中时RR隔离级别下的加锁情况。
在RC隔离等级下，不需要加锁;而在RR隔离级别会在ID=16前后两个索引之间加上间隙锁。
值得注意的是，间隙锁和间隙锁之间是互不冲突的，间隙锁唯一的作用就是为了防止其他事务的插入新行，导致幻读，所以加间隙S锁和加间隙X锁没有任何区别。
二级唯一索引，查询命中
下图展示了UPDATEbookSETscore=9.2WHEREISBN='N0003'在RC和RR隔离等级下命中时的加锁情况。
在InnoDB存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁。
二级唯一索引，查询未命中
下图展示了UPDATEbookSETscore=9.2WHEREISBN='N0008'语句在RR隔离等级下未命中时的加锁情况，RC隔离等级下该语句未命中不会加锁。
因为N0008大于N0007，所以要锁住(N0007,正无穷)这段区间，而InnoDB的索引一般都使用SuprenumRecord和InfimumRecord来分别表示记录的上下边界。Infimum是比该页中任何记录都要小的值，而Supremum比该页中最大的记录值还要大，这两条记录在创建页的时候就有了，并且不会删除。
所以，在N0007和SuprenumRecord之间加了间隙锁。
为什么不在主键上也加GAP锁呢欢迎留言说出你的想法。
二级非唯一索引，查询命中
下图展示了UPDATEbookSETscore=9.2WHEREAuthor='Tom'语句在RC隔离等级下命中时的加锁情况。
我们可以看到，在RC等级下，二级唯一索引和二级非唯一索引的加锁情况是一致的，都是在涉及的二级索引和对应的主键索引上加上排他记录锁。
那为什么唯一索引不需要加间隙锁呢间隙锁的作用是为了解决幻读，防止其他事务插入相同索引值的记录，而唯一索引和主键约束都已经保证了该索引值肯定只有一条记录，所以无需加间隙锁。
需要注意的是，上图虽然画着4个记录锁，三个间隙锁，但是实际上间隙锁和它右侧的记录锁会合并成Next-Key锁。
所以实际情况有两个Next-Key锁，一个间隙锁(Tom60,正无穷)和两个记录锁。
二级非唯一索引，查询未命中
下图展示了UPDATEbookSETscore=9.2WHEREAuthor='Sarah'在RR隔离等级下未命中的加锁情况，它会在二级索引Rose和Tom之间加间隙锁。而RC隔离等级下不需要加锁。
无索引
当Where从句的条件并不使用索引时，则会对全表进行扫描，在RC隔离等级下对所有的数据加排他记录锁。在RR隔离等级下，除了给记录加锁，还会对记录和记录之间加间隙锁。和上边一样，间隙锁会和左侧的记录锁合并成Next-Key锁。
下图就是UPDATEbookSETscore=9.2WHEREscore=22语句在两种隔离等级下的加锁情况。
聚簇索引，范围查询
上面介绍的场景都是where从句的等值查询，而范围查询的加锁又是怎么样的呢我们慢慢来看。
下图是UPDATEbookSETscore=9.2WHEREID<=25在RC和RR隔离等级下的加锁情况。
RC场景下与等值查询类似，只会在涉及的ID=10，ID=18和ID=25索引上加排他记录锁。
而在RR隔离等级下则有所不同，它会加上间隙锁，和对应的记录锁合并称为Next-Key锁。除此之外，它还会在(25,30]上分别加Next-Key锁。这一点是十分特殊的，具体原因还需要再探究。
二级索引，范围查询
下图展示了UPDATEbookSETISBN=N0001WHEREscore<=7.9在RR级别下的加锁情况。
修改索引值
UPDATE语句修改索引值的情况可以分开分析，首先Where从句的加锁分析如上文所述，多了一步Set部分的加锁。
下图展示了UPDATEbookSETAuthor='John'WHEREID=10在RC和RR隔离等级下的加锁情况。除了在主键ID上进行加锁，还会对二级索引上的Bob(就值)和John(新值)上进行加锁。
DELETE语句加锁分析
一般来说，DELETE的加锁和SELECTFORUPDATE或UPDATE并没有太大的差异。
因为，在MySQL数据库中，执行DELETE语句其实并没有直接删除记录，而是在记录上打上一个删除标记，然后通过后台的一个叫做purge的线程来清理。从这一点来看，DELETE和UPDATE确实是非常相像。事实上，DELETE和UPDATE的加锁也几乎是一样的。
INSERT语句加锁分析
接下来，我们来看一下Insert语句的加锁情况。
Insert语句在两种情况下会加锁：
为了防止幻读，如果记录之间加有间隙锁，此时不能Insert;如果Insert的记录和已有记录造成唯一键冲突，此时不能Insert;
除了上述情况，Insert语句的锁都是隐式锁。隐式锁是InnoDB实现的一种延迟加锁的机制来减少加锁的数量。
隐式锁的特点是只有在可能发生冲突时才加锁，减少了锁的数量。另外，隐式锁是针对被修改的B+Tree记录，因此都是记录类型的锁，不可能是间隙锁或Next-Key类型。
具体Insert语句的加锁流程如下：
首先对插入的间隙加插入意向锁(InsertIntensionLocks)如果该间隙已被加上了间隙锁或Next-Key锁，则加锁失败进入等待;如果没有，则加锁成功，表示可以插入;然后判断插入记录是否有唯一键，如果有，则进行唯一性约束检查如果不存在相同键值，则完成插入如果存在相同键值，则判断该键值是否加锁如果没有锁，判断该记录是否被标记为删除如果标记为删除，说明事务已经提交，还没来得及purge，这时加S锁等待;如果没有标记删除，则报duplicatekey错误;如果有锁，说明该记录正在处理(新增、删除或更新)，且事务还未提交，加S锁等待;插入记录并对记录加X记录锁;