a script to capture information for mysql lock waiting

mysql-lock-waiting.sh

#!/bin/bash

# crontab
# * * * * * /home/wangzx/mysql-lock-wait/mysql_lock_wait_check.sh

function run_lock_wait() {
	now=`date +%s`
	seq=$(( $now / 30 % 600 ))	# rotate 600 log file in 5 hours
	logfile=~/mysql-lock-wait/log/mysql_lock_wait-$seq.log

	content=$( echo "-- 查询等待锁的语句
	select 'W' as flag, his.* from information_schema.innodb_trx trx_w
	    left join performance_schema.threads p_threads_w on p_threads_w.processlist_id = trx_w.trx_mysql_thread_id
	    left join performance_schema.events_statements_current his on his.thread_id = p_threads_w.thread_id
	where trx_w.trx_state = 'LOCK WAIT' and trx_w.trx_wait_started < current_timestamp -  interval 15 second
	union
	-- 查询占有锁的语句
	select 'B' as flag, his.* from information_schema.innodb_trx trx_w
		left join information_schema.innodb_lock_waits l_w1 on l_w1.requesting_trx_id = trx_w.trx_id
	    left join information_schema.innodb_trx trx_b on trx_b.trx_id = l_w1.blocking_trx_id
	    left join performance_schema.threads p_threads_b on p_threads_b.processlist_id = trx_b.trx_mysql_thread_id
	    left join performance_schema.events_statements_history his on his.thread_id = p_threads_b.thread_id
	where trx_w.trx_state = 'LOCK WAIT' and trx_w.trx_wait_started < current_timestamp -  interval 15 second;
	" | mysql -u**** -p**** -h**** performance_schema
	)

	if [ -z "$content" ]
	then
		echo "check time lock ok at `date -d @$now '+%F %T'`" >> ~/mysql-lock-wait/running.log
	else
		echo "check time lock failed ($seq) at `date -d @$now '+%F %T'`" >> ~/mysql-lock-wait/running.log
		date -d @$now '+%F %T' >$logfile
		echo "$content" >>$logfile
	fi
}

run_lock_wait && sleep 30 && run_lock_wait