Two-phase locking across shards

In basic two-phase locking, a transaction has a growing phase in which it acquires locks and a shrinking phase in which it releases them; after releasing a lock it must not acquire another. Under the appropriate conflict model, this discipline yields conflict-serializable schedules. Strict two-phase locking additionally keeps exclusive write locks until commit or abort, preventing other transactions from observing or overwriting those uncommitted effects. A stronger rigorous variant holds shared locks as well until completion; terminology varies, so state the exact retention rule.

Two-phase commit, in contrast, coordinates prepare votes and a final outcome among participants. A distributed system can use both: locks protect isolation while the commit protocol preserves atomic agreement. When a participant prepares, it may have to retain its locks until the global decision is learned. Releasing an exclusive lock early because its local computation is finished can let another transaction depend on an effect whose global outcome is unresolved. The correctness argument must connect lock release to the protocol’s valid completion boundary.

Locking only rows currently returned by a query does not necessarily prevent a new row from satisfying that query later. A serializable locking design may need range or predicate protection covering the decision the transaction made. The exact supported mechanism depends on the database and access path. Do not infer that issuing SELECT FOR UPDATE on returned rows automatically protects every business condition. Test empty result sets, concurrent insertions, deletes, and changes that move records into a protected predicate.

A consistent lock acquisition order across shards can prevent certain cycles if every relevant transaction follows it, including upgrades and ancillary resources. A local order applied independently at each shard may still permit a global cycle when transactions contact shards in different orders. Deadlock detection and bounded retry remain important when the prevention assumptions are incomplete. A prepared transaction’s locks cannot be resolved solely by a local victim heuristic; the commit protocol constrains what outcome is still legal.

Break hold time into useful local execution and time retained for dependencies, prepare persistence, and decision delivery. Improving local CPU work may barely change the total if distributed waiting dominates. Measure the distribution of lock-hold duration and wait age by record or tenant, while observing which remote stage extends it. Avoid making network requests to unrelated services while holding transaction locks unless the design explicitly accounts for that cost and failure behavior. Admission control can prevent a hotspot from consuming every connection with waiting transactions.

The queue lab offers a capacity analogy for that hotspot. Reset, set arrival rate to 4 and service rate to 3, predict backlog at one second, then Step twice. Compare a new run after Reset with both rates explicitly set to 2 and 3, predicting before two Steps. The initial backlog is four abstract jobs in each run. This model does not implement locks, 2PL phases, deadlock, or commit; explain why reducing an exclusive hold time can raise a real service ceiling, while merely admitting more waiters cannot.