大事务造成线上insert插入缓慢

大事务造成线上insert插入缓慢

问题描述

最近有用户反馈，线上很多订单下单特别慢，每天集中在早上9-10点钟，我跟踪生产tomcat日志，发现一段insert sql执行时间大概要60s的样子。怀疑应该是生产有大事务执行导致表被锁了。

解决方法

使用监控脚本轮询查找LOCK WAIT状态的sql，并记录这个时候processlist中运行过久的sql，来定位到底是哪条sql造成的。

#!/bin/sh
while(true)
do
    echo "Begin" >> lock.sql
    #查询LOCK WAIT状态sql
    mysql -uroot -p'密码' -P3306 -h192.168.1.100 -e"select trx_started, concat(trx_query,'\n') from information_schema.innodb_trx where trx_state='LOCK WAIT' and now()-trx_started>=10" | grep -v "trx_query"    > /app/lockwait/lockwaitsql`date +%Y%m%d`.txt

    if [ -s /app/lockwait/lockwaitsql`date +%Y%m%d`.txt ]; then
    cat /app/lockwait/lockwaitsql`date +%Y%m%d`.txt >> lock.sql
    fi
    
    #查看LOCK WAIT状态时，线上正在执行的代码
    mysql -uroot -p'密码' -P3306 -h192.168.1.100 -e"select concat(USER,';',info,'\n') from information_schema.processlist where info is not null and time>=10" > /app/lockwait/slowquery`date +%Y%m%d`.txt
    
    if [ -s /app/lockwait/slowquery`date +%Y%m%d`.txt ]; then
    cat /app/lockwait/slowquery`date +%Y%m%d`.txt >> lock.sql
    fi
    
    	echo "End" >> lock.sql
    	sleep 10

done

通过上面这个方法，我们已经快速的找到了造成线上insert慢的sql，然后通过执行计划分析如下，发现这条sql用了trstatus=1的S锁,插入trastatus=1的数据时候，就会造成X锁等待。

解决方案：调整select的执行计划，不走trastatus字段索引