MySQL Deadlock Detection

MySQL does not provide direct variables for deadlock. The performance_shcema after version 5.5 can provide detailed information about the lock (but we still have 5.0)

MySQL does not provide direct variables for deadlock. The performance_shcema after version 5.5 can provide detailed information about the lock (but we still have 5.0)

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 the Innodb_lock_monitor monitor that comes with InnoDB, its output 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.