ITPEC FE Subject A April 2025 Question 22

Larger locks mean longer waits and lower throughput — granularity is the size of the unit one lock covers: a row, a page or a whole table.

Locking a large unit such as a whole table for an update blocks every other transaction that needs any row in it, even rows the lock holder never touches. Those transactions queue for longer, fewer run at the same time, and total throughput falls. In exchange, one transaction needs fewer locks, so lock-management overhead drops.

Small granularity (row locks) reverses the trade: more locks to manage, but more transactions can run concurrently.

Answer (c)

Why not others:

• •(a) — a larger lock unit makes conflicts more likely, so fewer transactions updating the same data can run at once, not more
• •(b) — whether other readers are blocked depends on the lock mode (shared or exclusive), not on its size; a larger lock covers more data and can only get in the way of more transactions
• •(d) — the reverse: one large lock covers many items, so a transaction needs fewer locks, not more

Key rule: Granularity trades overhead against concurrency: coarse locks are cheap to manage but serialize work, fine locks cost more to manage but let transactions run in parallel.

Databases · AI-written, may contain errors