CertGrid CertGrid
Troubleshooting·MySQL

MySQL Locks, Blocking and Deadlocks

One transaction holds a row lock, another waits and dies with ERROR 1205. Then two transactions take the same rows in opposite order and InnoDB kills one with ERROR 1213 - and its report names both, the rows and the index.

Performance Guide 35 of 45 Advanced

REPEATABLE READ is the MySQL default and is stricter than most databases' READ COMMITTED default. It holds more locks for longer, which makes both failures here more likely than on PostgreSQL or Oracle.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

This guide includes

Use this when a statement is hanging, or dying with ERROR 1205. This matters because blocking and deadlock are different things - one waits, the other is chosen and killed - and finding who blocks whom has to happen while it is still happening.

Before you start

  1. Read the three settings that govern all of it

  2. Block one transaction behind another

  3. Find out who is blocking whom, while it is happening

  4. Cause a real deadlock

  5. Read InnoDB's own account of it

Official sources

← All MySQL guides