#!/bin/bash

user=${MYSQL_REPL_USER:-repl}
password=${MYSQL_REPL_PASSWORD:-Xjb0^DDy}
logdir=/tmp/lock_waits.log

log() {
    echo -e "$1"  | tee -a $logdir
}

exec_sql() {
    mysql -u${user} -p${password}  -N -e "$1" 2>/dev/null
}

test_conn() {
    mysql -u${user} -p${password} -e "select 1" > /dev/null 2>&1
    if [ $? -ne 0 ]; then
        echo "Error: ${host} connection failed"
        exit 1;
    fi
}

monitor_lock_waits() {
    exec_sql "SELECT blocking_pid,wait_age_secs,waiting_query FROM sys.innodb_lock_waits" > /tmp/lock_waits_temp
    lock_waits_nums=$(cat /tmp/lock_waits_temp | wc -l)
    if [ ${lock_waits_nums} -ne 0 ] ; then
        # echo "$(date '+%Y-%m-%d %H:%M:%S') Found lock waits, number: ${lock_waits_nums})"
        log "$(date '+%Y-%m-%d %H:%M:%S') 发现锁等待，锁等待数量: ${lock_waits_nums}"

        while read -r line ; do
            block_pid=$(echo "$line" | awk '{print $1}')
            wait_time=$(echo "$line" | awk '{print $2}')
            waiting_query=$(echo "$line" | awk '{$1=""; $2=""; print $0}')
            log "等待锁id: ${block_pid}, 等待时间: ${wait_time}s"
            log "等待SQL: ${waiting_query}"

            thread_id=$(exec_sql "select thread_id from performance_schema.threads where processlist_id=${block_pid}")
            log "当前持有锁事务："
            log "==================================="
            log "$(exec_sql  "select sql_text from performance_schema.events_statements_history where thread_id=${thread_id}") "
            log "==================================="
            log ""
            log ""
            log ""
        done < /tmp/lock_waits_temp
   fi
}

monitor_trx() {
    exec_sql "SELECT trx_id,trx_state,trx_started,trx_tables_in_use,trx_rows_modified,trx_query from INFORMATION_SCHEMA.INNODB_TRX" > /tmp/trx_temp
    trx_nums=$(cat /tmp/trx_temp | wc -l)
    if [ ${trx_nums} -ne 0 ] ; then
        log "$(date '+%Y-%m-%d %H:%M:%S') 当前事务情况，事务数量: ${trx_nums}"

        while read -r line ; do
            trx_id=$(echo "$line" | awk '{print $1}')
            trx_state=$(echo "$line" | awk '{print $2}')
            trx_started=$(echo "$line" | awk '{print $3 $4}')
            trx_tables_in_use=$(echo "$line" | awk '{print $5}')
            trx_rows_modified=$(echo "$line" | awk '{print $6}')
            trx_query=$(echo "$line" | awk '{$1=""; $2=""; $3=""; $4=""; $5=""; $6=""; print }')

            log "事务id: ${trx_id}, 事务状态: ${trx_state}, 事务开始时间: ${trx_started}, 事务持有锁的表: ${trx_tables_in_use}, 事务修改的行数: ${trx_rows_modified}"
            log "事务SQL: ${trx_query}"
            log ""
            log ""
            log ""
        done < /tmp/trx_temp
    fi
}


while true; do
    monitor_lock_waits
    monitor_trx
    sleep 60
done

