How to detect deadlocks in Mysql / innodb?

Question

I know that deadlocks occur inevitably when using transactions in Innodb and that they are harmless if they are treated properly by the application code - "just try it again", as the manual says.

So I was wondering - how do you detect deadlocks? Does a deadlock issue some special mysql error number? I am using PHP's mysqli extension if that matters.

Thank you.

Edit: solution found, see comments

score 20 · Answer 1 · edited Oct 20 '14 at 22:05

20

"SHOW ENGINE INNODB STATUS" from the MySQL Command line client (not a query browser) will give you info on deadlocks.

Deadlocks can also be caused by uncommitted transactions (usually program bugs) and the person who is running the uncommitted transaction will not see the problem as they will be working fine (through their data will not be committed).

edited Oct 20 '14 at 22:05

msanford

11,803
11
66
93

answered Jan 17 '11 at 22:35

boz

201
2
2

4

there is a lot of output.. can I grep for something to detect it? – OZZIE Dec 10 '18 at 13:03

score 14 · Accepted Answer · answered Mar 13 '09 at 15:04

14

http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html

1213 (ER_LOCK_DEADLOCK)

Transaction deadlock. You should rerun the transaction.

answered Mar 13 '09 at 15:04

S.Lott

384,516
81
508
779

1

@S.Lott link is broken – Farid Movsumov Apr 19 '16 at 12:08
@S.Lott google led me here.... kind of weird place to say such things ^^ when this is a place to gather such information – OZZIE Jan 04 '18 at 12:02

score 8 · Answer 3 · answered Mar 13 '09 at 15:04

8

Try MaatKit. It has a deadlock logger.

answered Mar 13 '09 at 15:04

jonstjohn

59,650
8
43
55

8

Maatkit has been absorbed into the [Percona Toolkit](http://www.percona.com/software/percona-toolkit/) recently. The tool is called [pt-deadlock-logger](http://www.percona.com/doc/percona-toolkit/pt-deadlock-logger.html) and was formerly __mk-deadlock-logger__. – Mei Oct 04 '11 at 15:29

score 5 · Answer 4 · answered Jul 25 '12 at 08:51

5

Try using MONyog. Enable MONyog's "Deadlock Monitoring" option to trace the deadlocks reported by INNODB STATUS. MONyog will send an alert to the user when a new deadlock occur. enter image description here

answered Jul 25 '12 at 08:51

Sandy

1,043
2
21
32

score 2 · Answer 5 · answered May 18 '15 at 16:43

2

If you are on a mac:

$ brew install percona-toolkit

$ pt-deadlock-logger -uroot --ask-pass localhost

answered May 18 '15 at 16:43

Andrei Sura

2,465
1
20
15

score 1 · Answer 6 · answered Jul 06 '09 at 01:58

1

Try innotop, will detect the deadlock for you.

answered Jul 06 '09 at 01:58

score 1 · Answer 7 · answered Mar 28 '19 at 13:51

I recently created a very simple check for deadlocks for the implementation of a smoke test of a web applciation. Code can be improved a lot, but it's working for now. See https://dev.mysql.com/doc/refman/8.0/en/innodb-standard-monitor.html for more info on the output of the used query below.

$status = DB::select("SHOW ENGINE INNODB STATUS")["Status"]??null;

if(strpos($status,"LATEST DETECTED DEADLOCK") !== false)
{
  trigger_error("LATEST DETECTED DEADLOCK section present in output of SHOW ENGINE INNODB STATUS");
}

if(strpos($status,"LATEST FOREIGN KEY ERROR") !== false)
{
  trigger_error("LATEST FOREIGN KEY ERROR section present in output of SHOW ENGINE INNODB STATUS");
}

How to detect deadlocks in Mysql / innodb?

7 Answers7

Linked