关键词：
前几天碰到了一个MySQL数据库锁等待的问题，技术社区的这篇文章《故障分析 | MySQL锁等待超时一例分析》，正好介绍了此类问题的分析路径，值得借鉴学习。
1、问题现象
开发反馈某业务持续性报锁等待超时，相关错误信息如下，
Lock wait timeout exceeded; try restarting transaction为了能精确定位问题，继续询问开发有没有锁等待超时相关SQL，开发又给了相关报错SQL，
INSERT INTO <TABLE_NAME> VALUES(...)2、分析诊断
根据错误信息得知，单条insert语句锁等待超时，如果都是单条insert插入，不应该频繁报锁超时，似乎有点不寻常，当前数据库版本为5.6，锁等待超时参数设置时长30秒，
root@ (none)> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 30 |
+--------------------------+-------+查看慢日志及show engine innodb status\\G，发现有批量插入动作，由于自增锁竞争产生死锁，
询问开发，批量插入SQL为定时作业，查看当前innodb_autoinc_lock_mode参数设置，
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+innodb_autoinc_lock_mode=1，对于批量插入语句，需要等到语句执行结束才释放自增锁，故要解决锁等待超时，可以将参数值设置为2，但该参数为静态参数需要重启MySQL才能生效，不能重启情况下只能优化SQL执行时间，查看慢日志得知SQL执行一次需要100+秒，扫描行数86w，结果集却为0，说明SQL有优化空间，
Query_time: 108.527499 Lock_time: 0.000342 Rows_sent: 0 Rows_examined: 862584分析SQL执行计划，
SELECT *
from ( SELECT * from aa WHERE add_time >= '2022-10-01' ) a
left JOIN ( SELECT * from bb WHERE add_time >= '2022-10-01' ) b
on a.account = b.accountb and a.end_time = b.end_timeb and a.app_id = b.app_idb WHERE
b.accountb is null;
+----+-------------+----------------+-------+---------------+--------------+---------
+------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+----------------+-------+---------------+--------------+---------
+------+--------+----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL |
NULL | 2722 | NULL |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL |
NULL | 595248 | Using where; Using join buffer (Block Nested Loop) |
| 3 | DERIVED | bb | ALL | NULL | NULL | NULL |
NULL | 595248 | Using where |
| 2 | DERIVED | aa | range | idx_add_time | idx_add_time | 6 |
NULL | 2722 | Using index condition |
+----+-------------+----------------+-------+---------------+--------------+---------
+------+--------+----------------------------------------------------+
4 rows in set (0.00 sec)SQL有子查询，使用到了派生表，首先执行子查询，
( SELECT * from aa WHERE add_time >= '2022-10-01' )将结果集存入临时表derived2，然后执行子查询，
( SELECT **** from bb WHERE add_time >= '2022-10-01' )将结果集存入临时表derived3，derived2和derived3根据关联条件做表关联，使用Block Nested Loop算法，即使表chat_black(account , app_id , end_time)列有复合索引也使用不到。
如果MySQL版本是5.7的话，optimizer_switch参数会增加一个选项：derived_merge=on，满足一定条件，即子查询中没有如下条件，
Aggregate functions(SUM()、MIN()、MAX()、COUNT() and so forth)
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL
Subqueries in the select list
Assignments to user variables
Refererences only to literal values (in this case, there is no underlying table)
子查询将被合并到外层查询。
3、问题解决
知道SQL慢的原因后，对SQL进行改写，执行计划如下，
SELECT * FROM (
select * from aa where add_time >= '2022-10-01') a
left join bb b
on ( b.add_time >= '2022-10-01' and a.account = b.account and a.end_time =
b.end_time and a.app_id = b.app_id)
where b.account is null;
+----+-------------+----------------+-------+------------------------+----------------
--------+---------+-------------------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------+-------+------------------------+----------------
--------+---------+-------------------------------+------+-----------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL
| NULL | NULL | 3096 | NULL |
| 1 | PRIMARY | b | ref | idx_ac_app_id_end_time |
idx_ac_app_id_end_time | 1542 | a.account,a.app_id,a.end_time | 1 | Using where
|
| 2 | DERIVED | aa | range | idx_add_time | idx_add_time
| 6 | NULL | 3096 | Using index condition |
+----+-------------+----------------+-------+------------------------+----------------
--------+---------+-------------------------------+------+---------------------执行时间从原来的100+秒降低不到1秒，
root@ xsj_chat_filter> SELECT count(*) FROM (
-> select * from aa where add_time >= '2022-10-01') a
-> left join bb b
-> on ( b.add_time >= '2022-10-01' and a.account = b.account and a.end_time
= b.end_time and a.app_id = b.app_id)
-> where b.account is null;
+----------+
| count(*) |
+----------+
| 23 |
+----------+
1 row in set (0.65 sec)执行时间短了，自然就不存在自增锁等待超时了。
因此，针对这个案例，现象是出现了SQL锁等待，除了改参数，临时缓解，最根本的还是找到最耗时的SQL，结合执行计划，找到执行慢的主要矛盾，通过改写等方式，提高执行速度，进而水到渠成地解决锁等待的问题。
不仅仅是这种锁等待的问题，任何的技术问题，排查和解决路径都会有一定的套路，提升自身的水平，很重要的一点，就是这种探索问题本质的能力，没什么葵花宝典，靠的就是积累，多碰问题，多思考，多积累解决问题的方案，由点及面地增加自己的知识，让其更系统，当碰到相近问题，举一反三，融会贯通，上升一个境界，达到真正的提高。
如果您认为这篇文章有些帮助，还请不吝点下文章末尾的"点赞"和"在看"，或者直接转发pyq，
近期更新的文章：
近期的热文：
文章分类和索引：
更新查询超过了Mysql锁等待超时
】更新查询超过了Mysql锁等待超时【英文标题】：Mysqllockwaittimeoutexceededonupdatequery【发布时间】：2015-10-1321:19:24【问题描述】：在生产服务器的数据库中，调度程序每天运行一个程序，在该程序中我很少有删除插入和更新语句。... 查看详情
超时分布式事务处理等待锁解决办法
--使用SYSassysdba用户角色登录--查找被死锁的语句selectsql_textfromv$sqlwherehash_valuein(selectsql_hash_valuefromv$sessionwheresidin(selectsession_idfromv$locked_object));--查找被死锁的进程：SELECTs.username,l.OBJECT_ID 查看详情
mysql存储过程出现锁表锁行的情况怎么解决
行锁的等待在介绍如何解决行锁等待问题前，先简单介绍下这类问题产生的原因。产生原因简述：当多个事务同时去操作（增删改）某一行数据的时候，MySQL为了维护ACID特性，就会用锁的形式来防止多个事务同时操作某一行数据... 查看详情
mysql怎么样修改transactiondeadlockdetectiontimeout
参考技术A1、锁等待超时。是当前事务在等待其它事务释放锁资源造成的。可以找出锁资源竞争的表和语句，优化你的SQL，创建索引等，如果还是不行，可以适当减少并发线程数。2、你的事务在等待给某个表加锁时超时了，估计... 查看详情
使用模型工厂的 MySQL 锁定等待超时
】使用模型工厂的MySQL锁定等待超时【英文标题】：MySQLLockWaitTimeoutusingModelFactories【发布时间】：2016-07-2401:00:39【问题描述】：我在测试类的方法中生成了一个非常小的数据集，我曾尝试在setUp()方法中生成数据，但它导致每个测... 查看详情
超时分布式事务处理等待锁解决办法
--使用SYSassysdba用户角色登录--查找被死锁的语句selectsql_textfromv$sqlwherehash_valuein(selectsql_hash_valuefromv$sessionwheresidin(selectsession_idfromv$locked_object));--查找被死锁的进程：SELECTs.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_... 查看详情
mysql基础知识(代码片段)
...查询。还有一种解决方式，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说 查看详情
mysql事务没有提交导致锁等待lockwaittimeoutexceeded
Lockwaittimeoutexceeded当一个SQL执行完了，但未COMMIT，后面的SQL想要执行就是被锁，超时结束；select*frominformation_schema.innodb_trx 之后找到了一个一直没有提交的只读事务，kill到了对应的线程后ok了。mysql>select*fromi... 查看详情
解决一次mysql死锁问题
...近一次的死锁信息showengineinnodbstatus显示如下发现事务1在等待一个锁事务2也在等待一个锁而且事物2持有了事物1需要的锁关于锁的描述，出现了lock_mode，gapbeforerec，insertintention等字眼，看不懂说明了什么？说明我关于mysql的锁相... 查看详情
mysql事务没有提交导致锁等待lockwaittimeoutexceeded异常
异常：Lockwaittimeoutexceeded;tryrestartingtransaction解决办法：(需要数据库最高权限)执行select*frominformation_schema.innodb_trx之后找到了一个一直没有提交的只读事务，找到对应的线程后，执行killthreadid，再确认一直没有提交的只读事物被干... 查看详情
mysql行锁等待异常
...waits参数一直是6，查看processlist和innodb_trx中均未发现有锁等待的情况，这是怎么回事根据我之前接触到的此类问题，大致可以分为以下几种原因：1.程序中非数据库交互操作导致事务挂起将接口调用或者文件操作等这一类非数据... 查看详情
MySQL 集群错误：超过锁定等待超时
】MySQL集群错误：超过锁定等待超时【英文标题】：MySQLClusterError:Lockwaittimeoutexceeded【发布时间】：2013-09-0609:37:00【问题描述】：我试图将一个约200G的文件加载到具有4个数据节点的MySQL集群中，而我的目标表的DDL是这样的：CREATET... 查看详情
mysql提示lockwaittimeoutexceeded解决办法
...InnoDB 表类型的时候,默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s,因为有的锁等待超过了这个时间,所以抱错.你可以把这个时间加长,或 查看详情
mysql中innodb引擎的行锁是通过加在啥上完成
参考技术A行锁的等待在介绍如何解决行锁等待问题前，先简单介绍下这类问题产生的原因。产生原因简述：当多个事务同时去操作（增删改）某一行数据的时候，MySQL为了维护ACID特性，就会用锁的形式来防止多个事务同时操作... 查看详情
3分钟理解mysql所有timeout的值
...接（sleep）状态超时：interactive_timeout和wait_timeout3.服务端等待读取客户端发来的数据超时：net_read_timeout4.服务端等待把数据写给客户端（客户端没有接收）的超时：net_write_timeout5.事务等待获取行锁（rowlock）超时，返回应用失败... 查看详情
zookeeper分布式锁简单实践(代码片段)
...成功，谁就能够获取到锁。没有创建成功节点，就会进行等待，当释放锁的时候，采用事件通知给客户端重新获取锁资源。如果请求超时直接返回给客户端超时，重新请求即可。代码实现为了更好的展现效果，我这 查看详情
由cobar引起的mysql锁问题(代码片段)
...底层Mysql层面捕捉到了锁，并且多个session长时间的在等待该锁直到超时（锁超时时间是50s）。此时的Cobar完全就处于一个近似于僵死的状态。当时的场景是一个接口的并发调用，这个并发调用会更新同一个人的账... 查看详情
由cobar引起的mysql锁问题(代码片段)
...底层Mysql层面捕捉到了锁，并且多个session长时间的在等待该锁直到超时（锁超时时间是50s）。此时的Cobar完全就处于一个近似于僵死的状态。当时的场景是一个接口的并发调用，这个并发调用会更新同一个人的账... 查看详情