In this lesson, you will learn how transactional locking protocols create a "speed limit" for your database by forcing concurrent processes to wait for one another.
The Bottleneck of Isolation
When you group operations into a transaction, the database must ensure it stays consistent and isolated from other users. To do this, it uses locking protocols. When a transaction modifies data, it places a lock on those rows. If another transaction tries to access that same data, it is forced to stop and wait until the first transaction finishes.
While this ensures data integrity, it comes with a measurable cost: decreased throughput. As more users try to access the same tables, they spend more time waiting for locks to release than actually executing their queries.
Visualizing the Wait
Imagine a checkout counter at a store. If each customer (transaction) requires the clerk to lock the inventory ledger while they update their total, the store can only handle one customer at a time. The more customers, the longer the line gets, even if the clerk is working as fast as possible.
Consider this sequence in a concurrent environment:
-- Transaction A
BEGIN;
UPDATE Inventory SET stock = stock - 1 WHERE item_id = 101;
-- Transaction A holds a row-level lock here
-- Transaction B (running simultaneously)
UPDATE Inventory SET stock = stock - 1 WHERE item_id = 101;
-- Transaction B is now blocked, waiting for A to COMMIT or ROLLBACK
Quantifying the Impact
The "cost" is measured by the queue depth. If your database spends 10 milliseconds processing a request but 50 milliseconds waiting for a lock held by another process, your total transaction time has jumped from 10ms to 60ms.
High transactional overhead manifests as:
- Increased Latency: Individual queries take longer to finish because they are stuck in a queue.
- Reduced Throughput: The total number of transactions the database can complete per second drops because the system is busy managing lock requests rather than processing data.
To minimize this overhead, we aim to keep transactions as short as possible—only holding locks for the absolute minimum time required to maintain the database's consistency requirements.
Key Takeaway: Transactional locking guarantees data integrity, but every millisecond a lock is held adds to the system's total latency and reduces its overall throughput.