各位观众老爷们，晚上好！我是你们的老朋友，今天咱们来聊聊MySQL里一个让人头疼，但又不得不面对的问题：lock wait timeout。这玩意儿就像堵车，谁都不想遇到，但总也躲不开。别怕，今天咱们就来捋一捋，从事务、锁的机制，到等待链的排查，保证你听完之后，下次再遇到这玩意儿，也能淡定地把它给揪出来，修理好。
一、啥是lock wait timeout？
简单来说，lock wait timeout 就是 MySQL 给你的事务等待锁的最长时间。如果你在一个事务里，需要获取某个锁，但是这个锁被别人占着，你得等着。MySQL 怕你一直等下去，等到天荒地老，所以设置了一个超时时间。如果超过这个时间，还没拿到锁，它就会给你抛出一个 Lock wait timeout exceeded; try restarting transaction 的错误。
这就像你去饭店吃饭，前面排了很多人。饭店说：“最多给你等半个小时，半个小时后还没位置，你就走吧。”
这个超时时间，可以通过 innodb_lock_wait_timeout 这个系统变量来设置，单位是秒。
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';默认值一般是 50 秒。你可以根据自己的业务情况进行调整。
SET GLOBAL innodb_lock_wait_timeout = 30; -- 设置全局超时时间为30秒
SET SESSION innodb_lock_wait_timeout = 30; -- 设置当前会话超时时间为30秒二、事务和锁：恩怨情仇的故事
要理解 lock wait timeout，就必须先搞清楚事务和锁的关系。它们就像一对欢喜冤家，互相依赖，又互相制约。
-
事务（Transaction）： 事务是一系列操作的逻辑单元，要么全部成功，要么全部失败。它保证了数据库的 ACID 特性：
- 原子性（Atomicity）： 事务中的所有操作要么全部成功，要么全部失败，不会出现部分成功的情况。
- 一致性（Consistency）： 事务执行前后，数据库的状态必须保持一致。
- 隔离性（Isolation）： 多个事务并发执行时，每个事务都应该感觉不到其他事务的存在。
- 持久性（Durability）： 事务一旦提交，对数据库的修改就是永久性的，即使数据库崩溃也不会丢失。
-
锁（Lock）： 为了保证事务的隔离性，MySQL 引入了锁机制。锁就像一把锁，用来控制对共享资源的访问。当一个事务需要修改某个数据时，它会先给这个数据加上锁，防止其他事务同时修改它。
常见的锁类型有：
| 锁类型 | 作用 |
|---|---|
| 共享锁 (S) | 允许事务读取数据。多个事务可以同时持有同一个数据的共享锁。 |
| 排他锁 (X) | 允许事务修改数据。一个数据只能被一个事务持有排他锁。 |
| 意向共享锁 (IS) | 表级别的锁，表示事务打算在某些行上加共享锁。 |
| 意向排他锁 (IX) | 表级别的锁，表示事务打算在某些行上加排他锁。 |
| 自增锁 (AUTO-INC) | 用于自增列的锁，保证自增值的唯一性。 |
三、lock wait timeout 是怎么发生的？
lock wait timeout 的发生，通常是因为以下情况：
- 事务 A 持有了一个锁，并且长时间没有释放。
- 事务 B 想要获取这个锁，但是发现已经被事务 A 占用了，只能等待。
- 如果等待时间超过了
innodb_lock_wait_timeout的值，MySQL 就会报错。
这种情况就像：
- 事务 A：霸占着茅坑不拉屎。
- 事务 B：急着上厕所，只能在外面干瞪眼。
四、排查 lock wait timeout 的完整路径
好了，理论知识就讲到这里。下面我们来实战一下，看看如何排查 lock wait timeout 的问题。
1. 查看错误日志
首先，要查看 MySQL 的错误日志（error log），找到 Lock wait timeout exceeded 错误信息。错误日志里通常会包含一些有用的信息，比如：
- 哪个事务在等待锁？
- 哪个事务持有锁？
- 等待的是哪个表的哪个行？
错误日志的位置可以通过以下命令查看：
SHOW VARIABLES LIKE 'log_error';2. 使用 SHOW ENGINE INNODB STATUS 命令
SHOW ENGINE INNODB STATUS 命令可以提供关于 InnoDB 引擎的详细信息，包括事务、锁、等待链等等。它是一个非常强大的工具，也是我们排查 lock wait timeout 问题的主要手段。
执行 SHOW ENGINE INNODB STATUS 命令后，会输出一大堆信息。我们需要关注以下几个部分：
- LATEST FOREIGN KEY ERROR (如果存在外键约束问题)
- LATEST DETECTED DEADLOCK (如果存在死锁)
- TRANSACTIONS (当前活动的事务)
- LOCKS WAITING (正在等待锁的事务)
- LOCKS (当前持有的锁)
3. 分析 TRANSACTIONS 部分
TRANSACTIONS 部分会列出当前活动的事务，包括事务 ID、事务状态、事务开始时间、执行的 SQL 语句等等。我们需要找到等待锁的事务，并记录下它的 ID。
例如：
---TRANSACTION 2841522, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT table `test`.`t1` index `PRIMARY` for record
...这里可以看到，事务 ID 是 2841522，状态是 ACTIVE，正在等待 test.t1 表的 PRIMARY 索引上的记录锁。
4. 分析 LOCKS WAITING 部分
LOCKS WAITING 部分会列出正在等待锁的事务，以及它们等待的锁的信息。我们需要找到与等待锁的事务 ID 匹配的记录。
例如：
TABLE LOCK table `test`.`t1` trx id 2841522 lock mode IX
RECORD LOCKS space id 54 page no 35 n bits 72 index PRIMARY of table `test`.`t1` trx id 2841522 lock_mode X locks rec but not gap waiting这里可以看到，事务 ID 2841522 正在等待 test.t1 表的 PRIMARY 索引上的排他锁（X）。
5. 分析 LOCKS 部分
LOCKS 部分会列出当前持有的锁，包括锁的类型、锁的持有者等等。我们需要找到持有被等待的锁的事务。
例如：
TABLE LOCK table `test`.`t1` trx id 2841521 lock mode IX
RECORD LOCKS space id 54 page no 35 n bits 72 index PRIMARY of table `test`.`t1` trx id 2841521 lock_mode X locks rec but not gap这里可以看到，事务 ID 2841521 持有 test.t1 表的 PRIMARY 索引上的排他锁（X）。
6. 找到罪魁祸首
通过上面的分析，我们就可以找到导致 lock wait timeout 的两个事务：
- 等待锁的事务（事务 B）：ID 是 2841522
- 持有锁的事务（事务 A）：ID 是 2841521
接下来，我们需要找到这两个事务执行的 SQL 语句，看看它们到底在干什么。
7. 查询事务执行的 SQL 语句
可以使用以下方法查询事务执行的 SQL 语句：
- 查看慢查询日志（slow query log）： 如果事务执行的 SQL 语句比较慢，可能会被记录在慢查询日志中。
- 使用
performance_schema：performance_schema是 MySQL 5.6 版本引入的一个性能监控工具，可以记录各种性能指标，包括事务执行的 SQL 语句。 - 使用第三方监控工具： 很多第三方监控工具，比如 Prometheus、Grafana 等，可以监控 MySQL 的性能，并记录事务执行的 SQL 语句。
假设我们通过 performance_schema 找到了这两个事务执行的 SQL 语句：
- 事务 A (ID 2841521)：
UPDATE t1 SET c2 = 'value_a' WHERE id = 1;- 事务 B (ID 2841522)：
UPDATE t1 SET c2 = 'value_b' WHERE id = 1;8. 分析 SQL 语句
通过分析 SQL 语句，我们可以发现，这两个事务都在更新 t1 表的 id = 1 的记录。事务 A 先执行，获取了排他锁。事务 B 后执行，发现锁被占用，只能等待。由于事务 A 长时间没有释放锁，导致事务 B 等待超时。
9. 解决方案
找到问题的原因后，就可以采取相应的解决方案了。常见的解决方案包括：
- 优化 SQL 语句： 优化 SQL 语句，减少锁的持有时间。比如，尽量避免在事务中执行复杂的查询，或者使用索引来加速查询。
- 调整事务隔离级别： 降低事务隔离级别，可以减少锁的冲突。但是，降低事务隔离级别可能会导致数据不一致，需要根据实际情况进行权衡。
- 缩短事务的执行时间： 缩短事务的执行时间，可以减少锁的持有时间。比如，将一个大的事务拆分成多个小的事务。
- 增加
innodb_lock_wait_timeout的值： 增加innodb_lock_wait_timeout的值，可以延长事务等待锁的时间。但是，增加innodb_lock_wait_timeout的值可能会导致系统资源被长时间占用，需要谨慎使用。 - 手动 kill 掉持有锁的事务： 如果确定持有锁的事务不会对数据造成影响，可以手动 kill 掉它，释放锁。
在我们的例子中，可以考虑优化 SQL 语句，比如确保 id 列上有索引。如果业务允许，也可以考虑缩短事务 A 的执行时间。如果以上方法都无效，可以考虑增加 innodb_lock_wait_timeout 的值，或者手动 kill 掉事务 A。
五、死锁：更复杂的情况
除了 lock wait timeout，还有一种更复杂的情况：死锁（Deadlock）。死锁是指两个或多个事务互相等待对方释放锁，导致所有事务都无法继续执行。
MySQL 可以自动检测死锁，并在错误日志中记录相关信息。当检测到死锁时，MySQL 会选择一个事务回滚，释放锁，让其他事务继续执行。
排查死锁的方法与排查 lock wait timeout 类似，都需要使用 SHOW ENGINE INNODB STATUS 命令。在 LATEST DETECTED DEADLOCK 部分，可以找到死锁的详细信息，包括涉及的事务、锁、SQL 语句等等。
六、总结与预防
lock wait timeout 和死锁是 MySQL 中常见的并发问题。理解事务和锁的机制，掌握排查方法，可以帮助我们快速定位问题，并采取相应的解决方案。
为了预防 lock wait timeout 和死锁的发生，我们可以采取以下措施：
- 尽量使用短事务： 缩短事务的执行时间，减少锁的持有时间。
- 按照固定的顺序访问资源： 避免循环等待，减少死锁的发生。
- 使用较低的事务隔离级别： 降低事务隔离级别，可以减少锁的冲突。
- 监控 MySQL 的性能： 及时发现潜在的并发问题。
好了，今天的讲座就到这里。希望大家以后再遇到 lock wait timeout 的问题，能够不再慌张，冷静分析，最终解决问题。 记住，解决问题就像破案，找到线索，层层分析，真相总会浮出水面。感谢大家的观看！