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.