Locks (Shared & Exclusive): How Databases Handle Concurrent Access

Databases serve hundreds (sometimes thousands) of requests per second. Multiple transactions are reading and writing the same data at the same time. Without some way to coordinate this, data would get corrupted instantly.

That coordination mechanism is locking.

Why Locks Exist

Imagine two transactions both try to update the same bank account balance simultaneously:

• Transaction A reads balance: ₹1000
• Transaction B reads balance: ₹1000
• Transaction A adds ₹500, writes ₹1500
• Transaction B adds ₹200, writes ₹1200

The correct final balance should be ₹1700, but Transaction B overwrote A's update. This is a lost update — and it's exactly what locks prevent.

Shared Lock (S Lock)

"Multiple readers can hold this at once, but no one can write while it's held."

A shared lock is acquired when a transaction wants to read a row. Multiple transactions can hold shared locks on the same row simultaneously — reading the same data at the same time is safe.

BUT — if anyone holds a shared lock, no one can acquire a write lock on that data until all shared locks are released.

-- In some databases, you can explicitly request a shared lock
SELECT * FROM accounts WHERE account_id = 1 LOCK IN SHARE MODE;

Think of it like a library book that many people can read at the same time, but no one can modify (annotate, tear pages) while others are reading it.

Exclusive Lock (X Lock)

"Only one transaction can hold this. No one else can read OR write."

An exclusive lock is acquired when a transaction wants to write (INSERT, UPDATE, DELETE) a row. It's exclusive — only one transaction can hold it, and while it's held, no other transaction can read or write that row.

-- Explicitly request an exclusive lock for a read-you-plan-to-write
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

Back to the library analogy: an exclusive lock is like checking out the book. You have it, you can modify it, and no one else can even read it until you return it.

How They Interact

Lock Granularity

Locks can be applied at different levels:

• Row-level lock — only the specific row is locked. Most granular, least blocking. Used by default in PostgreSQL and MySQL InnoDB.
• Page-level lock — a block/page of rows is locked.
• Table-level lock — the entire table is locked. Simple, but blocks everyone else. Used in MyISAM (older MySQL engine).
• Database-level lock — the entire database is locked. Rare, used during things like backups.

Two-Phase Locking (2PL)

Databases don't just acquire locks randomly. They follow Two-Phase Locking to ensure serializability:

1. Growing phase — the transaction acquires all the locks it needs. It does not release any.
2. Shrinking phase — the transaction releases locks. It does not acquire any new ones.

This ensures that the order in which transactions access data is consistent, preventing most concurrency anomalies.

Deadlocks

Here's the dark side of locking.

A deadlock happens when two (or more) transactions are each waiting for the other to release a lock — and neither can proceed.

Example:

• Transaction A holds a lock on Row 1, wants Row 2.
• Transaction B holds a lock on Row 2, wants Row 1.
• Both are waiting. Forever.

Transaction A: locked Row 1 → waiting for Row 2
Transaction B: locked Row 2 → waiting for Row 1
       ↑_______________circular wait________________↑

How Databases Handle Deadlocks

Databases automatically detect deadlocks using a wait-for graph. When a cycle is detected, the database picks one transaction as the victim, rolls it back, and lets the other proceed.

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 5678.
        Process 5678 waits for ShareLock on transaction 1234;
        blocked by process 1234.
HINT: See server log for query details.

Avoiding Deadlocks in Practice

• Always acquire locks in the same order — if every transaction always locks Row 1 before Row 2, you eliminate circular waits.
• Keep transactions short — the longer a transaction holds locks, the higher the chance of conflict.
• Use SELECT ... FOR UPDATE early — acquire write locks at the start of your transaction, not partway through.
• Handle deadlock errors in your app — detect the error and retry the transaction (most ORMs and db drivers handle this automatically).

Optimistic vs Pessimistic Locking

These are two philosophies for handling concurrency:

Pessimistic Locking

Assume conflicts will happen. Lock the data before you read/write it. Wait if someone else holds a lock. This is what traditional SQL locks do.

Good for: High-conflict scenarios. When you know multiple transactions will compete for the same rows frequently.

Optimistic Locking

Assume conflicts are rare. Don't lock at all — just check at commit time whether the data changed since you read it. Usually done with a version column.

-- Read with version
SELECT balance, version FROM accounts WHERE account_id = 1;
-- balance = 1000, version = 5

-- Update only if version hasn't changed
UPDATE accounts
SET balance = 1500, version = 6
WHERE account_id = 1 AND version = 5;
-- If 0 rows updated → someone else changed it first → retry

Good for: Low-conflict scenarios. Web apps where most users are touching different records.

Quick Summary

Locking is what keeps concurrent database transactions from stepping on each other. Understanding it helps you write better queries, design less deadlock-prone transactions, and choose between optimistic and pessimistic strategies based on your actual workload.