mysql主键更新被锁_MySQL的加锁处理，你都了解的⼀清⼆
楚了吗？
MySQL加锁分析，⼀直是⼀个⽐较困难的话题。
我在⼯作过程中，经常会有同事咨询这⽅⾯的问题。本⽂，准备就MySQL加锁问题，展开较为深⼊的分析与讨论，主要是介绍⼀种思路，运⽤此思路，拿到任何⼀条SQL语句，都能完整的分析出这条语句会加什么锁？会有什么样的使⽤风险？甚⾄是分析线上的⼀个死锁场景，了解死锁产⽣的原因。了解了这⼏种场景，相信⼩伙伴们也能举⼀反三，灵活地分析真实开发过程中遇到的加锁问题。
如下图所⽰，数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执⾏时数据库⽣成的锁模式，锁类型和锁数量。
下⾯，我们会⾸先讲解⼀下隔离等级、不同 SQL 语句 和 当前数据库数据对⽣成锁影响的基本规则，然后再依次具体 SQL 的加锁场景。
隔离等级对加锁的影响
MySQL 的隔离等级对加锁有影响，所以在分析具体加锁场景时，⾸先要确定当前的隔离等级。读未提交
(Read Uncommitted 后续简称RU)：可以读到未提交的读，基本上不会使⽤该隔离等级，所以暂时忽略。
读已提交(Read Committed 后续简称 RC)：存在幻读问题，对当前读获取的数据加记录锁。
可重复读(Repeatable Read 后续简称 RR)：不存在幻读问题，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防⽌新的数据插⼊，导致幻读。
序列化(Serializable)：从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降，不建议使⽤。
这⾥说明⼀下，RC 总是读取记录的最新版本，⽽ RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读(Snapshot Read)
MySQL 还提供了另⼀种读取⽅式叫当前读(Current Read)，它读的不再是数据的快照版本，⽽是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，⼜分成三种情况：SELECT ... LOCK IN SHARE MODE：加共享(S)锁
SELECT ... FOR UPDATE：加排他(X)锁
INSERT / UPDATE / DELETE：加排他(X)锁
当前读在 RR 和 RC 两种隔离级别下的实现也是不⼀样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，⽤于解决幻读问题。
不同 SQL 语句对加锁的影响
不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况：SELECT ... 语句正常情况下为快照读，不加锁；
SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；
SELECT ... FOR UPDATE 语句为当前读，加 X 锁；
常见的 DML 语句(如 INSERT、DELETE、UPDATE)为当前读，加 X 锁；
常见的 DDL 语句(如 ALTER、CREATE 等)加表级锁，且这些语句为隐式提交，不能回滚。
其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使⽤索引，索引是否是唯⼀索引等等。
当前数据对加锁的影响
SQL 语句执⾏时数据库中的数据也会对加锁产⽣影响。
⽐如⼀条最简单的根据主键进⾏更新的 SQL 语句，如果主键存在，则只需要对其加记录锁，如果不存在，则需要在加间隙锁。
MySQL是⼀个⽀持插件式存储引擎的数据库系统。本⽂下⾯的所有介绍，都是基于InnoDB存储引擎，其他引擎的表现，会有较⼤的区别。
MVCC：Snapshot Read vs Current Read
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最⼤的好处，相信也是⽿熟能详：读不加锁，读写不冲突。在读多写少的OLTP应⽤中，读写不冲突是⾮常重要的，极⼤的增加了系统的并发性能，这也是为什么现阶段，⼏乎所有的RDBMS，都⽀持了MVCC。
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不⽤加锁。当前读，读取的是记录的最新版本，
并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在⼀个⽀持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作⼜是当前读呢？以MySQL InnoDB为例：
快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下⾯会分析)select * from table where ?;
当前读：特殊的读操作，插⼊/更新/删除操作，属于当前读，需要加锁。
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第⼀条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
为什么将 插⼊/更新/删除 操作，都归为当前读？可以看看下⾯这个 更新 操作，在数据库中的执⾏流程：
从图中，可以看到，⼀个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第⼀条满⾜条件的记录，然后InnoDB引擎会将第⼀条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起⼀个Update请求，更新这条记录。⼀条记录操作完成，再读取下⼀条记录，直⾄没有满⾜条件的记录为⽌。因此，Update操作内部，就包含了⼀个当前读。同理，Delete操作也⼀样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进⾏⼀个当前读。
注：根据上图的交互，针对⼀条当前读的SQL语句，InnoDB与MySQL Server的交互，是⼀条⼀条进⾏的，因此，加锁也是⼀条⼀条进⾏的。先对⼀条满⾜条件的记录加锁，返回给MySQL Server，做⼀些DML操作；然后在读取下⼀条加锁，直⾄读取完毕。
Cluster Index：聚簇索引
InnoDB存储引擎的数据组织⽅式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列。关于聚簇索引表的组织⽅式，可以参考MySQL的官⽅⽂档：Clustered and Secondary Indexes 。本⽂假设读者对这个，已经有了⼀定的认识，就不再做具体的介绍。接下来的
部分，主键索引/聚簇索引 两个名称，会有⼀些混⽤，望读者知晓。
2PL：Two-Phase Locking
传统RDBMS加锁的⼀个原则，就是2PL (⼆阶段锁)：Two-Phase Locking。相对⽽⾔，2PL⽐较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下⾯，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。
从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。
Isolation Level
隔离级别：Isolation Level，也是RDBMS的⼀个关键特性。相信对数据库有所了解的朋友，对于4种隔离级别：Read
Uncommited，Read Committed，Repeatable Read，Serializable，都有了深⼊的认识。本⽂不打算讨论数据库理论中，是如何定义这4种隔离级别的含义的，⽽是跟⼤家介绍⼀下MySQL/InnoDB是如何定义这4种隔离级别的。
MySQL/InnoDB定义的4种隔离级别：
Read Uncommited
可以读取未提交记录。此隔离级别，不会使⽤，忽略。
Read Committed (RC)
快照读忽略，本⽂不考虑。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
Repeatable Read (RR)
快照读忽略，本⽂不考虑。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满⾜查询条件的记录不能够插⼊ (间隙锁)，不存在幻读现象。
Serializable
从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使⽤。
⼀条简单SQL的加锁实现分析
在介绍完⼀些背景知识之后，本⽂接下来将选择⼏个有代表性的例⼦，来详细分析MySQL的加锁处理。当然，还是从最简单的例⼦说起。经常有朋友发给我⼀个SQL，然后问我，这个SQL加什么锁？就如同下⾯两条简单的SQL，他们加什么锁？
SQL1：select * from t1 where id = 10;
SQL2：delete from t1 where id = 10;
针对这个问题，该怎么回答？我能想象到的⼀个答案是：
SQL1：不加锁。因为MySQL是使⽤多版本并发控制的，读不加锁。
SQL2：对id = 10的记录加写锁 (⾛主键索引)。
这个答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不⾜，这个问题没有答案。如果让我来回答这个问题，我必须还要知道以下的⼀些前提，前提不同，我能给出的答案也就不同。要回答这个问题，还缺少哪些前提条件？
前提⼀：id列是不是主键？
前提⼆：当前系统的隔离级别是什么？
前提三：id列如果不是主键，那么id列上有索引吗？
前提四：id列上如果有⼆级索引，那么这个索引是唯⼀索引吗？
前提五：两个SQL的执⾏计划是什么？索引扫描？全表扫描？
没有这些前提，直接就给定⼀条SQL，然后问这个SQL会加什么锁，都是很业余的表现。⽽当这些问题有了明确的答案之后，给定的SQL 会加什么锁，也就⼀⽬了然。下⾯，我将这些问题的答案进⾏组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁？
注：下⾯的这些组合，我做了⼀个前提假设，也就是有索引时，执⾏计划⼀定会选择使⽤索引进⾏过滤 (索引扫描)。但实际情况会复杂很多，真正的执⾏计划，还是需要根据MySQL输出的为准。
组合⼀：id列是主键，RC隔离级别
组合⼆：id列是⼆级唯⼀索引，RC隔离级别
组合三：id列是⼆级⾮唯⼀索引，RC隔离级别
组合四：id列上没有索引，RC隔离级别
组合五：id列是主键，RR隔离级别
组合六：id列是⼆级唯⼀索引，RR隔离级别
组合七：id列是⼆级⾮唯⼀索引，RR隔离级别
组合⼋：id列上没有索引，RR隔离级别
组合九：Serializable隔离级别
排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另⼀个⾓度来说，只要你选定了⼀种组合，SQL需要加哪些锁，其实也就确定了。接下来，就让我们来逐个分析这9种组合下的SQL加锁策略。
注：在前⾯⼋种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采⽤的是快照读，因此在下⾯的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。
组合⼀：id主键+RC
这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。如下图所⽰：
结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。
组合⼆：id唯⼀索引+RC
这个组合，id不是主键，⽽是⼀个Unique的⼆级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：
此组合中，id是unique索引，⽽主键是name列。此时，加锁的情况由于组合⼀有所不同。由于id是unique索引，因此delete语句会选择⾛id列的索引进⾏where条件的过滤，在到id=10的记录后，⾸先会将unique索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁？试想⼀下，如果并发的⼀个SQL，是通过主键索引来更新：update t1 set id = 100 where name = ‘d’; 此时，如果delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同⼀记录上的更新/删除需要串⾏执⾏的约束。
结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，⼀个对应于id unique索引上的id = 10的记录，另⼀把锁对应于聚簇索引上的[name=’d’,id=10]的记录。
组合三：id⾮唯⼀索引+RC
相对于组合⼀、⼆，组合三⼜发⽣了变化，隔离级别仍旧是RC不变，但是id列上的约束⼜降低了，id列不再唯⼀，只有⼀个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进⾏过滤where条件，那么此时会持有哪些锁？同样见下图：
根据此图，可以看到，⾸先，id列索引上，满⾜id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合⼆唯⼀的区别在于，组合⼆最多只有⼀个满⾜等值查询的记录，⽽组合三会将所有满⾜查询条件的记录都加锁。
结论：若id列上有⾮唯⼀索引，那么对应的所有满⾜SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
组合四：id⽆索引+RC
相对于前⾯三个组合，这是⼀个⽐较特殊的情况。id列上没有索引，where id = 10；这个过滤条件，没法通过索引进⾏过滤，那么只能⾛全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有⼈说会在表上加X 锁；有⼈说会将聚簇索引上，选择出来的id = 10；的记录加上X锁。那么实际情况呢？请看下图：
由于id列上没有索引，因此只能⾛聚簇索引，进⾏全部扫描。从图中可以看到，满⾜删除条件的记录
有两条，但是，聚簇索引上所有的记录，都被加上了X锁。⽆论记录是否满⾜条件，全部被加上X锁。既不是加表锁，也不是在满⾜条件的记录上加⾏锁。
有⼈可能会问？为什么不是只在满⾜条件的记录上加锁呢？这是由于MySQL的实现决定的。如果⼀个条件⽆法通过索引快速过滤，那么存储引擎层⾯就会将所有记录加锁后返回，然后由MySQL Server层进⾏过滤。因此也就把所有的记录，都锁上了。mysql下载不了怎么办
注：在实际的实现中，MySQL有⼀些改进，在MySQL Server过滤条件，发现不满⾜后，会调⽤unlock_row⽅法，把不满⾜条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满⾜条件记录上的锁，但是每条记录的加锁操作还是不能省略的。
结论：若id列上没有索引，SQL会⾛聚簇索引的全扫描进⾏过滤，由于过滤是由MySQL Server层⾯进⾏的。因此每条记录，⽆论是否满⾜条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满⾜条件的记录，会在判断后放锁，最终持有的，是满⾜条件的记录上的锁，但是不满⾜条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。
组合五：id主键+RR
上⾯的四个组合，都是在Read Committed隔离级别下的加锁⾏为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁⾏为。
组合五，id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10; 这条SQL，加锁与组合⼀：[id主键，Read Committed]⼀致。
组合六：id唯⼀索引+RR
与组合五类似，组合六的加锁，与组合⼆：[ id唯⼀索引，Read Committed ]⼀致。两个X锁，id唯⼀索引满⾜条件的记录上⼀个，对应的聚簇索引上的记录⼀个。
组合七：id⾮唯⼀索引+RR
还记得前⾯提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，⽽RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁⾏为⼜是与RC下的加锁⾏为完全⼀致。那么RR隔离级别下，如何防⽌幻读呢？问题的答案，就在组合七中揭晓。
组合七，Repeatable Read隔离级别，id上有⼀个⾮唯⼀索引，执⾏delete from t1 where id = 10; 假设选择id列上的索引进⾏条件过滤，最后的加锁⾏为，是怎么样的呢？同样看下⾯这幅图：
此图，相对于组合三：[ id列上⾮唯⼀锁，Read Committed ]看似相同，其实却有很⼤的区别。最⼤的区别在于，这幅图中多了⼀个GAP 锁，⽽且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何⽤？
其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本⾝，⽽是两条记录之间的GAP。所谓幻读，就是同⼀个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量⼀致，记录本⾝也⼀致)，第⼆次的当前读，不会⽐第⼀次返回更多的记录 (幻象)。
如何保证两次当前读返回⼀致的记录，那就需要在第⼀次当前读与第⼆次当前读之间，其他的事务不会插⼊新的满⾜条件的记录并提交。为了实现这个功能，GAP锁应运⽽⽣。
如图中所⽰，有哪些位置可以插⼊新的满⾜条件的项 (id = 10)，考虑到B+树索引的有序性，满⾜条件的项⼀定是连续存放的。记录[6,c]之前，不会插⼊id=10的记录；[6,c]与[10,b]间可以插⼊[10, aa]；[10,b]与[10,d]间，可以插⼊新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插⼊满⾜条件的[10,e],[10,z]等；⽽[11,f]之后也不会插⼊满⾜条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插⼊新的满⾜条件的记录，MySQL选择了⽤GAP锁，将这三个GAP给锁起来。
Insert操作，如insert [10,aa]，⾸先会定位到[6,c]与[10,b]间，然后在插⼊前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert 不能插⼊记录。因此，通过第⼀遍的当前读，不仅将满⾜条件的记录锁上 (X锁)，与组合三类似。同时还是增加3把GAP锁，将可能插⼊满⾜条件记录的3个GAP给锁上，
保证后续的Insert不能插⼊新的id=10的记录，也就杜绝了同⼀事务的第⼆次当前读，出现幻象的情况。
有⼼的朋友看到这⼉，可以会问：既然防⽌幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？
⾸先，这是⼀个好问题。其次，回答这个问题，也很简单。GAP锁的⽬的，是为了防⽌同⼀事务的两次当前读，出现幻读的情况。⽽组合五，id是主键；组合六，id是unique键，都能够保证唯⼀性。⼀个等值查询，最多只能返回⼀条记录，⽽且新的相同取值的记录，⼀定不会在新插⼊进来，因此也就避免了GAP锁的使⽤。其实，针对此问题，还有⼀个更深⼊的问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第⼀次查询，没有到满⾜查询条件的记录，那么GAP锁是否还能够省略？此问题留给⼤家思考。
结论：Repeatable Read隔离级别下，id列上有⼀个⾮唯⼀索引，对应SQL：delete from t1 where id = 10; ⾸先，通过id索引定位到第⼀条满⾜查询条件的记录，加记录上的X锁，加GAP上的GAP锁，然后加主键聚簇索引上的记录X锁，然后返回；然后读取下⼀条，重复进⾏。直⾄进⾏到第⼀条不满⾜条件的记录[11,f]，此时，不需要加记录X锁，但是仍旧需要加GAP锁，最后返回结束。
组合⼋：id⽆索引+RR
组合⼋，Repeatable Read隔离级别下的最后⼀种情况，id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进⾏全表扫描。最终的加锁情况，如下图所⽰：
如图，这是⼀个很恐怖的现象。⾸先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个⽰例表，只有6条记录，⼀共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？
在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发SQL，均不能执⾏，不能更新，不能删除，不能插⼊，全表被锁死。
当然，跟组合四：[ id⽆索引, Read Committed ]类似，这个情况下，MySQL也做了⼀些优化，就是所谓的semi-consistent read。semi-consistent read开启的情况下，对于不满⾜查询条件的记录，MySQL会提前放锁。针对上⾯的这个⽤例，就是除了记录[d,10]，[g,10]之外，所有的记录锁都会被释放，同时不加GAP锁。semi-consistent read如何触发：要么是read committed隔离级别；要么是Repeatable Read隔离级别，同时设置了innodb_locks_unsafe_for_binlog 参数。更详细的关于semi-consistent read的介绍，可参考我之前的⼀篇博客：MySQL+InnoDB semi-consitent read原理及实现分析 。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。