MySql事务未提交导致锁等待如何解决？

chengcheng222e

发布于 2021-11-19 16:00:17

3.6K0

发布于 2021-11-19 16:00:17

文章被收录于专栏：简栈文化

背景

我们来先看一个图，了解一下故(事)事(故)的背景：

http://static.cyblogs.com/Jietu20211113-162059.jpg

有2个跑批任务，其实做的事情是同一件事情，都是为了跟下游系统保持数据的一致性。大任务是每隔2h跑一次，小任务是每隔10mins跑一次。除了这2个定时任务以外，还有一个额外的监控任务来做类似的对账，如果发现出现对账不平，就会出现邮件/短信告警到相关的责任上。

这是一个非常有特点的定时任务跑批任务+监控告警的场景了。

从上面的场景上看，我们可以得出一些结论：为了保证一致性写了大小Job来保证，并且还给出了监控告警，说明数据的重要性是比较强的。

某天，出现了频繁的告警提示，每10分钟就告警一次，而且内容没有发生变化，说明同步的index没有变化过。

错误排查

任务有在正常的执行吗？

第一反应肯定是在思考，我的大任务与小任务都有正常执行吗？因为之前的都是正常的。看了一下日志与进程发现有在跑，除了多次任务，日志打印不明确，看不到具体分支的逻辑。总结一下问题点：

任务很忌讳出现上一个任务没有跑完，下一个任务又继续开启一个新的任务，给服务器带来了不少的压力。一般如果对接了好的分布式调度能力，基本也很容易解决这个问题。
关键分支日志打印不明确，导致定位很难

先修复上面2个问题，短时间对接一个新的分布式调度时间上不可能，只能简单的改shell脚本让其不执行。

d=`date`

count=`ps -ef |grep {jobKeyword} |grep -v grep | wc -l`
if [ $count -lt 1 ]; then
  echo "$d : do {jobKeyword} . " >> /data/{projectName}/sync.log
  python xxxxx.py
else
  echo "$d : {jobKeyword} no finished, this time do nothing. " >> /data/{projectName}/sync.log
fi

然后在重点的地方添加上日志，其实这些操作都是一些非常简单，但是可以带来明显效果的步骤。反正，我基本都是如此的去做的，你什么信息都拿不到，你根本无法入手。

部署上去后，发现每次在insert into一条数据的时候，日志就卡住了，结合代码确定，确定就是insert into的时候，数据库没有返回，而其他的表以及其他数据的都是可以正常操作的。

出现了LOCK WAIT

第一反应，就是看下这条SQL现在是一个什么状态？我们可以利用SHOW PROCESSLIST看下

select * from information_schema.PROCESSLIST t;

SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ;

然后就发现该SQL语句的trx_state=LOCK WAIT，那说明没有获取到锁。那我们具体如何推断是谁没有释放锁了？

# 第1步:
SELECT * FROM information_schema.innodb_locks ;

# 第2步：1，2没有特别的先后顺序，之后为了确定trx_requested_lock_id以及是谁获取了锁lock_trx_id + local_data
SELECT * FROM information_schema.innodb_trx t where t.trx_state = 'LOCK WAIT';

# 第3步：找到对应的执行语句
select * from information_schema.PROCESSLIST t where t.id = {lock_trx_id}

可惜，那条语句已经是sleep的状态了，无法看到具体的SQL。在这里可以推断，就是有一条SQL在对数据{local_data}操作的时候获取了一把锁，但是因为事务未提交，导致后面的SQL再对{local_data}操作的时候要获取锁，无法获取到。理论上获取不到锁，一会儿也会释放掉报错出来。通过查询innodb_lock_wait_timeout=7200，默认值应该是50。

解决掉问题

到这一步就很明确了，就是让未提交事务的SQL结束掉，或者提交掉。此时只有kill掉这个进程的选项了。执行：

kill {lock_trx_id};

再执行就立马发现数据没有了，获取到了锁。

SELECT * FROM information_schema.innodb_trx t where t.trx_state = 'LOCK WAIT';

总结一下

innodb_lock_wait_timeout 设置不合理，时间太久了
出现获取不到锁的场景，需要告警到邮件、手机上来。
大任务与小任务的时间要搓开，出现这种情况也是对同一行数据进行X操作并且未释放锁导致的。把事务的时间搞短一点。可以每次都去获取连接，也不要一次连接执行很长时间。

实验性操作

就直接看脚本好了

http://static.cyblogs.com/Jietu20211113-171928.jpg

当右边的事务对同一条数据进行X操作的时候，它是要获取锁的。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这个时候可以去看下锁的表

select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
|lock_id         | lock_trx_id | lock_mode| lock_type | lock_table    | lock_index| lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
|757082:3279:3:2 | 757082      | X         | RECORD    | `test`.`test` | PRIMARY    |      3279 |         3 |       2 | 1         |
|757081:3279:3:2 | 757081      | X         | RECORD    | `test`.`test` | PRIMARY    |      3279 |         3 |        2 | 1         |
+-----------------+-------------+-----------+-----------+---------------+------------+------------+-----------+----------+-----------+
2 rowsin set, 1 warning (0.00 sec)

查看一下设置的超时时间

show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
|Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout|  50  |
+--------------------------+-------+

看关于事务的描述

show engine innodb status

查看当前的事务

mysql> show processlist;
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db                 | Command | Time   | State                  | Info             |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL               | Daemon  | 286110 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | test               | Sleep   |    956 |                        | NULL             |
|  9 | root            | localhost | test               | Sleep   |    754 |                        | NULL             |
| 10 | root            | localhost | information_schema | Query   |      0 | init                   | show processlist |
+----+-----------------+-----------+--------------------+---------+--------+------------------------+------------------+
4 rows in set (0.01 sec)