Database.Middle.How can you detect and resolve deadlocks?

Detecting and resolving deadlocks involves monitoring, logging, and possibly retrying transactions. Here’s a detailed overview:

---

🔍 How to Detect Deadlocks

✅ 1. Automatic Detection by the DBMS

Most modern databases detect deadlocks automatically and:

• Choose a “victim” transaction to rollback
• Raise an error like:
  • MySQL: ERROR 1213 (40001): Deadlock found when trying to get lock
  • PostgreSQL: ERROR: deadlock detected
  • SQL Server: Transaction (Process ID xx) was deadlocked...

✅ 2. View Deadlock Information

🐘 PostgreSQL

• Enable logging:

SET deadlock_timeout = '1s'; -- time to wait before detecting

Check logs (postgresql.log) for:

ERROR: deadlock detected
DETAIL: Process 12345 waits for ...

🐬 MySQL (InnoDB)

• Run:

SHOW ENGINE INNODB STATUS\G

Look for the LATEST DETECTED DEADLOCK section.

🪟 SQL Server

• Use the Deadlock Graph in SQL Server Management Studio (SSMS).
• Or:

SELECT * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL;

🔧 How to Resolve Deadlocks

✅ 1. Access Resources in Consistent Order

All transactions should lock rows/tables in the same order to prevent cycles.

✅ 2. Keep Transactions Short

Minimize the duration of locks by:

• Doing work outside the transaction when possible
• Avoiding user input inside a transaction

✅ 3. Add Indexes to Reduce Lock Scanning

If your query has to scan many rows, it may lock more than needed. Indexes help target exact rows.

✅ 4. Use Lower Isolation Levels (Carefully)

E.g., in PostgreSQL:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

This reduces locking but may allow anomalies.

✅ 5. Use Explicit Locking If Needed

To control locking behavior:

• PostgreSQL / MySQL:

SELECT ... FOR UPDATE;

✅ 6. Retry Logic in Application Code

If you catch a deadlock error, automatically retry the transaction:

int attempts = 3;
while (attempts-- > 0) {
  try {
    // start transaction
    // do work
    // commit
    break;
  } catch (DeadlockException e) {
    if (attempts == 0) throw e;
    // wait and retry
  }
}

📊 Summary Table

Action	Detection	Resolution
Enable logging	✅ PostgreSQL/MySQL logs	🔍 Use EXPLAIN, logs
Use admin tools	✅ SQL Server Graph	🎯 Visual deadlock detection
Fix lock order	❌ Prevents future issues	✅ Best long-term fix
Add retry logic	❌ Post-deadlock strategy	✅ Common in real systems