SQL Locks & Deadlocks: Shared, Exclusive & How to Avoid

1 Understanding Locks

Building on Transactions & Concurrency, locks are the mechanism databases use to prevent concurrent transactions from corrupting each other's data.

Lock Types

Lock Type	Also Called	Who Can Read?	Who Can Write?	Use Case
Shared (S)	Read lock	Everyone	No one (until released)	SELECT queries
Exclusive (X)	Write lock	Only the holder	Only the holder	INSERT, UPDATE, DELETE
Update (U)	Intent-to-write	Everyone	Only the holder (upgrades to X)	SELECT...FOR UPDATE

Lock Granularity

Level	What's Locked	Concurrency	Overhead
Row-level	Individual rows	High — other rows accessible	More memory per lock
Page-level	A page (group of rows)	Medium	Medium
Table-level	Entire table	Low — blocks all access	Minimal overhead

SQL

-- Explicitly lock rows for update
START TRANSACTION;

SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;  -- Exclusive lock on this row

-- Other transactions trying to read/write row id=1 will WAIT

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
COMMIT;  -- Lock released

SQL

-- Shared lock (read without blocking other readers)
SELECT balance FROM accounts
WHERE id = 1
LOCK IN SHARE MODE;  -- MySQL syntax

InnoDB (MySQL default): Uses row-level locking by default. This means two users can update different rows in the same table simultaneously. MyISAM uses table-level locking — one of many reasons to always use InnoDB.

Key Takeaways

Shared locks allow concurrent reads; exclusive locks block everything
FOR UPDATE acquires an exclusive lock; LOCK IN SHARE MODE acquires a shared lock
Row-level locking (InnoDB) provides the best concurrency
Locks are released on COMMIT or ROLLBACK

2 Deadlocks and How to Avoid Them

Top

A deadlock occurs when two transactions are each waiting for a lock the other holds. Neither can proceed — they're stuck forever. The database detects this and kills one transaction (the "victim").

Classic Deadlock Scenario

Deadlock timeline

Transaction A                    Transaction B
─────────────                    ─────────────
LOCK row 1 (account A)           LOCK row 2 (account B)
... processing ...               ... processing ...
TRY TO LOCK row 2 → WAITING     TRY TO LOCK row 1 → WAITING
         ↑                                ↑
         └── Both waiting for each other ──┘
                    DEADLOCK!

Prevention Strategies

Strategy	How It Helps	Example
Consistent lock order	Always lock tables/rows in the same order	Always lock account A before account B (by ID ascending)
Short transactions	Less time holding locks = less chance of conflict	Do calculations outside the transaction, lock only for the write
Lock timeout	Don't wait forever — fail fast and retry	`SET innodb_lock_wait_timeout = 5;`
Reduce lock scope	Lock fewer rows	Use precise WHERE clauses, not full table scans
Index your WHERE columns	Without index, InnoDB may lock more rows than needed	Add index on columns used in UPDATE...WHERE

SQL

-- Fix: Always lock in consistent order (lower ID first)
START TRANSACTION;

-- Both transactions lock account 1 first, then account 2
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Detecting Deadlocks

MySQL

-- Show the most recent deadlock
SHOW ENGINE INNODB STATUS;
-- Look for the "LATEST DETECTED DEADLOCK" section

-- Check current locks
SELECT * FROM performance_schema.data_locks;

Deadlocks are normal. In a busy application, occasional deadlocks are expected. The important thing is to handle them gracefully: catch the error in your exception handler and retry the transaction. Most deadlocks resolve on the first retry.

Key Takeaways

Deadlocks happen when two transactions wait for each other's locks
Prevention: consistent lock order, short transactions, proper indexing
The database automatically kills one transaction — your code should retry
Use SHOW ENGINE INNODB STATUS to diagnose deadlocks in MySQL

Transactions, Locks & Deadlocks

In This Chapter

1 Understanding Locks

Lock Types

Lock Granularity

Key Takeaways

2 Deadlocks and How to Avoid Them

Classic Deadlock Scenario

Prevention Strategies

Detecting Deadlocks

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect