MySQL raises ER_LOCKING_SERVICE_DEADLOCK (error 3132, SQLSTATE HY000) when two sessions hold metadata locks that block each other, creating a deadlock in the internal locking service.
ER_LOCKING_SERVICE_DEADLOCK is a MySQL metadata-lock deadlock. It appears when two sessions wait on each other’s metadata locks. Release or kill one blocker, retry the statement, and tune lock wait timeouts to resolve the conflict.
ER_LOCKING_SERVICE_DEADLOCK
MySQL throws error 3132 with SQLSTATE HY000 when its internal locking service detects a metadata-lock deadlock. Two or more sessions hold locks on table or schema objects and simultaneously request conflicting locks, so neither statement can proceed.
Because the conflict happens at the locking-service layer, a simple row lock query like SHOW ENGINE INNODB STATUS will not reveal the problem. MySQL breaks the tie by aborting one participant and returns ER_LOCKING_SERVICE_DEADLOCK to the victim session.
The error usually appears during DDL or START TRANSACTION WITH CONSISTENT SNAPSHOT statements that need a metadata lock on tables already grabbed by another session performing long reads or writes.
Short lock wait timeouts, missing indexes that keep readers open too long, and poorly ordered DDL in deployment scripts create the perfect storm for a deadlock inside the locking service.
First, identify blocking sessions with SHOW PROCESSLIST or performance_schema. Kill or complete one blocker to free its metadata lock and let the other statement continue.
If the workload allows, wrap DDL in ALGORITHM=INPLACE statements or use LOCK=NONE to reduce locking scope. Increasing lock_wait_timeout can also give MySQL more time to resolve contention before declaring a deadlock.
A deployment script executes ALTER TABLE while a long-running SELECT holds a shared MDL on the same table. Stop the SELECT, apply the DDL, and retry.
An online backup tool issues FLUSH TABLES WITH READ LOCK and blocks an application thread that then requests another lock it held earlier. Coordinate backup windows and isolate critical locks to avoid circular waits.
Run DDL during low-traffic windows, keep transactions short, and always access tables in a consistent order. Proper indexing speeds up queries and prevents long metadata lock holds.
Galaxy’s query history helps teams locate slow or blocking statements quickly. Endorsed queries reduce ad-hoc scripts that might grab unnecessary locks.
ER_LOCK_WAIT_TIMEOUT (error 1205) signals that a transaction waited too long for a lock but no circular wait occurred. Solutions overlap: tune timeouts and shorten transactions.
ER_LOCK_DEADLOCK (error 1213) is a row-level deadlock inside InnoDB rather than the locking service. Analyze SHOW ENGINE INNODB STATUS to find conflicting key locks.
Large analytical queries keep shared metadata locks open for minutes, blocking DDL that needs exclusive access and causing a deadlock chain.
Two deployment threads altering the same object can each hold a partial lock and request the other, resulting in an intractable circular wait.
Statements like FLUSH TABLES WITH READ LOCK or LOCK TABLES FOR BACKUP require broad locks and easily collide with normal workload locks.
Different parts of the code base access tables A then B, while others access B then A, creating opposing lock acquisition orders.
Occurs when a statement waits longer than lock_wait_timeout for a metadata or row lock without a circular dependency.
Signals a row-level deadlock inside InnoDB, not the metadata locking service.
MySQL fails to refresh its metadata lock within lock durations, typically due to heavy system load.
Yes. MySQL aborts the victim statement and rolls back its current transaction to free locks.
No. MDL is integral to MySQL. Focus on keeping transactions short and ordering statements consistently.
They are separate subsystems. ER_LOCKING_SERVICE_DEADLOCK involves metadata locks; ER_LOCK_DEADLOCK covers row locks inside InnoDB.
Galaxy’s blocking session insights surface long locks instantly, and Collections make vetted DDL scripts easy to discover and reuse without collisions.
.avif)
.avif){kind=logo}
