Part 127: Database Locking: Shared (S) vs Exclusive (X) Locks
How relational databases manage conflicting access to physical pages and rows.
1. The Lock Compatibility Matrix
| Requested Lock | Existing Shared (S) Lock | Existing Exclusive (X) Lock |
|---|---|---|
| Shared (S) (Read) | Compatible (Multiple readers allowed) | ❌ Blocked |
| Exclusive (X) (Write) | ❌ Blocked | ❌ Blocked |
- Shared Lock (S): Acquired when reading data. Multiple transactions can hold shared locks simultaneously.
- Exclusive Lock (X): Acquired when modifying data (
UPDATE,DELETE). Only one transaction can hold an X-lock.
Key Takeaways
- Readers don't block readers; writers block everyone.
In Part 128, we explore Lock Granularity: Row-Level vs Page-Level vs Table-Level!