📓 Introduction: The Classroom Journal
Imagine a single physical notebook sitting on a desk in a classroom:
- Reading (Shared Lock / S-Lock): Multiple students can walk up and read the open page at the same time. If 5 students are reading, there is no conflict. This is a Shared Lock.
- Writing (Exclusive Lock / X-Lock): If a student wants to write or correct a paragraph on the page, they must grab the notebook. While they are writing, no one else can read or write in it. They have exclusive control. This is an Exclusive Lock.
In databases, Locking is the mechanism used to manage concurrency. It ensures that when multiple queries try to read and write to the same rows at the identical millisecond, they don't corrupt the data!
🔒 Lock Compatibility & Granularity
Database locks have rules about who can access data at the same time:
Lock Compatibility Matrix
- Shared (S) Lock: Compatible with other Shared Locks, but blocks Exclusive Locks.
- Exclusive (X) Lock: Blocks both Shared and other Exclusive Locks.
LOCK COMPATIBILITY MATRIX
+-------------------+-------------------+-------------------+
| Requested \ Held | Shared (S) Lock | Exclusive (X) Lock|
+-------------------+-------------------+-------------------+
| Shared (S) Lock | ALLOWED (Read) | BLOCKED (Write) |
| Exclusive (X) Lock| BLOCKED (Read) | BLOCKED (Write) |
+-------------------+-------------------+-------------------+
Lock Granularity (Size of the Gate)
The database can lock data at different levels of size:
- Row-Level Lock: Lock only a single row. High concurrency, but uses more memory for locks.
- Page-Level Lock: Lock an entire 8KB page of rows.
- Table-Level Lock: Lock the entire table. Blocks everyone else entirely, but uses very little locking memory.
💻 Code Examples: Simulating Read/Write Locks
Let's look at how we acquire exclusive locks in SQL and simulate locks in code.
SQL Lock Query
-- Lock a row exclusively until the end of the transaction
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- Acquires an Exclusive (X) Lock!
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
COMMIT; -- Lock is released hereMulti-Language Execution
⚠️ Common Mistakes
1. Lock Escalation
Allowing too many individual row locks to accumulate. The database engine will run out of lock memory and automatically escalate those row locks into a single giant Table Lock, blocking everyone else on the server!
2. Not Releasing Locks
Leaving transactions open inside scripts without calling COMMIT or ROLLBACK. The locked rows remain inaccessible to all other users until the session times out.
🔍 Interview Corner
Q1: What is the difference between a Shared Lock (S) and an Exclusive Lock (X)?
- Shared Lock (S): Allows multiple read queries to access the same resource simultaneously. It blocks any write operations.
- Exclusive Lock (X): Allows only one write query to modify or read the resource, completely blocking all other reads and writes.
Q2: What is Two-Phase Locking (2PL)?
Two-Phase Locking (2PL) is a transaction locking protocol that guarantees serializability. It consists of two phases:
- Growing Phase: The transaction can acquire locks but cannot release any.
- Shrinking Phase: The transaction can release locks but cannot acquire new ones.
📝 Summary
- Locks prevent database transactions from corrupting data during concurrent operations.
- Shared (S) Locks permit concurrent reads.
- Exclusive (X) Locks isolate writes, blocking all other transactions.