Locking Mechanisms for Isolation
You'll learn: How databases use shared and exclusive locks to coordinate concurrent access and prevent conflicting operations between transactions.
The Coordination Problem
When multiple transactions run simultaneously, they need a way to coordinate access to the same data without stepping on each other's toes. Imagine two people trying to edit the same document at once—without a coordination mechanism, chaos ensues. Databases use locks as traffic signals to manage who can access what, and when.
Two Types of Locks
Shared Locks (Read Locks)
When a transaction wants to read data, it requests a shared lock. Multiple transactions can hold shared locks on the same data simultaneously—reading doesn't interfere with other readers. It's like multiple students checking out the same library book's digital copy: everyone can read at once.
Exclusive Locks (Write Locks)
When a transaction wants to write (modify) data, it requests an exclusive lock. Only one transaction can hold an exclusive lock at a time, and no shared locks are allowed simultaneously. This prevents the chaos of someone reading half-updated data or two writers overwriting each other. It's like checking out the library's only physical copy—nobody else can access it until you return it.
How They Work Together
- Shared + Shared: Compatible ✓ (multiple readers coexist)
- Shared + Exclusive: Incompatible ✗ (reader blocks writer, writer blocks reader)
- Exclusive + Exclusive: Incompatible ✗ (writers block each other)
When a transaction requests a lock that conflicts with an existing lock, it must wait until the conflicting lock is released (typically at commit or rollback).
Key Takeaway: Shared locks allow concurrent reads while exclusive locks enforce single-writer access, together preventing read/write and write/write conflicts that would violate transaction isolation.