1.3.2 - Transactions, ACID and record locking

Committing a complete business operation

A transaction is a group of database operations treated as one logical unit of work.

A theatre has Performance(PerformanceID, RemainingSeats) and Booking(BookingID, PerformanceID, SeatsBooked). Initially performance P7 has RemainingSeats 1 and no bookings. A successful one-seat reservation must reduce RemainingSeats to 0 and add booking B20 for one seat at P7. A foreign key ensures B20 references an existing performance, and a constraint forbids a negative remaining count. These are linked changes: decrementing without adding the booking loses a seat, while adding the booking without decrementing leaves it apparently available again.

A transaction begins before the related changes. COMMIT makes the completed transaction permanent; ROLLBACK discards its uncommitted changes. Atomicity means all the transaction's changes take effect or none do. If the application fails after decrementing the count but before adding B20, the incomplete operation must be rolled back. The database should return to RemainingSeats 1 with no B20, rather than retain half a reservation. This is a logical unit, even though the hardware performs its writes at different times.

Consistency means a committed transaction moves the database from one valid state to another, preserving its defined constraints and invariants. Here bookings must reference real performances, the remaining count cannot be negative, and booking/count changes must agree with the seat-capacity rule. A DBMS can enforce declared constraints; correct transaction logic must also preserve business rules which are not automatically expressed by those constraints. “Consistent” does not mean two unrelated fields have the same value or that input is factually true.

Preventing one user's update from replacing another's

Isolation prevents concurrent transactions from interfering in ways that violate the required transaction behaviour.

Consider a counter with value 10. Transaction A will add 2 and transaction B will add 3. If both read before either writes, this unsafe read–compute–write schedule loses one addition:

The correct serial result is 15 whichever complete transaction runs first. B's write of its stale computation overwrites A's effect; this is a lost update. A similar ticket schedule can let two users both read one remaining seat and both create a booking, even if they each write a non-negative count of zero. The business capacity rule is broken despite the local non-negative check.

Isolation aims to make concurrency behave as if transactions did not observe harmful partial or stale states, under the required isolation level. Another problem is a dirty read, where a transaction reads an uncommitted value which is later rolled back. Isolation is about interaction between transactions, while atomicity is about completeness within a transaction. DBMSs can offer different isolation levels and mechanisms; the conceptual requirement here is to protect this operation against the stated failures, not assume every level forbids every anomaly.

Locking the record before using its value

Record locking reserves access to a record so that conflicting operations cannot proceed unsafely at the same time.