Part 2 of 5 · 1 min read
Shared and exclusive locks
A shared lock allows concurrent reads; an exclusive lock requires sole access for a write.
Think of a library book. Several people can read photocopies of it at once. But the one actual pen a librarian uses to annotate the original can only be held by one person at a time.
In plain words: a shared lock, held to read a data item, can be held by multiple transactions at the same time, since concurrent reads do not conflict with each other. An exclusive lock, held to write a data item, can only be held by one transaction at a time, and no other transaction may hold any lock, shared or exclusive, on that same item while it is held. A transaction requesting a lock that conflicts with another transaction's currently held lock must wait until that lock is released.
A worked example. Two transactions can both hold a shared lock on the same row to read it concurrently; a third transaction wanting to write that row must wait for an exclusive lock, which cannot be granted until both shared locks are released.
The key lines. No new SQL; shared and exclusive locks are acquired automatically by the database as it executes reads and writes, based on the isolation level and locking protocol in effect.
Shared lock, exclusive lock
A shared lock permits concurrent reads and can be held by multiple transactions at once; an exclusive lock is required for a write and can be held by only one transaction, excluding all other locks on that item.
Example: multiple people reading photocopies of a library book at once mirrors a shared lock; the one pen used to annotate the original, held by only one person at a time, mirrors an exclusive lock.