HardExperience level: 8-15 years1 min read

What causes database deadlocks, and how do you prevent them in a busy OLTP system?

Reviewed by Gurusankar M.

Asked inAmazonMicrosoftbankingDeloitte
#deadlock#lock ordering#transaction#oltp#production
Report issue

Short Answer

Two transactions acquire the same rows/locks in opposite order and wait on each other; the DB detects the cycle and kills one (deadlock victim). Prevent with a consistent lock/update order, short transactions, the right indexes (to lock fewer rows), and retry logic on the victim.

On this page

Concept Made Simple

Coffee Chat Question

“What causes database deadlocks, and how do you prevent them in a busy OLTP system?”

Remember It Faster

Mind Map Answer

Cause
opposite lock-acquisition order
DB action
detect cycle → kill a victim
Prevent
consistent order, short txns, good indexes
Handle
catch + retry the victim

Think Beyond the Expected

What If?

Why can a MISSING index actually cause more deadlocks?

Without a good index, a query scans and locks far more rows (or escalates to range/table locks), widening the window for lock conflicts. Adding the right index narrows locking to the exact rows, reducing both contention and deadlocks.

Real World

OLTP deadlocks (order/inventory/ledger) are routine under load; the durable fixes are consistent update ordering, keeping transactions short, indexing to lock fewer rows, and idempotent retry on the deadlock victim.

Interviewer's Expectation

Keywords they're listening for:

  • opposite-order lock cycle
  • victim selection
  • consistent ordering
  • short txns + indexes reduce locking
  • retry the victim

Common Mistakes

  • Updating rows in inconsistent order across code paths
  • Long transactions holding locks across external calls
  • No retry on the deadlock victim

Best Practices

  • Acquire/update rows in a consistent order
  • Keep transactions short; index to lock fewer rows
  • Implement idempotent retry on deadlock errors

Follow-up Questions

  1. How do you read a deadlock graph / log?
  2. Why do shorter transactions reduce deadlocks?
  3. How does lock escalation contribute?

Continue Learning with AI

Take this question deeper with your favourite AI assistant. Pick a depth, copy the prompt, or open it directly — AI is your learning companion, not a shortcut.

Plain-language foundations

I'm preparing for a software engineering interview and want to understand this from scratch, as a beginner.

Topic: Transactions (SQL)
Interview question: "What causes database deadlocks, and how do you prevent them in a busy OLTP system?"

Please:
1. Explain the core idea in simple, plain language, using an everyday analogy.
2. Define any technical terms you use.
3. Walk through one small, concrete example.
4. Finish with a single sentence I can easily remember.

Keep the tone friendly and assume I'm new to this topic.
Open inChatGPTGeminiClaude

Was this answer helpful?

Affiliate links

Tools for Your Developer Workspace

Equipment for a comfortable, productive setup.

As an Amazon Associate I earn from qualifying purchases.