共计 8111 个字符，预计需要花费 21 分钟才能阅读完成。
本篇文章为大家展示了 MySQL 在并发场景下的问题及解决思路是怎样的，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
1、背景
对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数据的一致性一直是数据库系统追求的目标，既要满足大量并发访问的需求又必须保证在此条件下数据的安全，为了满足这一目标大多数数据库通过锁和事务机制来实现，MySQL 数据库也不例外。尽管如此我们仍然会在业务开发过程中遇到各种各样的疑难问题，本文将以案例的方式演示常见的并发问题并分析解决思路。
2、表锁导致的慢查询的问题
首先我们看一个简单案例，根据 ID 查询一条用户信息
mysql select * from user where id=6;这个表的记录总数为 3 条，但却执行了 13 秒。
出现这种问题我们首先想到的是看看当前 MySQL 进程状态：
从进程上可以看出 select 语句是在等待一个表锁，那么这个表锁又是什么查询产生的呢？这个结果中并没有显示直接的关联关系，但我们可以推测多半是那条 update 语句产生的（因为进程中没有其他可疑的 SQL），为了印证我们的猜测，先检查一下 user 表结构：
果然 user 表使用了 MyISAM 存储引擎，MyISAM 在执行操作前会产生表锁，操作完成再自动解锁。如果操作是写操作，则表锁类型为写锁，如果操作是读操作则表锁类型为读锁。正如和你理解的一样写锁将阻塞其他操作 (包括读和写)，这使得所有操作变为串行；而读锁情况下读 - 读操作可以并行，但读 - 写操作仍然是串行。以下示例演示了显式指定了表锁（读锁），读 - 读并行，读 - 写串行的情况。
显式开启 / 关闭表锁，使用 lock table user read/write; unlock tables;
session1：
session2：
可以看到会话 1 启用表锁（读锁）执行读操作，这时会话 2 可以并行执行读操作，但写操作被阻塞。接着看：
session1：
session2：
当 session1 执行解锁后，seesion2 则立刻开始执行写操作，即读 - 写串行。
总结：
到此我们把问题的原因基本分析清楚，总结一下 mdash; mdash;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 开始提供了 online ddl 功能，允许一些 DDL 语句和 DML 语句并发，在当前 5.7 版本对 online ddl 又有了增强，这使得大部分 DDL 操作可以在线进行。详见：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
所以对于特定场景执行 DDL 过程中，DML 是否会被阻塞需要视场景而定。
总结：通过这个例子我们对元数据锁和 online ddl 有了一个基本的认识，如果我们在业务开发过程中有在线修改表结构的需求，可以参考以下方案：
1. 尽量在业务量小的时间段进行；
2. 查看官方文档，确认要做的表修改可以和 DML 并发，不会阻塞线上业务；
3. 推荐使用 percona 公司的 pt-online-schema-change 工具，该工具被官方的 online ddl 更为强大，它的基本原理是：通过 insert hellip; select hellip; 语句进行一次全量拷贝，通过触发器记录表结构变更过程中产生的增量，从而达到表结构变更的目的。
例如要对 A 表进行变更，主要步骤为：
创建目的表结构的空表，A_new;
在 A 表上创建触发器，包括增、删、改触发器;
通过 insert hellip;select hellip;limit N 语句分片拷贝数据到目的表
Copy 完成后，将 A_new 表 rename 到 A 表。
4、一个死锁问题的分析
在线上环境下死锁的问题偶有发生，死锁是因为两个或多个事务相互等待对方释放锁，导致事务永远无法终止的情况。为了分析问题，我们下面将模拟一个简单死锁的情况，然后从中总结出一些分析思路。
演示环境：MySQL5.7.20 事务隔离级别：RR
表 user：
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(300) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8下面演示事务 1、事务 2 工作的情况：
事务 1
事务 2
事务监控
T1
begin;
Query OK, 0 rows affected (0.00 sec)
begin;
Query OK, 0 rows affected (0.00 sec)
T2
select * from user where id=3 for update;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 3 | sun | 20 |
+—-+——+——+
1 row in set (0.00 sec)
select * from user where id=4 for update;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 4 | zhou | 21 |
+—-+——+——+
1 row in set (0.00 sec)
select * from information_schema.INNODB_TRX；
通过查询元数据库 innodb 事务表，监控到当前运行事务数为 2，即事务 1、事务 2。
T3
update user set name= haha where id=4;
因为 id= 4 的记录已被事务 2 加上行锁，该语句将阻塞
监控到当前运行事务数为 2。T4 阻塞状态
update user set name= hehe where id=3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
id= 3 的记录已被事务 1 加上行锁，而本事务持有 id= 4 的记录行锁，此时 InnoDB 存储引擎检查出死锁，本事务被回滚。
事务 2 被回滚，事务 1 仍在运行中，监控当前运行事务数为 1。T5
Query OK, 1 row affected (20.91 sec)
Rows matched: 1 Changed: 1 Warnings: 0
由于事务 2 被回滚，原来阻塞的 update 语句被继续执行。
监控当前运行事务数为 1。T6
commit；
Query OK, 0 rows affected (0.00 sec)
事务 1 已提交、事务 2 已回滚，监控当前运行事务数为 0。
这是一个简单的死锁场景，事务 1、事务 2 彼此等待对方释放锁，InnoDB 存储引擎检测到死锁发生，让事务 2 回滚，这使得事务 1 不再等待事务 B 的锁，从而能够继续执行。那么 InnoDB 存储引擎是如何检测到死锁的呢？为了弄明白这个问题，我们先检查此时 InnoDB 的状态：
show engine innodb status\G ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-01-14 12:17:13 0x70000f1cc000 *** (1) TRANSACTION: TRANSACTION 5120, ACTIVE 17 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 10, OS thread handle 123145556967424, query id 2764 localhost root updating update user set name= haha where id=4 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5120 lock_mode X locks rec but not gap waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 0000000013fa; asc ;; 2: len 7; hex 520000060129a6; asc R ) ;; 3: len 4; hex 68616861; asc haha;; 4: len 4; hex 80000015; asc ;; *** (2) TRANSACTION: TRANSACTION 5121, ACTIVE 12 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 11, OS thread handle 123145555853312, query id 2765 localhost root updating update user set name= hehe where id=3 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 0000000013fa; asc ;; 2: len 7; hex 520000060129a6; asc R ) ;; 3: len 4; hex 68616861; asc haha;; 4: len 4; hex 80000015; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`user` trx id 5121 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 0000000013fe; asc ;; 2: len 7; hex 5500000156012f; asc U V /;; 3: len 4; hex 68656865; asc hehe;; 4: len 4; hex 80000014; asc ;; *** WE ROLL BACK TRANSACTION (2)InnoDB 状态有很多指标，这里我们截取死锁相关的信息，可以看出 InnoDB 可以输出最近出现的死锁信息，其实很多死锁监控工具也是基于此功能开发的。
在死锁信息中，显示了两个事务等待锁的相关信息（蓝色代表事务 1、绿色代表事务 2），重点关注：WAITING FOR THIS LOCK TO BE GRANTED 和 HOLDS THE LOCK(S)。
WAITING FOR THIS LOCK TO BE GRANTED 表示当前事务正在等待的锁信息，从输出结果看出事务 1 正在等待 heap no 为 5 的行锁，事务 2 正在等待 heap no 为 7 的行锁；
HOLDS THE LOCK(S)：表示当前事务持有的锁信息，从输出结果看出事务 2 持有 heap no 为 5 行锁。
从输出结果看出，***InnoDB 回滚了事务 2。
那么 InnoDB 是如何检查出死锁的呢？
我们想到最简单方法是假如一个事务正在等待一个锁，如果等待时间超过了设定的阈值，那么该事务操作失败，这就避免了多个事务彼此长等待的情况。参数 innodb_lock_wait_timeout 正是用来设置这个锁等待时间的。
如果按照这个方法，解决死锁是需要时间的（即等待超过 innodb_lock_wait_timeout 设定的阈值），这种方法稍显被动而且影响系统性能，InnoDB 存储引擎提供一个更好的算法来解决死锁问题，wait-for graph 算法。简单的说，当出现多个事务开始彼此等待时，启用 wait-for graph 算法，该算法判定为死锁后立即回滚其中一个事务，死锁被解除。该方法的好处是：检查更为主动，等待时间短。
下面是 wait-for graph 算法的基本原理：
为了便于理解，我们把死锁看做 4 辆车彼此阻塞的场景：
4 辆车看做 4 个事务，彼此等待对方的锁，造成死锁。wait-for graph 算法原理是把事务作为节点，事务之间的锁等待关系，用有向边表示，例如事务 A 等待事务 B 的锁，就从节点 A 画一条有向边到节点 B，这样如果 A、B、C、D 构成的有向图，形成了环，则判断为死锁。这就是 wait-for graph 算法的基本原理。
总结：
1. 如果我们业务开发中出现死锁如何检查出？刚才已经介绍了通过监控 InnoDB 状态可以得出，你可以做一个小工具把死锁的记录收集起来，便于事后查看。
2. 如果出现死锁，业务系统应该如何应对？从上文我们可以看到当 InnoDB 检查出死锁后，对客户端报出一个 Deadlock found when trying to get lock; try restarting transaction 信息，并且回滚该事务，应用端需要针对该信息，做事务重启的工作，并保存现场日志事后做进一步分析，避免下次死锁的产生。
5、锁等待问题的分析
在业务开发中死锁的出现概率较小，但锁等待出现的概率较大，锁等待是因为一个事务长时间占用锁资源，而其他事务一直等待前个事务释放锁。
事务 1
事务 2
事务监控
T1
begin;
Query OK, 0 rows affected (0.00 sec)
begin;
Query OK, 0 rows affected (0.00 sec)
T2
select * from user where id=3 for update;
+—-+——+——+
| id | name | age |
+—-+——+——+
| 3 | sun | 20 |
+—-+——+——+
1 row in set (0.00 sec)
其他查询操作
select * from information_schema.INNODB_TRX；
通过查询元数据库 innodb 事务表，监控到当前运行事务数为 2，即事务 1、事务 2。
T3 其他查询操作
update user set name= hehe where id=3;
因为 id= 3 的记录被事务 1 加上行锁，所以该语句将阻塞（即锁等待）
监控到当前运行事务数为 2。T4 其他查询操作
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
锁等待时间超过阈值，操作失败。注意：此时事务 2 并没有回滚。
监控到当前运行事务数为 2。T5commit; 事务 1 已提交，事务 2 未提交，监控到当前运行事务数为 1。
从上述可知事务 1 长时间持有 id= 3 的行锁，事务 2 产生锁等待，等待时间超过 innodb_lock_wait_timeout 后操作中断，但事务并没有回滚。如果我们业务开发中遇到锁等待，不仅会影响性能，还会给你的业务流程提出挑战，因为你的业务端需要对锁等待的情况做适应的逻辑处理，是重试操作还是回滚事务。
在 MySQL 元数据表中有对事务、锁等待的信息进行收集，例如 information_schema 数据库下的 INNODB_LOCKS、INNODB_TRX、INNODB_LOCK_WAITS，你可以通过这些表观察你的业务系统锁等待的情况。你也可以用一下语句方便的查询事务和锁等待的关联关系：
SELECT r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query wating_query, b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;结果：
waiting_trx_id: 5132 waiting_thread: 11 wating_query: update user set name= hehe where id=3 blocking_trx_id: 5133 blocking_thread: 10 blocking_query: NULL总结：
1. 请对你的业务系统做锁等待的监控，这有助于你了解当前数据库锁情况，以及为你优化业务程序提供帮助；
2. 业务系统中应该对锁等待超时的情况做合适的逻辑判断。
上述内容就是 MySQL 在并发场景下的问题及解决思路是怎样的，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注丸趣 TV 行业资讯频道。
