mysql“空事务”引发的锁阻塞

原创金同学 2024-06-27

247

日常运维中，innodb_trx视图中经常会出现running时间很长的事务，但是并没有显示sql语句，很多DBA对这类问题很困惑。

比如上图中，show processlist 查看到ID对应的session一直存在，status状态时而running时而 sleep，最后分析发现，应用程序使用 begin、start transction 启动了一个大事务，不停的轮询更新数据，大事务运行时间超过 1 天。

如何追踪定位？

一般情况下，应用程序通过begin执行显示事务后，事务中的sql已经执行，但是该显示事务一直没有提交时就会出现这种现象。下面sql语句会查询到“空事务”中执行的上一条sql语句，拿到sql后找开发一起排查sql来源，最后尝试复现找到根因。

select trx_id,trx_operation_state,trx_mysql_thread_id prs_id,now(),
trx_started,to_seconds(now())-to_seconds(trx_started) trx_es_time,
user,db,host,command,state,Time,info current_sql,PROCESSLIST_INFO 
last_sql,t4.ROWS_AFFECTED 'ROWS_AFFECTED(last)',t4.ROWS_SENT as 'ROWS_SENT(last)' 
,t4.ROWS_EXAMINED as 'ROWS_EXAMINED(last)',t1.trx_rows_locked,t1.trx_rows_modified 
from information_schema.innodb_trx t1,information_schema.processlist t2,performance_schema.threads  
t3,performance_schema.events_statements_current t4 where t1.trx_mysql_thread_id=t2.id  and   
t1.trx_mysql_thread_id=t3.PROCESSLIST_ID and t1.trx_mysql_thread_id!=connection_id() and   
t3.THREAD_ID = t4.THREAD_ID and to_seconds(now())-to_seconds(trx_started) >= 5;
复制

看一个案例

下面案例就是“空事务”引发的锁阻塞。

#############正在锁的事务#############

mysql>SELECT lock_id,lock_trx_id,lock_mode,lock_table,lock_index,lock_data FROM INFORMATION_SCHEMA.INNODB_LOCKS;
lock_id lock_trx_id     lock_mode       lock_table      lock_index      lock_data
517490031:70:548240:33  517490031       X       `campaign_prod`.`reward_campaign_instance`      PRIMARY 64404684
517487888:70:548240:33  517487888       X       `campaign_prod`.`reward_campaign_instance`      PRIMARY 64404684

#############锁等待##################
mysql>SELECT * FROM information_schema.innodb_lock_waits;
requesting_trx_id       requested_lock_id       blocking_trx_id blocking_lock_id
517490031       517490031:70:548240:33  517487888       517487888:70:548240:33

#############运行中的事务#############
mysql>SELECT trx_id,trx_state,trx_started,trx_mysql_thread_id,trx_query,trx_operation_state  FROM information_schema.INNODB_TRX;
trx_id  trx_state       trx_started     trx_mysql_thread_id     trx_query       trx_operation_state
517490355       RUNNING 2023-11-24 16:19:12     63065298        NULL    NULL
517490031       LOCK WAIT       2023-11-24 16:19:03     63022117        UPDATE reward_campaign_instance  SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,last_updated_by = 'admin',last_updated_by_name = 'admin',last_update_date = '2023-11-24 16:19:03.162000',last_accrual_date = '2023-11-24 16:19:03.160000',accrual_amount_total = 3937.700 WHERE  id = 64404684 AND OBJECT_VERSION_NUMBER = 3       starting index read
517489792       RUNNING 2023-11-24 16:18:44     63065302        NULL    NULL
517487888       RUNNING 2023-11-24 16:16:38     63065211        NULL    NULL
复制

分析

通过innodb_lock_waits看到，517490031被517487888堵塞
通过innodb_locks看到，517490031 和517487888 都想要修改主键为64404684的行记录
查看innodb_trx，517490031被堵塞，状态为lock wait，能看到是一个单表更新
查看innodb_trx，517487888是一个 RUNNING 的显示事务，应该是没有提交，才导致后面的事务被堵。

517487888应该为delete或者update语句，在操作主键为64404684的行记录时，加了X锁，但是由于“显示事务”没有提交，导致后面的517490031事务的update等待X锁。

“空事务”可能出现的原因

1> 正常业务逻辑中显示事务，本身执行时间很长，导致影响的行比较多；
2> 服务异常导致显示事务未提交，比如业务逻辑中需要用到activemq，但由于集群故障导致发送mq消息阻塞，导致mysql事务执行超时。
3> 第三方接口响应超时，导致事务运行时间过长，表面看起来就是innodb_trx下出现很多空事务；