In this lesson, you will learn how the act of locking resources to ensure data integrity creates a physical bottleneck that dictates your system's overall transaction throughput.
The Trade-off: Consistency vs. Capacity
In database systems, locking is the mechanism we use to prevent data corruption during concurrent operations (as we discussed in our study of Shared vs. Exclusive Lock Modes). However, every lock is a "stop sign" for other transactions. When a transaction acquires an exclusive lock on a row or table, any other process attempting to access that same data must wait in a queue.
As the number of concurrent users increases, the frequency of these "lock collisions" rises. When transactions spend more time waiting for locks than actually executing SQL, your throughput—the number of transactions processed per second—plateaus and eventually drops.
Visualizing the Contention
Imagine a busy intersection. If every car (transaction) needs to occupy the entire intersection to pass through, traffic (throughput) grinds to a halt. The system becomes "latency-bound."
Consider this SQL scenario:
-- Transaction A
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE item_id = 101;
-- Transaction A holds an Exclusive lock on item 101.
-- Transaction B (running simultaneously)
UPDATE inventory SET stock = stock - 1 WHERE item_id = 101;
-- Transaction B must wait for Transaction A to COMMIT or ROLLBACK.
If Transaction A takes 500ms to complete due to complex logic, Transaction B sits idle. If you have 100 concurrent users trying to update the same row, the last user in line might wait several seconds, even if their actual update would only take 1ms to perform.
Why Throughput Suffers
When lock contention is high, the system suffers from serialized execution. Even if your database server has 64 CPU cores, the hardware cannot speed up the process if every core is stuck waiting for the same row lock. This is why we often prefer Optimistic Locking or Snapshot Isolation in high-concurrency environments—they allow us to avoid the "stop sign" of pessimistic locking whenever possible, keeping data pipelines moving at full speed.
Key Takeaway: Excessive locking turns parallel processing into serial processing, causing transaction latency to spike and total system throughput to collapse as contention increases.