3228
MySQLERRORCommonLockingHIGH confidence
Locking service deadlock detected
Production Risk
High — one session is rolled back; application must retry.
Why it happens
- 1Two or more sessions requested locking service locks in conflicting orders, creating a deadlock.
How to reproduce
trigger — this will error
trigger — this will error
-- Session 1: service_get_write_locks('ns','lock1',0) then 'lock2'
-- Session 2: service_get_write_locks('ns','lock2',0) then 'lock1'expected output
ERROR 3228 (HY000): Locking service deadlock: rollback lock request.
Fix 1
Acquire locks in a consistent global order
Acquire locks in a consistent global order
-- Always acquire 'lock1' before 'lock2' across all sessions.
Why this works
Consistent lock acquisition order prevents circular wait conditions.
Fix 2
Use timeouts to detect and recover from deadlocks
Use timeouts to detect and recover from deadlocks
SELECT service_get_write_locks('ns', 'lock1', 5);Why this works
Non-zero timeout allows the call to fail fast instead of waiting indefinitely.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3228 ER_LOCKING_SERVICE_DEADLOCK
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev