MySQL Deadlock Detection

MySQL does not provide direct variables for deadlock. For Versions later than Version 5.5, performance_shcema can provide detailed information about the lock (but we are still 5.0). For Innodb_lock_monitor, the output of innodb_monitor is always output to the error log, which is inconvenient for comparison.

I use zabbix for monitoring and send data to zabbix server passively using agent. The script is shell and uses show innodb status redirection.

Core code:

Check for new deadlock information:

New_deadlock (){
New_line_tile = $ (grep-n "latest detected deadlock" $1 | cut-d': '-f 1)
New_line_time = $ (echo "$ new_line_tile + 2" | bc)
Last_dect_time = "$ (head-n $ new_line_time $1 | tail-n 1) ### capture the timestamp of a deadlock

[-E $2] | cp $1 $2 # Compare the output information with the previous one. If this is the first check, use the output information cp as the previous output information.
 
Old_line_tile = $ (grep-n "latest detected deadlock" $2 | cut-d': '-f 1)
 
If [-z $ old_line_tile]; then
Echo 1
Mv $1 $2 # determine whether the last output is a deadlock. If not, 1 is returned directly, indicating that the last deadlock was a new one. Rename the output information.
Exit 1
Else ## otherwise, compare the two timestamps
Old_line_time = $ (echo "$ old_line_tile + 2" | bc)
Old_last_dect_time = "$ (head-n $ old_line_time $2 | tail-n 1 )"
Mv $1 $2 # prepare for next Detection
 
If ["$ last_dect_time" = "$ old_last_dect_time"]; then
Echo 0
Else
Cp $1/tmp/$ deadlock detail # It has been determined to be a deadlock and the crime information needs to be retained
Echo 1
Fi
Fi
}

Deadlock_check (){
Case $1 in
1)
$ MYSQL_BIN-u $ user-p $ password-S $ SOC1-e "show engine innodb status \ G">/tmp/innodb_status_1 _ $ dthm
Have_dead_lock = $ (grep-c "latest detected deadlock"/tmp/innodb_status_1 _ $ dthm)
# Determine whether the detection contains the deadlock information. If the deadlock information is included, use New_dead_lock to compare deadlocks. Otherwise, 0 is returned.
If [$ have_dead_lock-gt 0]; then
New_deadlock/tmp/innodb_status_1 _ $ dthm/tmp/innodb_status_1 _ $ dt
Else
Echo 0
Fi ;;
2)
$ MYSQL_BIN-u $ user-p $ password-S $ SOC2-e "show engine innodb status \ G">/tmp/innodb_status_2 _ $ dthm
Have_dead_lock = $ (grep-c "latest detected deadlock"/tmp/innodb_status_2 _ $ dthm)
If [$ have_dead_lock-gt 0]; then
New_deadlock/tmp/innodb_status_2 _ $ dthm/tmp/innodb_status_2 _ $ dt
Else
Echo 0
Fi ;;
3)
$ MYSQL_BIN-u $ user-p $ password-S $ SOC3-e "show engine innodb status \ G">/tmp/innodb_status_3 _ $ dthm
Have_dead_lock = $ (grep-c "latest detected deadlock"/tmp/innodb_status_3 _ $ dthm)
If [$ have_dead_lock-gt 0]; then
New_deadlock/tmp/innodb_status_3 _ $ dthm/tmp/innodb_status_3 _ $ dt
Else
Echo 0
Fi ;;
4)
$ MYSQL_BIN-u $ user-p $ password-S $ SOC4-e "show engine innodb status \ G">/tmp/innodb_status_4 _ $ dthm
Have_dead_lock = $ (grep-c "latest detected deadlock"/tmp/innodb_status_4 _ $ dthm)
If [$ have_dead_lock-gt 0]; then
New_deadlock/tmp/innodb_status_4 _ $ dthm/tmp/innodb_status_4 _ $ dt
Else
Echo 0
Fi ;;
*)
Echo $ ERROR
Exit 1 ;;
Esac
}

The variables in case $1 are used to transmit socket parameters for multiple instances on different machines.