What's the difference between a shared lock and an exclusive lock?
Quick Answer
A **shared (read) lock** allows multiple transactions to hold it simultaneously on the same resource — any number of readers can proceed concurrently, but no one can acquire an exclusive lock while any shared lock is held. An **exclusive (write) lock** allows only one transaction to hold it at a time, and blocks both other exclusive locks and other shared locks — while one transaction holds it, no one else can read (under lock-based, non-MVCC read semantics) or write that resource.
Detailed Answer
Compatibility matrix
| Shared held by another txn | Exclusive held by another txn | |
|---|---|---|
| Request Shared | Allowed (both can hold it) | Blocked |
| Request Exclusive | Blocked | Blocked |
Shared lock — for reading
-- Explicit shared lock (SQL Server style)
SELECT * FROM accounts WITH (HOLDLOCK) WHERE id = 1;
Multiple transactions can hold a shared lock on the same row/table simultaneously — any number of concurrent readers is fine, since none of them are modifying the data. A shared lock only conflicts with an exclusive lock request: if any transaction holds a shared lock, no one else can acquire an exclusive lock on that resource until all the shared locks are released.
Exclusive lock — for writing
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- Acquires an exclusive lock on row id=1 for the duration of the transaction
Only one transaction can hold an exclusive lock on a given resource at a time, and while held, no other transaction can acquire any lock (shared or exclusive) on that same resource — it must wait.
Why MVCC changes the practical picture
In a pure lock-based engine, a long-running reader holding a shared lock can block a writer, and vice versa. Under MVCC (PostgreSQL, InnoDB, SQL Server's snapshot isolation), plain SELECTs generally don't take shared row locks at all — they read a consistent snapshot instead (see the MVCC question), so ordinary reads and writes don't block each other. Shared/exclusive locks in an MVCC engine mostly come into play for explicit locking reads (SELECT ... FOR SHARE / SELECT ... FOR UPDATE) and for the writes themselves, not for plain reads.
-- Explicit shared lock in PostgreSQL/MySQL: "I'm reading this, don't let anyone modify it until I'm done"
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
-- Explicit exclusive lock: "I intend to modify this, block others from reading-for-update or writing it"
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
Use FOR UPDATE (exclusive) when you're about to modify a row based on its current value and must prevent another transaction from changing it in between your read and your write (classic "read, check, then write" race condition). Use FOR SHARE when you need to ensure a row doesn't change while you rely on its value, but you're not modifying it yourself and are fine with other readers also holding a shared lock concurrently.