4.2Concurrency control and locking

For a DBA designing a system in which multiple business processes access the same inventory or account table concurrently, deciding "at what granularity, and with what kind of lock" is a central judgment that balances data integrity against throughput. Locking coarsely and for a long time makes integrity robust but reduces concurrency; locking finely and briefly raises throughput but increases the risk of compromising integrity. This section builds the judgment needed to decide how to design the concurrency-control approach.

4.2.1Shared locks and exclusive locks

• A shared lock (S lock) is acquired for reads (SELECT); multiple transactions can hold an S lock on the same resource at the same time (reads do not conflict with each other). An exclusive lock (X lock) is acquired for writes (UPDATE/INSERT/DELETE) and cannot be held concurrently with any other lock (S or X)—it is exclusive. If a resource already holds an X lock, any other transaction requesting either an S lock or an X lock on it must wait.
• Lock granularity is the scope of the locked target—row-level, page-level, or table-level. A row lock is fine-grained and offers high concurrency, but increases the number of locks and management overhead. A table lock is coarse-grained and lightweight to manage, but concurrency is low because the entire table is the target. Many RDBMSes automatically perform lock escalation to a table lock when they detect an update pattern likely to cause heavy row-lock contention.

4.2.2Two-phase locking and serializability

• The two-phase locking protocol (2PL) splits each transaction's lock acquisitions and releases into two phases. In the growing phase, only acquiring new locks is allowed; no locks are released. As soon as even one lock is released, the transaction enters the shrinking phase, after which only releasing locks is allowed and no new lock may be acquired—once the shrinking phase begins, the transaction can never return to the growing phase, which is the core of the rule.
• Any schedule produced by transactions that all follow 2PL is theoretically guaranteed to be serializable (the result of running multiple transactions concurrently is equivalent to running them one at a time in some order). Note, however, that 2PL alone does not prevent deadlock (detailed in the next section)—serializability and deadlock-freedom are distinct properties. In practice, Strict 2PL, which releases all locks together at COMMIT/ROLLBACK, is widely used, since it also prevents cascading rollbacks (cascading aborts).

Suppose a DBA for an online seat-reservation system is investigating a throughput drop during a peak-access period. The investigation reveals that both the SELECT that checks seat availability and the UPDATE that reserves a seat are acquiring a table lock scoped to the entire seat table (table-level granularity), which is the cause. The design decision to make here is to narrow the lock granularity to row locks (one seat per row)—transactions updating the same seat row still block each other, but queries and updates on different seats no longer wait on each other, substantially improving concurrency. However, narrowing to row locks alone does not guarantee serializability, so it is also necessary to check whether each transaction follows the two-phase locking protocol (2PL)—specifically, whether checking availability (acquiring an S lock) and reserving the seat (acquiring an X lock) happen within a single transaction, going through a growing phase in which no lock is released until all needed locks are acquired, followed by releasing them all together at COMMIT, i.e., a strict 2PL implementation. A common mistake to avoid here is the design of "releasing the lock immediately after the seat-reservation UPDATE completes, since releasing locks early should raise throughput, and then separately updating an inventory summary afterward"—acquiring a new lock within the same transaction after having already released one and entered the shrinking phase violates the 2PL rule, breaking serializability and risking an inconsistency such as a double booking.

4.2.3Section summary

• A shared lock (S) is for reads and shareable; an exclusive lock (X) is for writes and exclusive against all other locks
• Lock granularity is row/page/table level—finer granularity raises concurrency but increases management overhead
• Two-phase locking (2PL) guarantees serializability via a growing phase (acquisition only) followed by a shrinking phase (release only), but deadlock requires separate countermeasures