MySql事务未提交导致锁等待如何解决?
背景
我们来先看一个图,了解一下故(事)事(故)的背景:
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)
参考地址
- https://blog.51cto.com/corasql/1923427
如果大家喜欢我的文章,可以关注个人订阅号。欢迎随时留言、交流。如果想加入微信群的话一起讨论的话,请加管理员微信号:chengcheng222e
,他会拉你们进群。
简栈文化服务订阅号
相关文章
- 从本体论开始说起——运营商关系图谱的构建及应用
- 如何成为一名数据科学家?
- 从未见过的堂兄杀了人,你的DNA是关键证据
- 20个安全可靠的免费数据源,各领域数据任你挑
- 20个安全可靠的免费数据源,各领域数据任你挑
- 阿里云李飞飞:All in Cloud时代,云原生数据库优势明显
- 基于Hadoop生态系统的一高性能数据存储格式CarbonData(性能篇)
- 大数据告诉你:10年漫威,到底有多少角色
- TigerGraph:实时图数据库助力金融风控升级
- Splunk利用Splunk Connected Experiences和Splunk Business Flow 扩大数据访问
- 大数据开发常见的9种数据分析手段
- 以免在景区看人,我爬了5W条全国景点门票数据...
- 【实战解析】基于HBase的大数据存储在京东的应用场景
- 数据科学家告诉你哪些计算机科学书籍是你应该看的
- Kafka作为大数据的核心技术,你了解多少?
- Spring Boot 整合 Redis 实现缓存操作
- 大数据学习必须掌握的五大核心技术有哪些?
- 基于Antlr在Apache Flink中实现监控规则DSL化的探索实践
- 甲骨文再次被Gartner评为分析型数据管理解决方案魔力象限领导者
- 爬取吴亦凡微博102118条转发数据,扒一扒流量的真假