SQL 97 🛢️ Database Locks, Deadlocks, and Blocking Resolution

Locks are the mechanism that makes concurrent transactions safe. When one transaction modifies a row, the database places a lock on it so that another transaction cannot read or write the same row until the first one finishes. This is not a bug or a performance problem—it is the foundation of the ACID model, the guarantee that a transaction either completes fully or not at all, and that concurrent transactions do not see each other’s partial work . Blocking is what happens when one transaction waits for another to release a lock. It is a normal part of database operation, and it only becomes a problem when the wait is prolonged or never resolves.

A deadlock is a specific kind of blocking that cannot resolve itself. Transaction A holds a lock on row 1 and waits for row 2. Transaction B holds a lock on row 2 and waits for row 1. Neither can proceed, and neither will release its lock until it proceeds. The database engine detects this cycle and kills one of the transactions—the “deadlock victim”—rolling back its work so the other can continue . The victim’s application receives an error and must retry the transaction.

Key point: Blocking is normal waiting for a lock to be released; a deadlock is a circular wait that the engine resolves by killing one transaction; the fix for blocking is finding and terminating the blocking session, while the fix for deadlocks is preventing the circular wait through consistent access ordering, short transactions, and retry logic.

---

Why locks and blocking exist

The isolation problem. Without locks, two transactions could read and write the same row simultaneously, producing lost updates, dirty reads, and inconsistent results. The database engine acquires locks automatically for UPDATE, DELETE, and INSERT statements, and for SELECT ... FOR UPDATE when explicit locking is requested . These locks are held until the transaction commits or rolls back.

The duration problem. Locks are held for the duration of the transaction, not the duration of the statement. If a transaction begins, updates a row, and then waits for user input before committing, the lock remains held for as long as the user takes. This is the most common cause of long blocking: a transaction left open, holding locks while the application does something unrelated . The fix is to keep transactions short and to never include user interaction inside a transaction.

The escalation problem. SQL Server locks the smallest resource needed—usually a row—to maximize concurrency. But if a single statement needs to lock more than 5,000 rows on one table, the engine escalates the row locks to a single table lock to reduce memory overhead. This is a deliberate trade-off: less lock management cost, but more blocking for other transactions that need that table . PostgreSQL does not use this style of escalation for ordinary row locks, but a large UPDATE that touches many rows will hold many locks and increase contention .

The gap lock problem in MySQL. InnoDB uses gap locks to prevent phantom reads at certain isolation levels. A gap lock locks the space between index records, not just the records themselves. A query like SELECT ... WHERE id BETWEEN 10 AND 20 FOR UPDATE locks the gaps, preventing other transactions from inserting values like 15, even if no row with that value exists yet . Gap locks are necessary for correctness at REPEATABLE READ isolation, but they increase the chance of deadlocks because two transactions can hold conflicting gap locks. At READ COMMITTED isolation, gap locking is disabled for searches and index scans, which reduces deadlock potential .

---

a. Identifying blocking and deadlocks

The first step in resolving a blocking problem is identifying which session is holding the lock and which session is waiting. Each database engine provides different tools for this.

SQL Server provides dynamic management views. sys.dm_tran_locks shows the current locks, sys.dm_os_waiting_tasks shows what each session is waiting for, and sys.dm_exec_requests shows the currently executing requests. The blocking_session_id column in sys.dm_os_waiting_tasks identifies the session that is blocking the current one . A query joining these views shows the blocked session, the blocking session, the SQL text of both, and the lock mode:

SELECT
    wt.session_id AS blocked_session,
    wt.blocking_session_id AS blocking_session,
    blocked_text.text AS blocked_sql,
    blocking_text.text AS blocking_sql,
    tl.request_mode AS lock_mode
FROM sys.dm_os_waiting_tasks wt
JOIN sys.dm_tran_locks tl ON tl.lock_owner_address = wt.resource_address
CROSS APPLY sys.dm_exec_sql_text(
    (SELECT most_recent_sql_handle FROM sys.dm_exec_connections WHERE session_id = wt.session_id)
) AS blocked_text
CROSS APPLY sys.dm_exec_sql_text(
    (SELECT most_recent_sql_handle FROM sys.dm_exec_connections WHERE session_id = wt.blocking_session_id)
) AS blocking_text
WHERE wt.blocking_session_id IS NOT NULL;

For deadlocks, SQL Server records the event in the system_health extended event session, which is enabled by default. The deadlock report contains an XML graph showing the resources and the processes involved, including which transaction was chosen as the victim .

PostgreSQL uses pg_locks and pg_stat_activity. The pg_blocking_pids() function is the recommended way to find the session that is blocking a given process, rather than manually joining pg_locks . A query that finds blocked sessions and their blockers:

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

PostgreSQL’s deadlock_timeout setting controls how long a transaction waits before the engine checks for a deadlock. The default is one second. If a deadlock is detected, the engine terminates one of the transactions and logs the event . The log_lock_waits setting can be enabled to log all lock waits that exceed deadlock_timeout, which helps identify blocking patterns before they become deadlocks.

MySQL provides SHOW ENGINE INNODB STATUS, which shows the latest detected deadlock, the transactions involved, the locks they held, and the SQL they were executing . The information_schema.INNODB_TRX and INNODB_LOCK_WAITS tables provide a structured view of current transactions and lock waits. The performance_schema.data_locks and data_lock_waits tables are the modern replacement and show which locks are blocking which requests .

---

b. Resolving blocking by terminating the blocking session

When a session is blocked for an extended period, the fastest resolution is to terminate the blocking session. This rolls back its transaction and releases its locks, allowing the blocked sessions to proceed.

SQL Server: KILL <spid>. The session is terminated, its transaction is rolled back, and its locks are released .

PostgreSQL: SELECT pg_terminate_backend(<pid>). The backend process is terminated, its transaction is rolled back, and its locks are released. This requires superuser privileges or membership in the pg_signal_backend role .

MySQL: KILL <thread_id>. The connection is terminated, its transaction is rolled back, and its locks are released .

Terminating a session is a blunt instrument. The rolled-back transaction may have been doing legitimate work, and the application must handle the failure. It is the correct response when a session is stuck and holding locks indefinitely, but it is not a fix for the underlying cause. If the same blocking pattern recurs, the transaction logic or the application’s connection handling is the problem.

---

c. Preventing deadlocks

Deadlocks cannot be eliminated entirely, but they can be made rare. The strategies target the conditions that create circular waits.

Consistent access ordering. If every transaction accesses tables and rows in the same order, a circular wait cannot form. Transaction A locks table 1 then table 2; transaction B locks table 1 then table 2. B waits for A on table 1, but A never waits for B because A already has table 1 and will acquire table 2 before B can. The cycle is broken . This is the single most effective prevention technique.

Short transactions. The longer a transaction runs, the longer it holds its locks, and the more opportunity there is for another transaction to acquire a conflicting lock. Keeping transactions short and free of user interaction reduces the window .

Lower isolation levels. READ COMMITTED reduces gap locking in MySQL and allows the engine to release locks sooner than REPEATABLE READ or SERIALIZABLE . If the application’s consistency requirements allow it, the lower isolation level reduces deadlock potential.

Retry logic. Even with prevention, some deadlocks will occur. The application must catch the deadlock error and retry the transaction. SQL Server returns error 1205, MySQL returns error 1213, and PostgreSQL returns a deadlock_detected error. The retry should include a short, randomized delay to avoid immediately recreating the deadlock .

---

Complete Example Session

The following session demonstrates a deadlock in PostgreSQL, the error it produces, and the resolution.

-- ============================================
-- PART 1: SETUP
-- ============================================
-- Create a table with two rows
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance INT NOT NULL
);
INSERT INTO accounts VALUES (1, 1000), (2, 1000);

-- ============================================
-- PART 2: TRANSACTION A
-- ============================================
-- Session 1 begins and locks row 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Row 1 is locked. Do not commit yet.

-- ============================================
-- PART 3: TRANSACTION B
-- ============================================
-- Session 2 begins and locks row 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- Row 2 is locked. Do not commit yet.

-- ============================================
-- PART 4: THE CIRCULAR WAIT
-- ============================================
-- Session 1 tries to lock row 2, which Session 2 holds
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Session 1 blocks, waiting for Session 2 to release row 2

-- Session 2 tries to lock row 1, which Session 1 holds
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- Session 2 blocks, waiting for Session 1 to release row 1
-- Neither can proceed. This is a deadlock.

-- ============================================
-- PART 5: DETECTION AND RESOLUTION
-- ============================================
-- PostgreSQL detects the deadlock after deadlock_timeout (default 1s)
-- It terminates one transaction and returns:
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890;
--         blocked by process 67890.
--         Process 67890 waits for ShareLock on transaction 12345;
--         blocked by process 12345.
-- HINT: See server log for query details.

-- The terminated transaction is rolled back.
-- The surviving transaction proceeds.

-- ============================================
-- PART 6: PREVENTION WITH ORDERING
-- ============================================
-- If both transactions locked rows in the same order:
-- Session 1: UPDATE id = 1; UPDATE id = 2;
-- Session 2: UPDATE id = 1; UPDATE id = 2;
-- Session 2 would wait for Session 1 on row 1,
-- and no circular wait would form.

The six parts demonstrate the deadlock cycle: two transactions each hold one lock and wait for the other. PostgreSQL detects the cycle, terminates one transaction, and returns an error. The prevention section shows how consistent ordering breaks the cycle before it forms.

---

Quick Reference

Blocking vs Deadlock

Aspect	Blocking	Deadlock
Cause	Waiting for a lock	Circular wait
Resolution	Lock is released	Engine kills a victim
Application action	Wait	Retry
Prevention	Short transactions	Consistent ordering

Monitoring Tools

Engine	Lock View	Wait View
SQL Server	sys.dm_tran_locks	sys.dm_os_waiting_tasks
PostgreSQL	pg_locks	pg_stat_activity + pg_blocking_pids()
MySQL	performance_schema.data_locks	data_lock_waits

Termination Commands

Engine	Command
SQL Server	KILL <spid>
PostgreSQL	SELECT pg_terminate_backend(<pid>)
MySQL	KILL <thread_id>

Deadlock Error Codes

Engine	Error
SQL Server	1205
PostgreSQL	deadlock_detected
MySQL	1213

---

Best Practices

✅ Do This:

-- Access tables in the same order in every transaction
BEGIN; UPDATE accounts SET ... WHERE id = 1; UPDATE accounts SET ... WHERE id = 2; COMMIT;

-- Keep transactions short
BEGIN; UPDATE ...; COMMIT;

-- Set a lock timeout
SET lock_timeout = '5s';

-- Retry the victim transaction with a delay
-- (application logic)

❌ Don’t Do This:

-- Leave a transaction open while waiting for user input
BEGIN; UPDATE ...; -- wait for HTTP request; COMMIT;

-- Access tables in different orders in different transactions
-- Session 1: UPDATE 1; UPDATE 2;
-- Session 2: UPDATE 2; UPDATE 1;

-- Ignore deadlock errors and let the request fail

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Long blocking	Transaction left open	Commit or rollback promptly
Deadlock repeats	Inconsistent access order	Order accesses consistently
Deadlock victim not retried	No error handling	Catch error, retry with delay
Gap lock deadlock (MySQL)	REPEATABLE READ gap locks	Use READ COMMITTED if possible
Blocking from DDL	ALTER TABLE waits for open transactions	Run DDL during maintenance windows

---

Real-World Examples

1. Find blocking in SQL Server

SELECT blocking_session_id, session_id, wait_type, wait_time
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL;

2. Find blocking in PostgreSQL

SELECT pid, pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

3. Terminate a blocker in PostgreSQL

SELECT pg_terminate_backend(12345);

4. View latest deadlock in MySQL

SHOW ENGINE INNODB STATUS;

5. Set lock timeout in PostgreSQL

SET lock_timeout = '5s';

6. Consistent ordering

-- Always lock lower ID first
UPDATE accounts SET ... WHERE id = LEAST(a, b);
UPDATE accounts SET ... WHERE id = GREATEST(a, b);

7. Retry on deadlock (pseudocode)

try: run transaction
catch deadlock: sleep(random 1-3s); retry

8. Check open transactions in SQL Server

SELECT session_id, transaction_begin_time
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id = tst.transaction_id;

9. MySQL lock wait timeout

SET innodb_lock_wait_timeout = 10;

10. Identify lock mode in SQL Server

SELECT request_session_id, resource_type, request_mode
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

---

Visual

The Deadlock Cycle

┌──────────────────────────────────────────────┐
│  TRANSACTION A                               │
│  Holds lock on Row 1                         │
│  Waits for lock on Row 2                     │
│         │                                    │
│         ▼                                    │
│  TRANSACTION B                               │
│  Holds lock on Row 2                         │
│  Waits for lock on Row 1                     │
│         │                                    │
│         ▼                                    │
│  Circular wait → deadlock                    │
│  Engine kills one → the other proceeds       │
└──────────────────────────────────────────────┘

Consistent Ordering Breaks the Cycle

┌──────────────────────────────────────────────┐
│  BOTH TRANSACTIONS LOCK ROW 1 FIRST          │
│                                              │
│  Transaction A locks Row 1                   │
│  Transaction B waits for Row 1               │
│  Transaction A locks Row 2, commits          │
│  Transaction B acquires Row 1, proceeds      │
│                                              │
│  No circular wait, no deadlock               │
└──────────────────────────────────────────────┘

Blocking Resolution Flow

┌──────────────────────────────────────────────┐
│  Session is blocked                          │
│         │                                    │
│         ▼                                    │
│  Identify blocking session                   │
│  (dm_os_waiting_tasks / pg_blocking_pids)    │
│         │                                    │
│         ▼                                    │
│  Is the blocker stuck?                       │
│  ├─ YES → terminate the session              │
│  └─ NO → wait for it to finish               │
└──────────────────────────────────────────────┘

Lock Duration

┌──────────────────────────────────────────────┐
│  BEGIN TRANSACTION                           │
│  UPDATE row 1  → lock acquired               │
│  (waiting for user input)  → lock held       │
│  UPDATE row 2  → lock acquired               │
│  COMMIT  → locks released                    │
│                                              │
│  The longer the gap before COMMIT,           │
│  the longer the lock is held.                │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Lock purpose	ACID isolation for concurrent transactions
Blocking	Normal wait for a lock
Deadlock	Circular wait, engine kills a victim
SQL Server monitoring	sys.dm_os_waiting_tasks
PostgreSQL monitoring	pg_blocking_pids()
MySQL monitoring	performance_schema.data_lock_waits
SQL Server terminate	KILL <spid>
PostgreSQL terminate	pg_terminate_backend(<pid>)
MySQL terminate	KILL <thread_id>
Prevention	Consistent ordering, short transactions

Key takeaways:

• Locks are necessary. They enforce isolation and prevent concurrent transactions from corrupting each other’s data. Blocking is the normal consequence of one transaction waiting for another to release a lock .
• Long transactions are the primary cause of blocking. A transaction that holds locks while waiting for user input or a slow external call will block every other transaction that needs those rows. Keep transactions short and free of external waits .
• Deadlocks are circular waits. The engine detects the cycle and kills one transaction. The victim’s application must catch the error and retry .
• Consistent access ordering prevents deadlocks. If every transaction locks resources in the same order, a circular wait cannot form .
• Terminating a session is a blunt but effective resolution. When a session is stuck and holding locks indefinitely, killing it releases the locks and unblocks the waiting sessions .
• MySQL’s gap locks increase deadlock potential. At REPEATABLE READ, InnoDB locks gaps between index records to prevent phantom reads. At READ COMMITTED, gap locking is disabled for searches, reducing deadlocks .

Remember: Locks are not the enemy—they are the mechanism that makes concurrent transactions safe. The enemy is the long transaction that holds locks while doing nothing, or the inconsistent access order that creates a circular wait. When blocking occurs, identify the blocker, decide whether it is stuck, and terminate it if necessary. When deadlocks occur, fix the ordering, shorten the transaction, and add retry logic to the application. The database engine will detect and resolve deadlocks on its own, but it will not prevent them. Prevention is the application’s responsibility.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!