Shared & Exclusive Locks: The Read vs. Write Rule

Why it exists

Databases need to handle many operations at once without corrupting data. If one transaction is updating a record while another is reading it, the second transaction could see incomplete, invalid data (a "dirty read"). Shared and exclusive locks were invented to enforce order and prevent these race conditions, ensuring data integrity.

The mental model

Think of data in a database as a book in a library. A Shared (S) lock is like letting multiple people read the book inside the library. As long as no one is writing, many people can read simultaneously. An Exclusive (X) lock is like checking the book out. Only one person can have it, they can read and write in it, and no one else can even look at it until it's returned.

How it works

A transaction must acquire a lock before accessing a data item. There are two types. First, a Shared (S) lock, used for reading. Multiple transactions can hold an S-lock on the same item at the same time. Second, an Exclusive (X) lock, used for writing. Only one transaction can hold an X-lock on an item, and if it does, no other transaction can hold any lock (S or X) on that item. If a transaction requests a lock that is incompatible with an existing lock, it must wait until the conflicting lock is released.

When to use it

S/X locking is the default concurrency control mechanism in most relational databases to ensure ACID properties, specifically Isolation. It's used whenever multiple transactions might access the same data, guaranteeing that reads are consistent and writes are atomic. It allows for high read concurrency while protecting data during modifications.

When not to use it

Aggressive or long-held locks can severely limit concurrency, causing other transactions to wait and reducing system throughput. In write-heavy systems, this can lead to frequent contention and deadlocks. Some high-throughput systems may use alternative strategies like Optimistic Concurrency Control, which avoids locking by assuming conflicts are rare.

One canonical example

Transaction T1 needs to update a user's balance. It requests and is granted an Exclusive (X) lock on the user's record. While T1 is calculating the new balance, Transaction T2 tries to read the same record for a report. T2 requests a Shared (S) lock, but it must wait because T1 holds an incompatible X-lock. Once T1 finishes its update, commits, and releases the lock, T2's S-lock is granted, and it reads the new, correct balance.