What is a Deadlock in SQL?

A deadlock in SQL occurs when two or more transactions permanently wait for each other to release locked resources, causing none of them to proceed.

In simple words:

A deadlock happens when transactions block each other in a circular waiting situation.

Why Deadlocks are Important

Enterprise applications execute:

Multiple concurrent transactions
Parallel updates
Simultaneous database operations

Without proper handling:

Applications may freeze
Transactions may fail
Performance issues occur
Users may experience delays

Simple Real-Life Example

Imagine:

Person A holds Room 1 key and waits for Room 2 key
Person B holds Room 2 key and waits for Room 1 key

Result

Both wait forever.

This is Exactly a Deadlock

In databases:

Transactions wait for locked resources

Deadlock Internal Architecture

Transaction A locks Resource X
            |
            v
Transaction B locks Resource Y
            |
            v
Transaction A waits for Y
Transaction B waits for X
            |
            v
Circular Waiting
            |
            v
Deadlock Occurs

Deadlock Example in SQL

Transaction 1

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

Transaction 1

Locks:

Account 1

Transaction 2

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

Transaction 2

Locks:

Account 2

Next Step

Transaction 1 tries:

UPDATE accounts
SET balance = balance - 50
WHERE account_id = 2;

Problem

Account 2 already locked by:

Transaction 2

Transaction 2 tries:

UPDATE accounts
SET balance = balance + 50
WHERE account_id = 1;

Problem

Account 1 already locked by:

Transaction 1

Final Situation

Transaction	Holding Lock	Waiting For
T1	Account 1	Account 2
T2	Account 2	Account 1

Result

Deadlock occurs.

Deadlock Query Flow

Transaction Starts
        |
        v
Acquire Lock
        |
        v
Request Another Lock
        |
        v
Resource Already Locked?
      /                  \
    No                    Yes
    |                      |
Proceed              Wait for Lock
                           |
                           v
Circular Waiting?
                     /            \
                   Yes             No
                    |               |
               Deadlock         Continue

What Causes Deadlocks?

Concurrent transactions
Improper locking order
Long-running transactions
High contention on tables
Poor transaction design

Conditions Required for Deadlock

Deadlock occurs when these four conditions exist:

1. Mutual Exclusion

Resources cannot be shared simultaneously.

2. Hold and Wait

Transaction holds one lock while waiting for another.

3. No Preemption

Locks cannot be forcibly removed.

4. Circular Wait

Transactions wait for each other in a cycle.

Deadlock Detection

Modern databases automatically:

Detect deadlocks

How Detection Works

Database creates:

Wait-for graph

Deadlock Detection Flow

Transaction Wait Graph
         |
         v
Cycle Detected?
      /           \
    Yes             No
     |               |
Kill One         Continue
Transaction

Deadlock Resolution

Database resolves deadlock by:

Rolling back one transaction

Rolled Back Transaction Called

Deadlock victim.

Example Error

Deadlock found when trying
to get lock;
try restarting transaction

Deadlock Prevention Techniques

1. Access Tables in Same Order

All transactions should:

Lock resources consistently

Example

Always lock:

Account 1 → Account 2

in every transaction.

2. Keep Transactions Short

Reduce lock holding time.

3. Use Proper Indexes

Indexes reduce:

Lock duration
Table scanning

4. Avoid User Interaction Inside Transactions

Do not:

Wait for user input while transaction open

5. Use Lower Isolation Levels Carefully

Can reduce:

Lock contention

Deadlock Prevention Architecture

Consistent Lock Order
          |
          v
Short Transactions
          |
          v
Reduced Lock Conflicts
          |
          v
Lower Deadlock Probability

Deadlock vs Blocking

Feature	Deadlock	Blocking
Waiting	Circular waiting	One-way waiting
Resolution	Rollback required	Wait until lock released
Severity	Critical	Temporary

Deadlock vs Starvation

Feature	Deadlock	Starvation
Cause	Circular waiting	Resource never allocated
Transactions Affected	Multiple	Usually one

Performance Impact of Deadlocks

Transaction rollback overhead
Reduced throughput
Increased latency
User experience degradation

Deadlocks in Banking Systems

Banking applications commonly face deadlocks during:

Account transfers
Balance updates
Concurrent payments

Example

Account A → Account B
Account B → Account A

Why Critical?

Financial transactions require consistency

Deadlocks in E-Commerce Systems

E-commerce platforms may face deadlocks during:

Inventory updates
Order processing
Payment confirmation

Example

Concurrent stock reduction
for same product

Deadlocks in Learning Platforms

Learning systems may face deadlocks during:

Course enrollment updates
Certificate generation
Exam result processing

Deadlocks in Microservices

Microservices architectures may face:

Distributed deadlocks

Example

Service A waits for B
Service B waits for A

Distributed Deadlock Challenges

Harder detection
Cross-service coordination
Network latency issues

Advantages of Deadlock Detection

Maintains consistency
Protects database integrity
Avoids infinite waiting

Disadvantages of Deadlocks

Transaction rollbacks
Performance degradation
Application retry complexity

Best Practices

Access resources in consistent order
Keep transactions small
Use indexes properly
Handle retries gracefully
Monitor long-running transactions

Common Interview Mistake

Many developers think:

Deadlock and blocking are same

Reality

Blocking:

Usually temporary

while deadlock:

Requires transaction rollback

Professional Interview Answer

A deadlock in SQL occurs when two or more transactions hold locks on resources and simultaneously wait for each other to release those locks, creating a circular dependency. As a result, none of the transactions can proceed. Modern database systems automatically detect deadlocks using wait-for graphs and resolve them by rolling back one transaction called the deadlock victim. Deadlocks commonly occur in highly concurrent systems such as banking applications, e-commerce platforms, and distributed microservices during simultaneous updates or transactional operations. Deadlocks can be minimized by maintaining consistent locking order, keeping transactions short, using proper indexes, and reducing lock contention.

Why Interviewers Like This Answer

Clearly explains circular waiting
Includes transaction and locking concepts
Shows deadlock detection understanding
Explains prevention strategies
Provides enterprise-level real-world examples

Frequently Asked Questions