Devsolved
The Symptom
Symptom
Customer support reported that a small percentage of users were clicking "Pay Now", but their orders never appeared in their account. The payment gateway showed the charge was successful. Our server logs revealed `ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction`.
Root Cause Analysis
Root Cause
The issue stemmed from how MySQL (InnoDB engine) handles row-level locks on tables with Foreign Key constraints. When a user checked out, Transaction A would lock the `users` row to update their loyalty points, and then attempt to insert a row into `orders` (which has a foreign key referencing `users`). Simultaneously, a background webhook (Transaction B) would attempt to insert a receipt into `orders`, and then update the `users` table to mark them as a returning customer. Because Transaction A locked `users` and waited for `orders`, while Transaction B locked `orders` and waited for `users`, a classic circular deadlock occurred. InnoDB detected this and instantly aborted Transaction A to break the cycle.
Deadlocks aren't bugs in the database; they are bugs in how your application orders its operations.
Resolution
Resolution
We refactored the application code to guarantee a consistent global locking order. Every transaction across the entire monolith must now always lock/update the `users` table FIRST, before touching the `orders` table. Because both transactions now request locks in the exact same sequence, Transaction B simply waits patiently in line for Transaction A to finish, entirely eliminating the circular dependency.
Code
javascript
// The fatal flaw: Inconsistent locking order
// Webhook Tx: Locks Orders -> Locks Users
// Checkout Tx: Locks Users -> Locks Orders (DEADLOCK!)
// The fix: Global ordered locking convention
// Webhook Tx: Locks Users -> Locks Orders
// Checkout Tx: Locks Users -> Locks Orders
Discussions 0