大家好，我是小林。昨天在群里看到大家在讨论一个MySQL锁的问题，就是执行select...forupdate语句。如果查询条件没有索引字段，是加“行锁”还是“表锁”？如果你做过这个实验，你会发现，当你执行select...forupdate语句时，如果查询条件没有索引字段，则无法对整张表进行增删改查。从这个现象看，好像是表被锁了。起床，是不是因为表锁？不要急于下结论。MySQL提供了分析数据表加了哪些锁的命令。我们用这个方法看是加了什么样的锁，导致无法对整表进行增删改查。准备工作为了方便后续故事的展开，首先创建一张t_user表。表中有主键索引（id字段），其他字段不是索引字段，是普通字段。表中有如下三条记录。select语句添加什么锁？不知道大家有没有想过执行select查询语句的时候会加什么锁呢？相信大家都知道，普通的select查询（snapshotread）语句并没有加行级锁（Innodb层锁），因为它是通过MVCC技术实现的无锁查询。验证这个结论也很简单。MySQL8.0以上版本可以执行select*fromperformance_schema.data_locks\G;语句查看Innodb存储引擎向事务添加了哪些锁。假设事务a执行这个普通的select查询语句：mysql>begin;QueryOK,0rowsaffected(0.00sec)mysql>select*fromt_userwhereage<20;+----+--------+-----+------------+|编号|姓名|年龄|奖励|+----+--------+-----+------------+|1|路飞|19|3000000000|+----+-----+-----+------------+1rowinset(0.00sec)mysql>select*fromperformance_schema.data_locks\G;这条语句，输出结果如下：可以看到输出结果为空，说明普通的selectFor查询语句，Innodb存储引擎并没有为事务加任何锁。那你不加锁吗？当然不是。当我们对数据库表进行DML和DDL操作时，MySQL会在表上加MDL锁，即元数据锁。MDL锁是server层实现的表级锁，适用于所有存储引擎。对表进行增删改查（DML操作）时，加MDL读锁；当对表进行表结构改变操作（DDL操作）时，加MDL写锁；MDL之所以需要Lock，是因为在事务执行的时候不能改变表结构，否则会导致同一个事务的混乱。如果当前事务持有MDL读锁，则DDL操作无法申请MDL写锁，从而保证表元数据的数据一致性。MDL的读锁和写锁满足读写共享、读写互斥、写写互斥的关系。例如：read-readsharing：MDL读锁和MDL读锁之间不会有阻塞，即增删改查不能因为MDL读锁而阻塞，可以并发执行。如果没有，则对数据库进行串行操作；读写互斥：MDL读锁和MDL写锁互锁，即同一张表上的DML和DDL互相Block；写写互斥：MDL写锁和MDL写锁相互阻塞，即两个会话不能同时改变一张表的结构，需要串行操作；如果发现很多会话在工作执行的S??QL提示“Waitingfortablemetadatalock”。这是因为MDL读锁和写锁冲突。如果想紧急解决问题，应该考虑杀掉持有MDL锁的事务。因为MDL锁是在事务提交后释放的，也就是说在事务执行过程中一直持有MDL锁。如何查看事务是否持有MDL锁？前面我们的事务A执行了一条普通的select查询语句。如果要查看事务持有的MDL锁，可以使用此命令select*fromperformance_schema.metadata_locks;。可以看出此时事务A持有表级MDL锁，锁类型为SHARED_READ，为MDL读锁。对于增删改操作，申请的MDL锁类型是SHARED_WRITE，也属于MDL读锁，因为SHARED_WRITE和SHARED_READ的类型是相互兼容的。所以，我们常说普通查询不加锁。其实他们指的是没有Innodb的行级锁，但实际上需要持有MDL锁。select...forupdate添加什么样的锁？select...forupdate语句是一个lockingread语句，对表中的记录加了一个X型的行级锁。不同隔离级别下，行级锁的类型不同。在readcommitted隔离级别下，行级锁的唯一类型是记录锁，即只锁定一条记录。在可重复读隔离级别下，行级锁的种类包括记录锁和间隙锁（目的是为了避免幻读），所以行级锁主要分为三种：RecordLock，recordlock，即，只锁定一条记录；GapLock，间隙锁，锁定一个范围，但不包括记录本身；Next-KeyLock：RecordLock+GapLock的组合，锁定范围，锁定记录本身。行级锁加锁规则比较复杂，不同场景加锁形式也不同。加锁的对象是索引。加锁的基本单位是next-keylock，由recordlock和gaplock组成。间隔。但是，在某些情况下，next-key锁会退化为记录锁或间隙锁。场景是什么？综上所述，在使用记录锁或间隙锁可以避免幻读的场景下，next-key锁会退化为记录锁或间隙锁。这次我们只讨论，执行select...forupdate语句，如果查询条件没有索引字段，加什么锁？现在假设事务A执行了如下语句，查询条件中age不是索引字段。mysql>begin;QueryOK,0rowsaffected(0.00sec)mysql>select*fromt_userwhereage<20forupdate;+----+------------+------+------------+|编号|姓名|年龄|奖励|+----+------------+-----+-----------+|1|路飞|19|3000000000|+----+---------+-----+-----------+1rowsinset(0.00sec)此时，如果其他事务对这张表进行增删改查，就会发生阻塞。首先，事务A持有什么类型的MDL锁？您可以执行select*fromperformance_schema.metadata_locks\G;语句查看事务A当前持有的MDL锁类型。执行结果如下：可以看出此时事务A持有表级MDL锁，锁类型为SHARED_WRITE，属于MDL读锁。前面提到，事务对表进行增删改查时，事务会申请MDL读锁，MDL读锁是相互兼容的。因此，当事务A执行查询条件中没有索引字段的select...forupdate语句时，不可能因为事务A持有MDL读锁而导致其他事务无法进行增删改查操作。我们再来看看，事务A持有哪些行级锁？您可以执行select*fromperformance_schema.data_locks\G;语句查看此时事务A持有了哪些行级锁。输出如下，我删除了不必要的信息：从上图可以看出，增加了两种锁，分别是：1表级锁：X-typeintentlock（表级锁）；4行级锁：X型行级锁；什么是意向锁？在InnoDB存储引擎中，一个事务在进行锁读、插入、更新、删除操作后，需要先对表加一个“意向锁”，然后再对记录加一个“行级锁”。之所以设计“意向锁”，是为了快速判断表中是否存在行级锁。具体说明参见：MySQL全局锁、表级锁、行级锁。你想出来了吗？意向锁不会和行级锁冲突，意向锁之间也不会有冲突。意向锁只会与共享表锁（locktables...read）和独占表锁（locktables...write）发生冲突。因此，当事务A执行查询条件没有索引字段的select...forupdate语句时，不可能因为事务A持有意向锁而导致其他事务无法进行增删改查。它们是哪4个行级锁？图中LOCK_TYPE中的RECORD表示行级锁，不是记录锁：如果LOCK_MODE为X，则表示X型next-key锁；如果LOCK_MODE为X，REC_NOT_GAP，表示X型记录锁；如果LOCK_MODE是X,GAP，说明是X型间隙锁；然后通过LOCK_DATA信息，可以确定下一键锁的范围。如何确定呢？根据我的经验，如果LOCK_MODE是next-key锁或者gap锁，那么LOCK_DATA代表锁范围最右边的值，锁范围最左边的值就是LOCK_DATA上一条记录的值。因此，此时事务A在主键索引（INDEX_NAME:PRIMARY）上加了4把next-key锁，如下：X-typenext-keylocks,range:(-∞,1]X-typenext-keylocks,Range:(1,2]X-typenext-keylock,range:(2,3]X-typenext-keylock,range:(3,+∞]这相当于对整个表加锁，other事务在对表进行增删改查时会阻塞，只有事务A提交事务时，事务A执行过程中产生的锁才会被释放，为什么事务A在表中所有记录都加了X之后X型next-key锁，其他事务不能进行增删改操作吗?其他事务在执行“删除或更新操作”时也会申请X型next-key锁。next-key锁包括记录锁和间隙锁，虽然间隙锁是相互兼容的，但是记录锁之间存在X型和S型关系，即read-readsharing，读写互斥，写互斥。所以当事务A持有X型next-key锁时，其他事务无法申请X型next-key锁，导致阻塞。比如在前面的例子中，事务B在更新id=1的记录时，会申请X型记录锁（唯一索引等价操作，next-key锁会退化为记录锁），但是由于事务A持有X型next-key锁，事务B在申请X型锁时，会发生阻塞。我们也可以通过语句select*fromperformance_schema.data_locks\G;得知。这也是事务C的删除操作被阻塞的原因。事务D的insert操作阻塞的原因与事务B和事务C不同，insert语句在插入一条记录之前，需要定位这条记录在B+树中的位置。如果插入位置的下一条记录的索引上有间隙锁，如果已经加了间隙锁，此时会产生插入意向锁。然后把锁的状态设置为waiting状态，现象就是insert语句会被阻塞。事务D插入一条id=10的新记录，在主键索引树上定位插入的位置，该位置的下一条记录为supremepseudo-record，是用来标识最后一条记录的特殊记录，间隙lock（next-keylock包括gaplock）恰好持有在这条特殊记录上，所以这条insert语句会阻塞。我们也可以通过语句select*fromperformance_schema.data_locks\G;知道。为什么只查询20岁以下的行记录，并锁住整张表？这是因为事务A的lockedread查询语句没有使用索引列作为查询条件，所以扫描方式是全表扫描。行级锁是在遍历索引时加的，不是针对输出结果加的。行级锁。这不仅是由于没有索引的锁定读取查询语句造成的。如果update和delete语句的查询条件没有索引，那么因为扫描方式是全表扫描，next会在每条记录的索引上加-key锁，相当于加锁了全表。因此，在线执行update、delete、select...forupdate等加锁语句时，一定要检查语句是否使用了索引。如果是全表扫描，会在每个索引上加一个next-key锁，这相当于锁定了整个表，这是一个严重的问题。如果数据量很大，还是一样的道理吗？前面我们得出结论，如果读查询语句是加锁的，没有索引列作为查询条件，那么扫描就是全表扫描。然后，每条记录的索引都会加一个X型的next-key锁（行级锁）。也正是因为这个原因，其他事务无法对表进行增删改查。那么如果一个表的数据量超过几百万行，是否会在每条记录的索引上加一个X型的next-key锁呢？群里有小伙伴提出了这个说法，说如果MySQL认为数据量太大，会自动将行升级为表锁。不用纠结结论，我们直接做个实验。我在t_user表中插入了超过300万条记录。现在一个事务执行这条查询语句，查询条件的age字段不是索引字段。mysql>begin;QueryOK,0rowsaffected(0.00sec)mysql>select*fromt_userwhereage<20forupdate;然后，我们执行select*fromperformance_schema.data_locks\G;这条语句（我执行了很久，至少几十分钟）。可以看出，每条记录的索引都会加一个X型的next-key锁（行级锁）。因此，当MySQL认为数据量过大时，会自动将行升级为表锁，这是不准确的。总结在执行select...forupdate语句的时候，会有两种表级锁：一种是server-level表级锁：MDL锁。事务增删改查时，server层申请的MDL锁都是MDL读锁，MDL读锁之间是兼容的。MDL读锁只会与MDL写锁发生冲突。更改表结构时仅在应用MDL写锁时。一种是Inoodb层表级别的锁：意向锁。当一个事务增加、删除、修改或锁定读时，inoodb层会申请意向锁。意向锁不会和行级锁冲突，意向锁之间也不会有冲突。意向锁只会与共享表锁（locktables...read）和独占表锁（locktables...write）发生冲突。如果select...forupdate语句的查询条件没有索引字段，则无法对整张表进行增删改查。从这个现象来看，好像是表被锁了，但并不是因为上面两个表级的锁。.是因为如果读查询语句被加锁，索引列没有作为查询条件，导致全表扫描。然后，会在每条记录的索引上加一个next-key锁（行级锁），相当于锁了全表。这时候如果有其他事务对表进行增删改查，就会被锁住。堵塞。
