Database Deadlocks Interview Questions

Master Database Deadlocks with interview-focused questions covering deadlock detection, prevention, avoidance, wait-for graph, victim selection, lock ordering, retry mechanisms, monitoring, production scenarios, and enterprise best practices.

Introduction

As applications become highly concurrent,

multiple transactions may wait for each other forever.

This situation is called a Deadlock.

Deadlocks are common in

Banking Systems
E-Commerce
Inventory Management
Payment Systems
Stock Trading
Airline Booking

Understanding deadlocks is essential for building scalable enterprise applications.

It is one of the most frequently asked database interview topics.

Deadlock Architecture

flowchart LR

TransactionA["Transaction A"] --> LockRowAWaitingrowbwaiting["Lock Row A --> WaitingRowB["Waiting Row B"]"]

TransactionB["Transaction B"] --> LockRowBWaitingrowawaiting["Lock Row B --> WaitingRowA["Waiting Row A"]"]

WaitingRowB["Waiting Row B"] --> Deadlock

WaitingRowA["Waiting Row A"] --> Deadlock

1. What is a Deadlock?

Answer

A Deadlock occurs when two or more transactions wait indefinitely for resources locked by each other.

Neither transaction can continue,

so the database must intervene.

Deadlock Example

Transaction A

Locks Account A

↓

Needs Account B

Transaction B

Locks Account B

↓

Needs Account A

↓

Deadlock

2. Why do Deadlocks occur?

Deadlocks occur because

Multiple Transactions
Exclusive Locks
Different Lock Order
Circular Waiting

Deadlock Conditions

Transaction A

↓

Resource 1

↓

Waiting

↑

Resource 2

↑

Transaction B

3. What are the four necessary conditions for a Deadlock?

A deadlock occurs when all four conditions exist.

Mutual Exclusion
Hold and Wait
No Preemption
Circular Wait

Four Conditions

Deadlock

├── Mutual Exclusion

├── Hold and Wait

├── No Preemption

└── Circular Wait

4. What is Mutual Exclusion?

A resource

can be owned

by only one transaction

at a time.

Example

Row Lock

Only one transaction

can update it.

5. What is Hold and Wait?

A transaction

holds one lock

while waiting

for another lock.

Example

Transaction A

Locks Row 1

↓

Waiting

↓

Row 2

6. What is No Preemption?

Locks

cannot be forcibly taken away.

Only the owning transaction

can release them.

7. What is Circular Wait?

Transaction A waits for B,

Transaction B waits for C,

Transaction C waits for A.

This creates

a cycle.

Circular Wait

flowchart LR

A --> B

B --> C

C --> A

8. What is Deadlock Detection?

Deadlock Detection

identifies

cycles

between waiting transactions.

If found,

the database resolves the deadlock.

9. How do databases detect Deadlocks?

Most databases build

Wait-for Graph

and search for cycles.

If a cycle exists,

a deadlock has occurred.

Wait-for Graph

flowchart LR

TransactionA["Transaction A"] --> TransactionB["Transaction B"]

TransactionB["Transaction B"] --> TransactionC["Transaction C"]

TransactionC["Transaction C"] --> TransactionA["Transaction A"]

10. What is a Wait-for Graph?

A Wait-for Graph

shows

which transaction

is waiting

for another transaction.

A cycle indicates

a deadlock.

11. What is Deadlock Resolution?

The database chooses

one transaction

as the

Deadlock Victim

rolls it back,

and allows

the remaining transaction(s)

to continue.

Deadlock Resolution

Deadlock

↓

Choose Victim

↓

Rollback Victim

↓

Other Transaction Continues

12. What is a Deadlock Victim?

A Deadlock Victim

is the transaction

selected

for rollback.

Selection depends on the database,

often considering

Least Work Done
Lowest Rollback Cost
Transaction Priority

13. What is Deadlock Prevention?

Deadlock Prevention

ensures

one of the four deadlock conditions

never occurs.

Examples

Fixed Lock Ordering
Acquire All Locks Upfront
Timeout

14. What is Deadlock Avoidance?

Deadlock Avoidance

checks

whether granting a lock

could create

an unsafe state.

If yes,

the request waits.

15. What is Lock Ordering?

Always acquire

resources

in the same order.

Example

Account A

↓

Account B

↓

Account C

Never change the order.

Lock Ordering Example

Transaction A

Lock A

↓

Lock B

Transaction B

Lock A

↓

Lock B

No deadlock.

16. What is Lock Timeout?

If waiting exceeds

a configured timeout,

the transaction fails

instead of waiting forever.

Timeout Workflow

Waiting

↓

Timeout

↓

Rollback

17. What is Retry Logic?

After

Deadlock Rollback,

applications

should retry

the transaction.

Most enterprise applications automatically retry deadlock victims.

Retry Workflow

flowchart LR

Deadlock --> Rollback --> Retry --> Success

18. How does MVCC reduce Deadlocks?

MVCC allows

readers

to access

previous versions

instead of waiting

for writers.

This reduces

reader-writer deadlocks.

19. Banking Example

Money Transfer

Transaction A

Lock Account A

↓

Account B

Transaction B

Lock Account B

↓

Account A

Deadlock occurs.

20. E-Commerce Example

Inventory

Order Service

↓

Inventory Row

↓

Shipment Row

Another service

locks them

in reverse order.

Deadlock.

21. Healthcare Example

Patient Update

↓

Doctor Update

↓

Billing Update

↓

Deadlock

if locking order differs.

22. Airline Booking Example

Seat Reservation

↓

Payment

↓

Booking

Different transaction order

can create deadlocks.

23. Stock Trading Example

Portfolio

↓

Wallet

↓

Portfolio

Opposite order

creates deadlock.

24. Production Example

Payroll

Employee

↓

Department

Another transaction

Department

↓

Employee

Deadlock occurs.

25. Common Causes

Different Lock Order
Long Transactions
Missing Indexes
Large Batch Updates
Excessive Locking

26. Advantages of Deadlock Detection

Automatic Recovery
Prevents Infinite Waiting
Maintains Consistency
Improves Reliability

27. Disadvantages

Transaction Rollback
Additional CPU
Retry Required
Temporary Performance Drop

28. Performance Tips

Keep transactions short.
Lock resources in the same order.
Commit quickly.
Create proper indexes.
Update fewer rows.
Avoid user interaction inside transactions.
Monitor deadlock frequency.

29. Best Practices

Always follow consistent lock ordering.
Keep transaction scope small.
Use retry mechanisms.
Handle deadlock exceptions.
Use optimistic locking where possible.
Monitor deadlock reports.
Avoid unnecessary table locks.
Tune lock timeout.
Analyze execution plans.
Test concurrent scenarios.

30. Deadlock Lifecycle

flowchart LR

TransactionA["Transaction A"] --> AcquireLockWait["Acquire Lock --> Wait"]

TransactionB["Transaction B"] --> AcquireLockWait["Acquire Lock --> Wait"]

Wait --> Deadlock

Deadlock --> RollbackVictim["Rollback Victim"]

RollbackVictim["Rollback Victim"] --> Retry

Enterprise Best Practices

Design transactions to acquire locks in a fixed order.
Keep transactions as short as possible.
Create indexes to minimize lock duration.
Handle deadlock exceptions gracefully.
Implement automatic retry with exponential backoff.
Monitor deadlocks using database monitoring tools.
Review deadlock graphs regularly.
Avoid large batch updates during peak hours.
Prefer optimistic locking for high-read workloads.
Continuously test concurrent transaction behavior.

Quick Revision

Topic	Key Point
Deadlock	Circular Waiting
Mutual Exclusion	One Owner
Hold and Wait	Hold One, Wait Another
No Preemption	Lock Cannot Be Taken
Circular Wait	Waiting Cycle
Wait-for Graph	Detect Cycles
Deadlock Victim	Rolled Back Transaction
Prevention	Remove Deadlock Conditions
Avoidance	Prevent Unsafe State
Lock Ordering	Best Prevention Technique
Retry	Retry After Rollback

Interview Tips

Interviewers frequently ask

What is a Deadlock?
Why do Deadlocks occur?
Explain the four deadlock conditions.
What is a Wait-for Graph?
How does a database detect deadlocks?
What is a Deadlock Victim?
Deadlock Prevention vs Avoidance.
How do you prevent deadlocks?
Why is Lock Ordering important?
How should applications handle deadlocks?

A strong interview explanation is:

"A deadlock occurs when two or more transactions wait indefinitely for resources locked by each other, creating a circular dependency. Modern databases detect deadlocks using a wait-for graph and automatically choose one transaction as the deadlock victim, rolling it back so the remaining transactions can proceed. In production systems, deadlocks are minimized by keeping transactions short, acquiring locks in a consistent order, creating proper indexes, and implementing automatic retry logic."

Summary

Deadlocks are an unavoidable aspect of highly concurrent database systems, but modern databases provide robust mechanisms to detect and resolve them automatically. Understanding deadlock conditions, wait-for graphs, victim selection, prevention, avoidance, retry mechanisms, and lock ordering is essential for designing reliable, scalable enterprise applications.

Mastering deadlocks prepares you for advanced topics such as MVCC, Distributed Transactions, Spring Transaction Management, High-Concurrency Systems, and Enterprise Database Performance Tuning.