1.背景对于数据库系统，在多用户并发情况下提高并发性，同时保证数据的一致性一直是数据库系统追求的目标。大量并发访问的需求必须保证这种情况下数据的安全性。为了达到这个目标，大多数数据库都实现了锁和事务机制，MySQL数据库也不例外。尽管如此，我们在业务发展过程中还是遇到了各种棘手的问题。本文将以案例的形式展示常见的并发问题并分析解决方案。2.表锁导致查询慢的问题首先我们来看一个简单的案例，根据ID查询一个用户信息：mysql>select*fromuserwhereid=6;该表中的记录总数为3，但执行需要13秒。当出现这种问题时，我们首先想到的是查看当前MySQL进程状态：从进程中可以看出select语句在等待表锁，那么是什么查询导致了表锁呢？这个结果并没有直接关系，但是我们可以推测它很可能是由更新语句产生的（因为这个过程中没有其他可疑的SQL）。为了证实我们的猜测，首先查看用户表结构：果然，用户表使用了MyISAM存储引擎，MyISAM在执行操作前会生成表锁，操作完成后会自动解锁。如果操作是写操作，表锁类型是写锁，如果操作是读操作，表锁类型是读锁。正如你所理解的，写锁会阻塞其他操作（包括读取和写入），这使得所有操作串行化；在读锁的情况下，读写操作可以并行化，但读写操作仍然是串行的。以下示例演示了显式指定表锁（读锁）、读写并行和读写序列化的情况。显式启用/禁用表锁，使用locktableuserread/write；解锁表；session1:session2:可以看到session1启用表锁（读锁）进行读操作，session2可以并行进行读操作，但是Write操作被阻塞。再看：session1:session2:当session1进行解锁时，seesion2立即开始进行写操作，即读写串行。总结：至此我们对问题的原因做了一个基本的分析。综上所述，MyISAM存储引擎在执行操作时会产生表锁，影响其他用户对该表的操作。如果表锁是写锁，会导致其他用户的操作是串行的。如果是读锁，其他用户的读操作可以并行化。所以有时我们会遇到一个简单的查询需要很长时间才能看到是否是这种情况。解决方法：1）尽量不要使用MyISAM存储引擎。在MySQL8.0版本中，MyISAM存储引擎的表已经全部去掉，推荐使用InnoDB存储引擎。2）如果必须使用MyISAM存储引擎来减少写操作的时间；3、在线修改表结构有哪些风险？如果有一天业务系统需要增加一个字段的长度，是否可以直接在线修改？在回答这个问题之前，先来看一个案例：上面的语句试图修改user表的name字段的长度，语句被阻塞了。和往常一样，我们查看一下当前的流程：从流程中我们可以看到alter语句在等待一个元数据锁，而这个元数据锁很可能就是上面的select语句导致的，事实确实如此。当执行DML（select、update、delete、insert）操作时，会在表上加一个元数据锁。这个元数据锁是为了保证在查询过程中不会修改表结构，所以上面的alter语句会被阻塞。那么如果执行顺序颠倒，先执行alter语句，再执行DML语句呢？DML语句会被阻塞吗？比如我在在线环境修改表结构，在线DML语句会不会被阻塞？答案是：不确定。MySQL5.6开始提供在线ddl功能，允许部分DDL语句和DML语句并发。目前5.7版本增强了onlineddl，可以让大部分DDL操作在线进行。详见：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html所以在针对特定场景执行DDL的过程中，DML是否会被阻塞取决于在场景上。总结：通过这个例子，我们对元数据锁和在线ddl有了一个基本的了解。如果我们在业务开发过程中需要在线修改表结构，可以参考以下解决方案：1、尽量在业务量小的时间段使用2、查看官方文档，确认该表要做的修改可以和DML同时进行，不会阻塞在线业务；3、推荐使用percona公司的pt-online-schema-change工具，官方在线ddl改的最强大，它的基本原理是：通过insert...select..进行全量复制。.语句，并通过触发器记录表结构变化过程中产生的增量，从而达到表结构变化的目的。例如，改变表A，主要步骤是：创建一个目标表结构的空表，A_new；在表A上创建触发器，包括增删改触发器；通过insert...select...limitN语句分段复制数据目标表复制完成后，将A_new表重命名为A表。4.一个死锁问题的分析在线上环境中，死锁的问题时有发生。死锁是两个或多个事务相互等待对方释放锁，导致事务永远无法终止的情况。为了分析问题，下面我们模拟一个简单的死锁情况，然后从中总结出一些分析思路。演示环境：MySQL5.7.20事务隔离级别：RR表用户：CREATETABLE`USER`(`ID`INT(11)NOTNULLAUTO_INCREMENT,`NAME`VARCHAR(300)DEFAULTNULL,`AGE`INT(11)DEFAULTNULL,PRIMARYKEY(`ID`))ENGINE=INNODBAUTO_INCREMENT=5DEFAULTCHARSET=UTF8下面演示事务1和事务2的工作情况：这是一个简单的死锁场景，事务1和事务2互相等待对方释放锁，InnoDB存储引擎检测到死锁发生，让事务2回滚，使得事务1不再等待事务B的锁，从而可以继续执行。那么InnoDB存储引擎是如何检测死锁的呢？为了了解这个问题，我们先查看此时InnoDB的状态：showengineinnodbstatusG------------------------LATESTDETECTEDDEADLOCK-----------------------2018-01-1412:17:130x70000f1cc000***(1)TRANSACTION:TRANSACTION5120,ACTIVE17secstartingindexreadmysqltablesinuse1,locked1LOCKWAIT3lockstruct(s),heapsize1136,2rowlock(s)MySQLthreadid10,OSthreadhandle123145556967424,queryid2764localhostrootupdatingupdateusersetname='haha'whereid=4***(1)WAITINGFORTHISLOCKTOBEGRANTED:RECORDLOCKSspaceid94pageno3nbits80indexPRIMARYoftable`test`.`user`trxid5120lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno5PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000004;升序；；1:len6;hex0000000013fa;asc;;2:len7;hex520000060129a6;ascR);3:len4;hex68616861;aschaha;;4:len4;hex80000015;asc;;***(2)事务:TRANSACTION5121,ACTIVE1locksectable1index3readmysql,(s),heapsize1136,2rowlock(s)MySQLthreadid11,OSthreadhandle123145555853312,queryid2765localhostrootupdatingupdateusersetname='hehe'whereid=3***(2)持有锁（S）：RECORDLOCKSspaceid94pageno3nbits80indexPRIMARYoftable`test`.`user`trxid5121lock_modeXlocksrecbutnotgapRecordlock，heapno5PHYSICALRECORD：n_fields5；compactformat；infobits08；lenc0；hex1：len04；hepno5121lock_modeXlocksrecbutnotgapRecordlockhex0000000013fa;asc;;2:len7;hex520000060129a6;ascR);3:len4;hex68616861;aschaha;;4:len4;hex80000015;asc;;***(2)等待授予此锁：RECORDLOCKSspaceid94pageno`.`user`trxid5121lock_modeXlocksrecbutnotgapwaitingRecordlock,heapno7PHYSICALRECORD:n_fields5;compactformat;infobits00:len4;hex80000003;asc;;1:len6;hex0000000013fe;asc;;2:len7;hex5500000156012f;ascUV/;;3:len4;hex68656865;aschehe;;4:len4;hex80000014;asc;;***WEROLLBACKTRANSACTION(2)InnoDB状态的指标有很多。这里我们拦截死锁相关的信息。可以看到InnoDB可以输出最新的死锁信息。其实很多死锁监控工具也是基于这个功能开发的。在死锁信息中，显示了等待锁的两个事务的相关信息（蓝色代表事务1，绿色代表事务2），重点是：WAITINGFORTHISLOCKTOBEGRANTED和HOLDSTHELOCK(S)。WAITINGFORTHISLOCKTOBEGRANTED表示当前事务正在等待的锁信息。从输出结果可以看出，事务1等待的是堆号为5的行锁，事务2等待的是堆号为7的行锁；HOLDSTHELOCK(S)：表示当前事务持有的锁信息。从输出中可以看出，事务2持有5行的heapnolock。从输出可以看出，***InnoDB回滚了事务2。那么InnoDB是如何检测死锁的呢？我们认为最简单的做法是，如果一个事务在等待锁，如果等待时间超过设定的阈值，那么这个事务操作就会失败，这样就避免了多个事务互相等待很长时间的情况。参数innodb_lock_wait_timeout用于设置锁等待时间。如果按照这种方式，解决死锁需要时间（即等待超过innodb_lock_wait_timeout设置的阈值），这种方式略显被动，影响系统性能，InnoDB存储引擎提供了更好的算法来解决死锁问题，等待图算法。简单来说，就是当多个事务开始互相等待时，启用wait-forgraph算法，在判断为死锁后立即回滚其中一个事务，解除死锁。这种方式的优点是检查更主动，等待时间短。下面是等待图算法的基本原理：为了便于理解，我们把死锁看成4辆车互相堵车的场景：4辆车看成4笔交易，各自等待对方的锁，结果陷入僵局。等待图算法的原理是以事务为节点，事务之间的锁等待关系用有向边表示。比如事务A等待事务B的锁，从节点A到节点B画一条有向边，这样如果A、B、C、D组成的有向图形成环路，则判断为一个僵局。这就是等待图算法的基本原理。总结：1.如何检查我们的业务发展是否存在僵局？刚才介绍过，通过监控InnoDB的状态，可以得出结论，可以做一个小工具收集死锁记录，方便事后查看。2、如果出现死锁，业务系统应该如何应对？从上面我们可以看出，当InnoDB检测到死锁时，会报一个Deadlockfoundwhentryingtogetlock；尝试向客户端重新启动事务消息，并回滚事务。应用端需要根据这些信息重新启动事务。工作，并保存现场日志以供事后进一步分析，避免下一次死锁。5、锁等待问题分析在业务开发中，死锁的概率很小，但是锁等待的概率比较高。锁等待是因为一个事务长期占用锁资源，而其他事务一直在等待前一个事务释放锁。.由上可知，事务1长期持有id=3的行锁，事务2产生锁等待。等待时间超过innodb_lock_wait_timeout后，中断操作，但不回滚事务。如果我们在业务开发中遇到锁等待，不仅会影响性能，还会对你的业务流程带来挑战，因为你的业务需要适应锁等待的情况。逻辑处理，是重试操作还是回滚事务。MySQL元数据表中收集了事务和锁等待的信息，如information_schema数据库下的INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS。你可以通过这些表观察你的业务系统的锁等待情况。TRX_MYSQL_THREAD_IDBLOCKING_THREAD,B.TRX_QUERYBLOCKING_QUERYFROMINFORMATION_SCHEMA.INNODB_LOCK_WAITSWINNERJOININFORMATION_SCHEMA.INNODB_TRXBONB.TRX_ID=W.BLOCKING_TRX_IDINNERJOININFORMATION_SCHEMA.INNODB_TRXRONR.TRX_ID=W.REQUESTING_TRX_ID;Result:waiting_trx_id:5132waiting_thread:11wating_query:updateusersetname='hehe'whereid=3blocking_trx_id:5133blocking_thread:10blocking_query:NULLSummary:1.请监控您的业务系统是否有锁等待，这有助于您了解当前数据库锁情况，帮助您优化您的业务程序；2、业务系统应对锁等待超时情况做出适当的逻辑判断。6.小结本文通过几个简单的例子，介绍了几种常用的MySQL并发问题，并尝试提出解决这些问题的思路。本文涉及到事务、表锁、元数据锁、行锁，但是引起并发问题的远不止这些，比如事务隔离级别、GAP锁等，真正的并发问题可能很多很复杂，但是故障排除思路和方法可以重复使用。在本文中，我们使用了showprocesslist;显示引擎innodb状态；以及查询元数据表排查问题的方法。如果问题涉及Replication，还需要master/slave监控辅助。
