A brief introduction to MySQL deadlock detection

For deadlocks, MySQL does not provide a direct variable to represent. For the 5.5 version of Performance_shcema can provide the details of the lock (but we are still 5.0), for Innodb's own monitor innodb_lock_monitor its output is always output to the error log, inconvenient to compare.

I monitor the use of Zabbix, using the agent passive way to Zabbix server to transfer data. Script for Shell, with show InnoDB status redirect

Core code:

Detect if new deadlock information is being detected:

New_deadlock () {

new_line_tile=$ (grep-n "LATEST detected deadlock" $ | cut-d ': ' F 1)

New_line_time=$ (echo "$new _line_tile + 2" | BC)

Last_dect_time= "$ (head-n $new _line_time $ | tail-n 1) # # #截取死锁发生的时间戳

[e-$] | | CP $ #拿这次输出信息, compared with last output information; If this is the first test, this output information CP is the last output information

old_line_tile=$ (grep-n "LATEST detected deadlock" $ | cut-d ': ' F 1)

If [-Z $old _line_tile];then

Echo 1

MV $ # #判断上次输出是否为死锁, if not, direct return 1 indicates that the most recent is a new deadlock. and rename this output information

Exit 1

else # #否则对比两次的时间戳

Old_line_time=$ (echo "$old _line_tile + 2" | BC)

Old_last_dect_time= "$ (head-n $old _line_time $ | tail-n 1)"

MV $ $ #输出信息为下一次检测做准备

If ["$last _dect_time" = "$old _last_dect_time"];then

Echo 0

Else

CP $1/tmp/$1_detail #已判定为死锁, need to keep the crime information

Echo 1

Fi

Fi

}

Deadlock_check () {

Case is in

1)

$MYSQL _bin-u $user-p$password-s $SOC 1-e "show engine InnoDB status\g" >/tmp/innodb_status_1_$dthm

have_dead_lock=$ (grep-c "LATEST detected deadlock"/tmp/innodb_status_1_$dthm)

#判断这次检测是否包含死锁信息, contains the words let New_dead_lock do deadlock contrast otherwise return 0

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 $SOC 2-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;;