Two transactions, waiting on each other, forever. That's a deadlock — and
it's not the same thing as a slow query.
THE CIRCULAR WAIT
T1 holds record A → wants B
T2 holds record B → wants A
Each holds the lock the other needs. Neither can proceed, and neither will
ever release. That cycle is what makes it a deadlock.
DEADLOCK ≠ SLOW QUERY ≠ LOCK CONTENTION
A slow query finishes eventually. Ordinary lock contention clears once the
holder commits. A deadlock resolves never — which is why raising a
statement timeout does nothing. Nobody is making progress to wait for.
REDUCING THE ODDS
1. Lock rows in a consistent order. If every code path touches A before B,
the cycle can't form between them. Note: this reduces deadlocks, it
doesn't eliminate them — different tables, index-level locks, foreign-key
checks and lock escalation can all still produce one.
2. Keep transactions short. HTTP calls, email sending and file I/O do not
belong inside a transaction. The longer you hold locks, the wider the
window.
HANDLING THEM PROPERLY
PostgreSQL detects the cycle and aborts one transaction as the victim,
which surfaces in Rails as `ActiveRecord::Deadlocked`.
tries = 0
begin
ActiveRecord::Base.transaction do
the whole unit of work
end
rescue ActiveRecord::Deadlocked
retry if (tries += 1) less than 3
raise
end
Three things that matter here:
• Retry the WHOLE transaction. The victim was rolled back entirely — re-running
only the statement that failed leaves you on top of nothing.
• Bound the attempts and back off. Unbounded immediate retries recreate the
same collision under load.
• Make the work safe to repeat. If the transaction sends an email or charges a
card, a retry can do it twice. Move side effects outside, or make them
idempotent.
Deadlocks happen in well-designed systems. Prevent what you can — handle the rest.
—
#Astmation — #software #systems, #automation, and #AI-powered #applications .
Smart Technology. Smarter Solutions.
https://astmation.com/
#Shorts #PostgreSQL #Database #Deadlock #RubyOnRails #BackendDevelopment #SQL #Transactions #Concurrency #SoftwareEngineering
Watch on YouTube