Understanding MySQL deadlock logs

Recently I became interested in understanding deadlock errors in the application at my {{dayjob}}. To get a better understanding you first need some data of what transactions actually deadlock. In order to do this I configured our database servers to log all deadlocks to the mysql error log. See innodb_print_all_deadlocks.

I can't show you the actual logs that I'm getting from our application but what I can do is expand on the excellent mysql deadlock example from the official documentation (archive.org).

I'll pick out a couple of important lines from the error log example and expand on the explanation why this caused a deadlock. The first line I want to talk about is:

Transaction 1: RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43260 lock mode S locks rec but not gap
Transaction 1: RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43260 lock_mode X locks rec but not gap waiting
Transaction 2: RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43261 lock mode S locks rec but not gap
Transaction 2: RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43261 lock_mode X locks rec but not gap waiting

We'll see 4 similar looking lines like this one in the error log, each with a slight variation. I want to highlight a couple of important bits:

lock mode S locks rec but not gap: This tells us that this transaction has acquired a shared lock
index PRIMARY of table `test`.`Birds`: This tells us that this transaction is locking the PRIMARY index of the Birds table

The second line has some variations on this:

lock_mode X locks rec but not gap waiting: This tells us that this transaction is waiting to acquire an exclusive lock
index PRIMARY of table `test`.`Animals`: This tells us that this transaction is waiting to lock the PRIMARY index of the Animals table

As we can see in the lines of Transaction 2, the inverse is happening. Transaction 2 is waiting for an exclusive lock on Birds, and currently holds a shared lock on Animals. And this is the cause of the deadlock, neither transaction can finish without the other giving up it's exclusive lock.

One final detail is that we can actually tell which physical records are the cause of the deadlock. The error logs also describe which exact records are being locked. You can find out which by looking at the lines that look like Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0.
The important bit here is the heap no 2, this tells us that we are locking record 2. It's not entirely clear to me what this 2 refers to, it's not the actual id of the row. In the example in the mysql docs this isn't really that interesting, all locks point to the same heap no 2. But I've found that in our production logs there are often more records locked and then you can determine using this number after heap no which exact records are causing your deadlock.