本文转载自微信公众号《码虫》，作者码虫。转载本文请联系CodeBugBeetle公众号。背景当数据库中的多个操作需要修改同一个数据时，不可避免地会出现数据脏读的情况。这时候数据库就需要有很好的并发控制能力，这一切都是由MySQL中的服务器和存储引擎来实现的。解决并发问题最有效的办法就是引入锁机制。锁从功能上分为共享锁和排它锁，通常称为读锁和写锁；锁的粒度分为行锁和表锁，表级锁MySQL中表级锁有两种：一种是表锁，一种是元数据锁（metadatalock，MDL）行锁typeNext-KeyLock：锁定一个范围，并锁定在记录本上；GapLock：间隙锁，锁定一个范围，但不包括记录簿；RecordLock：锁定单行记录；锁定规则MySQL虽然有各种锁，但有一些基本的锁定原则是不变的，例如：快照读取不加锁，更新语句必须独占锁定，RC隔离级别没有间隙锁等等。这些规则组织如下：lockSELECT...statementofcommonstatementsisasnapshotreadinnormalcircumstances,withoutlocking;SELECT...LOCKINSHAREMODE语句为当前读，加了S锁；SELECT...FORUPDATE语句为当前读取添加X锁；普通DML语句（如INSERT、DELETE、UPDATE）为当前读加X锁；普通的DDL语句（如ALTER、CREATE等）添加表级锁，并且这些语句是隐式的Commit，不能回滚；tablelocktablelock(SlockandXlock)intentlock(ISlockandIXlock)自增锁（一般见不到，只有在innodb_autoinc_lock_mode=0orBulkinserts时才有可能）行锁分析行锁都加在索引上,最终会落在聚簇索引上；添加行锁的过程一个一个添加；锁冲突S锁与S锁兼容，X锁与X锁冲突，X锁与S锁冲突；不同隔离级别下的锁说SELECT...语句是正常情况下的快照读，没有锁；但是在Serializable隔离级别下，是当前读，加了一个S锁；RC隔离级别下没有Gap锁和Next-key锁的SQL锁分析我们以下面的students表为例，其中id为主键，no(学号)为二级唯一索引，name(姓名)和age（年龄）是次要的Non-uniqueindex，score（信用）没有指标。我们只分析最简单的一种SQL，只包含一个WHERE条件，相等查询或范围查询。SQL虽然很简单，但是对于不同类型的列，我们还是会面临各种情况：聚簇索引，查询命中：UPDATEstudentsSETscore=100WHEREid=15;聚簇索引，查询未命中：UPDATEstudentsSETscore=100WHEREid=16;二级非唯一索引，查询命中：UPDATEstudentsSETscore=100WHEREname='Tom';辅助非唯一索引，查询未命中：更新学生SETscore=100WHEREname='John';无索引：更新学生SETscore=100WHEREscore=22;聚簇索引，范围查询：UPDATEstudentsSETscore=100WHEREid<=20;二级索引，范围查询：UPDATEstudentsSETscore=100WHEREage<=23;聚簇索引，查询命中语句UPDATEstudentsSETscore=100WHEREid=15RC和RR隔离级别下加锁情况相同，对id的聚簇索引加X锁，如下：ClusteringIndex,查询未命中如果查询未命中记录，在RC和RR的隔离级别下加锁是不同的，因为RR有GAP锁。语句UPDATEstudentsSETscore=100WHEREid=16在RC和RR隔离级别下加锁如下（RC不加锁）：secondarynon-uniqueindex，queryhit如果查询命中secondarynon-uniqueindex，在下RR隔离级别，GAP锁也会加进去。语句UPDATEstudentsSETscore=100WHEREname='Tom'被加锁如下：数一下右上图中的锁，你可能会认为总共加了7把锁，但实际情况并非如此，而且它需要注意的是(Tom,37)上的记录锁和前一个GAP锁的组合是Next-key锁。这个锁被添加到索引（Tom，37）上，并且在（Tom，49）上也有一个Next-key锁。那么最右边的GAP锁在哪里呢？右边没有记录。实际上，在InnoDB存储引擎中，每个数据页中有两条虚拟行记录，用于限制记录的边界。它们是：InfimumRecord和SupremeRecord。Infimum小于页面中的任何记录。value，而Supreme大于页面中的最大记录值，这两条记录在页面创建时就存在，不会被删除。右上角的GAP锁是加在SupremeRecord上的。所以右上图有2个Next-key锁，1个GAP锁，2个record锁，一共5个锁。二级非唯一索引，查询未命中如果查询未命中记录，RR隔离级别会加GAP锁，RC没有锁。语句UPDATEstudentsSETscore=100WHEREname='John'加锁如下：如果没有索引，如果WHERE条件不能使用索引，MySQL会如何加锁呢？有人说会给表加X锁，也有人说会根据WHERE条件，过滤的记录在聚簇索引上加X锁。看下图：当没有索引时，只能使用聚簇索引对表中的记录进行全表扫描。RC隔离级别下，所有记录都会加行锁。在RR隔离级别下，不仅会在所有记录上加行锁，还会在所有聚簇索引与聚簇索引之间加GAP锁。语句UPDATEstudentsSETscore=100WHEREscore=22虽然满足条件的记录只有一条，但是聚簇索引上的所有记录都用X加锁。那么，为什么不只加锁满足条件的记录呢？这是由MySQL的实现决定的。如果一个条件不能通过索引快速过滤，那么存储引擎层会锁定所有的记录并返回，然后MySQLServer层会过滤，所以所有的记录都会被锁定。聚簇索引、范围查询上面介绍的各种情况其实都是很常见的SQL。它们有一个特点：都只有一个WHERE条件，而且都是等价查询。那么问题来了，如果不是等值查询而是范围查询，那么加锁的情况会怎样呢？SQL语句是UPDATEstudentsSETscore=100WHEREid<=20，按理说我们只需要设置id=20,18,15三个记录就可以锁定，但是看右图，下RR隔离级别，我们同样锁定了id=30的记录和(20,30)之间的间隙，显然这是一个Next-key锁。二级索引，范围查询然后我们将范围查询应用于二级非唯一索引SQL语句为：UPDATEstudentsSETscore=100WHEREage<=23加锁情况如下图所示：可以看到和聚簇索引的范围查询是一样的，除了加锁记录在WHERE条件范围内，下一条记录也会被Next-key锁定，这里有意思的地方在于，虽然有两条满足age=24的记录，但是只有第一条被锁定，第二条没有被锁定，并且第一个和第二个之间没有锁元数据锁元数据锁（metadatalock,MDL)不需要显式使用，它会在访问表时自动添加。MDL的作用是保证读写的正确性。想象一下，如果一个查询是在遍历一个表中的数据，而另一个线程在执行过程中对表结构进行了更改，删除了一个列，那么查询线程得到的结果与表结构不匹配，肯定是行不通的。因此，在MySQL5.5中引入了MDL。对表进行增删改查时，加MDL读锁；当你想改变表的结构时，添加MDL写锁。读锁不是互斥的，所以多个线程可以同时对一个表进行增删改查。读写锁之间，写锁之间是互斥的，用来保证改变表结构操作的安全性。因此，如果两个线程同时要向一张表中添加字段，则其中一个线程必须等到另一个线程执行完毕后才能开始执行。MDL锁虽然是系统默认会添加的，但它是一个你不能忽视的机制。比如下面这个例子，经常看到有人掉进这个坑：给一个小表加一个字段导致整个库挂掉。要知道对于一个表添加字段，或者修改字段，或者增加索引，都需要扫描全表的数据。操作大表时，需要特别注意避免影响在线服务。事实上，即使是小的表操作也会有问题。我们来看下面的操作顺序，假设表t2是一个小表。注：这里的实验环境是MySQL5.7SessionaSessionbSessioncSessiondbegin;select*fromt2limit1;select*fromt2limit1;altertablet2addfint;(blocked)select*fromt2limit1;(blocked)we可以看到先启动sessionA，此时会对表t加一个MDL读锁。由于sessionB也需要MDL读锁，sessionC正常执行后会阻塞，因为sessionA的MDL读锁还没有释放，而sessionC需要MDL写锁，所以只能阻塞。如果只有sessionC本身被阻塞是没有关系的，但是后续所有对表t申请MDL读锁的请求也会被sessionC阻塞。我们前面说过，所有对表t的增删改查表需要先申请MDL读锁，然后全部上锁，即表完全不可读。如果某个表的查询语句比较频繁，客户端有重试机制，也就是说超时后要重新启动一个新的session，这个库的线程很快就会满。解决长事务，如果事务没有提交，会一直占用MDL锁；OnlineDDL的流程如下：1.取MDL写锁2.降级为MDL读锁3.做真正的DDL4.升级为MDL写锁5.释放MDL锁1,2,4,5执行时间很长short如果没有锁冲突。第三步占用了DDL的大部分时间。在此期间，表可以正常读写数据，所以称为“上线”。我们上面的例子在第一步就被阻塞了，无法获取到MDL写锁。在MySQL中使用createtable2asselect*fromtable1时，table1的mdl锁在语句执行完成之前不会释放，table1的写操作会因为一直持有S锁而阻塞；insertintotable1select*fromtable2在MySQL中使用insertinto当table1select*fromtable2时，table2会被锁住。这种加锁分为以下几种情况：1.没有查询条件，也没有排序方法insertintotable1select*fromtable2:此时MySQL在逐行加锁，每一行都加锁2.查询使用了primarykeytosortinsertintotable1select*fromtable2orderbyid：使用主键排序时，MySQL逐行加锁，每行加锁3.使用非主键排序insertintotable1select*fromtable2orderbymodified_date：当使用非主键排序时，MySQL会锁住整张表4.查询条件中使用非主键过滤条件，insertintotable1select*fromtable2wheremodified_date>='2017-10-0100:00:00'当使用非主键过滤条件时，MySQL逐行加锁，每一行都加锁结论：这种句型造成写阻塞建议：select...outfile不会阻塞dml操作，可以用select...intooutfile和loaddata组合infile来替换insert...select完成插入操作。在实际场景中，如何避免资源争抢锁，使SELECT尽可能快，尽量减少大而复杂的查询，将复杂的查询逐步拆分成若干个小的查询；尽可能创建足够高效的索引，让数据检索更快；使用EXPLAINSELECT确保查询中使用的索引符合预期DeadlockCaseCase1SessionaSessionbupdatestudentssetscore=100whereid=20;pdatestudentssetscore=100whereid=30;updatestudentssetscore=100whereid=30;更新学生集score=100whereid=20;死锁的根本原因是两个或多个事务之间的加锁顺序不一致。这个死锁案例其实是最经典的死锁场景。首先，事务A获取id=20的锁（lock_modeX锁rec但不锁gap），事务B获取id=30的锁；然后，事务A尝试获取id=30的锁，而这个锁已经被事务B持有，所以事务A等待事务B释放锁，然后事务B尝试获取id=20的锁，即被事务A占用，所以两个事务互相等待，导致死锁。Case2SessionaSessionbupdatestudentssetscore=100whereid<30;更新studentssetscore=100whereage>23;在这种情况下，每个事务只有一条SQL语句，但可能会造成死锁问题。其实这个deadlock和case1没什么区别，只是需要理解的深一点。要知道在范围查询中，加锁是一条一条记录一条条记录，所以虽然只有一条SQL语句，但是如果两条SQL语句的加锁顺序不同，也会导致死锁。情况1，事务A的加锁顺序是：id=20->30，事务B的加锁顺序是：id=30->20，刚好相反，所以会导致死锁。这里的场景是一样的。事务A的范围条件为id<30，加锁顺序为：id=15->18->20。事务B使用二级索引age，加锁顺序为：(age,id)=(24,18)->(24,20)->(25,15)->(25,49)，其中id的锁序列为id=18->20->15->49。可以看出事务A先锁15再锁18，而事务B先锁18再锁15，这样就形成了死锁。如何避免死锁如上面的案例一和案例二所示，加锁索引的顺序不一致可能会导致死锁，所以如果可能的话，尽量以相同的顺序访问索引记录和表。程序在批量处理数据时，如果提前对数据进行排序，保证每个线程按照固定的顺序处理记录，也可以大大降低死锁的可能性；给表加一个合理的索引，如果不使用索引，表的每一行都会被锁住，死锁的概率会大大增加；为避免大额交易，尽量将大额交易拆分成多个小额交易进行处理；因为大事务占用资源多，耗时长，与其他事务不同，发生冲突的概率也会增加；避免运行多个同时读写同一张表的脚本，特别注意加锁和操作数据量大的语句；我们经常有一些定时脚本来避免同时运行它们。时间点操作；设置锁等待超时参数：innodb_lock_wait_timeout（默认50s），这个参数不仅仅用来解决死锁问题，在并发访问比较高的情况下，如果大量事务不能立即获取到需要的锁而挂起，会占用大量的计算机资源，造成严重的性能问题，甚至会拖累整个数据库。我们可以通过设置合适的锁等待超时阈值来避免这种情况的发生。
