4.10.5 - Concurrent database access and integrity
Learn why a client–server database needs concurrency controls when several clients use the same records. Trace a lost update, then distinguish record locks, serialisation, timestamp ordering and commitment ordering.
Simultaneous access can lose an update
A client–server database system lets multiple clients send requests to a database server. The server manages the shared database rather than giving each client an unrelated authoritative copy. Requests can overlap, so useful concurrency must be combined with controls that preserve integrity: data remain consistent with the database's rules and the intended completed transactions.
A transaction is a logical unit of database work, such as reading a stock quantity, calculating a new quantity and writing it back. Commit makes a successful transaction's changes permanent; rollback cancels its uncommitted changes. One transaction may contain several operations.
Suppose the stored stock quantity is 20. Client A records delivery of 8 items; client B records sale of 3 items. With no appropriate concurrency control:
| Step | Client A | Client B | Stored quantity |
|---|---|---|---|
| 1 | Reads 20 | 20 | |
| 2 | Reads 20 | 20 | |
| 3 | Calculates 20 + 8 = 28 | 20 | |
| 4 | Calculates 20 − 3 = 17 | 20 | |
| 5 | Writes 28 | 28 | |
| 6 | Writes 17 | 17 |
The correct result after both changes is 25. B's write overwrites A's update because B calculated from the old quantity. This is a lost update. B's arithmetic is correct for its stale input; the problem is the interaction between the transactions. Merely writing each value without interruption does not protect the complete read–calculate–write sequence.
Record locks and serialisation
A record lock can reserve a record for a transaction while it is being updated. With an exclusive lock, another transaction cannot make a conflicting update until the lock is released. Reading permissions depend on the lock/isolation policy; do not assume every lock prevents every reader.
For the stock example, A obtains the required lock before reading the value it will update. A reads 20, calculates 28, writes and commits, then releases the lock. B waits, obtains the lock, reads the current 28, calculates 25 and commits. Locking only at the final write would leave B's earlier stale calculation unprotected. The protected sequence, not just the existence of a lock, explains why the update is safe.
Serialisation arranges transactions so that their effect is equivalent to a valid serial order: one entire transaction followed by another. A simple implementation runs conflicting transactions one after the other. More advanced schedules can interleave operations while preserving a serial-equivalent result. In either case the final state must reflect both operations, rather than a mixture based on stale values.
Locks permit safe overlap on unrelated records, although waiting reduces responsiveness. If transactions acquire several locks in inconsistent orders, each may wait for a lock held by the other: deadlock. A database can detect this and cancel/retry a transaction. Locks are therefore a mechanism with costs, not a promise that no transaction ever waits or restarts.
Timestamp ordering fixes a transaction order
Timestamp ordering assigns transactions unique ordered timestamps and uses that order to decide whether their operations are allowed. A timestamp may be generated from a clock or a counter. The important property is an unambiguous order, rather than the printed time of day.
For example, an earlier transaction has timestamp 41 and a later one has timestamp 42. The database aims for an outcome equivalent to transaction 41 before transaction 42. It checks operations against relevant read/write timestamps recorded for the data. If an older transaction tries an operation that would contradict that order, the system may reject it, roll it back and restart it with a new timestamp.
Suppose transaction 42 has already written a record. Transaction 41 must not now overwrite that value using an earlier stale read as if its operation came after 42. Rejecting and restarting the conflicting transaction prevents that inconsistent ordering. This is not “allow every request and save the newest timestamp”: validation controls which operations may succeed.
Timestamp ordering can avoid waiting for record locks in a basic implementation, but conflicts can cause repeated retries. The database still needs a consistent policy for all relevant operations. Attaching a time label to a record without checking it does not itself preserve integrity.
Commitment ordering controls permanent changes
Commitment ordering controls when conflicting transactions are allowed to commit. The commit order must agree with the order required by the transactions' dependencies: if a conflict requires transaction A to come before B in the serial-equivalent schedule, A must commit before B. An incompatible commit is delayed or a transaction is cancelled and retried.
Consider B using a value produced by A. B cannot safely treat that value as a permanent database fact while A might still be rolled back. Controlling the commit order keeps accepted changes consistent with their required ordering. In more complex cases, the system checks the dependencies between transactions rather than simply using the order in which clients first connected.
This principle differs from timestamp ordering's preassigned transaction order. Commitment ordering constrains the order in which accepted work becomes permanent, using the relevant dependencies. It is not a rule that the transaction which starts first always commits first.
Ordering final saves alone cannot repair arbitrary stale calculations. If both stock clients have already calculated from 20, committing 28 followed by 17 still loses an update. A valid control must detect the conflict and preserve a serial-equivalent outcome, potentially by retrying B so that it uses the new value. The shared aim of all four techniques is integrity despite overlapping requests.
Explain the conflict, the specific operation the control restricts and why the accepted result preserves the transactions. Naming a concurrency technique alone does not explain its mechanism.