Transactions, Concurrency, and Recovery

Introduction

A database is valuable only when its data remains correct despite failures, simultaneous users, and incomplete operations. An online purchase, for example, may require reducing inventory, charging a customer, and recording an order. If the system stops after charging the customer but before recording the order, the database and the real world may disagree. Transaction processing provides the concepts and mechanisms needed to prevent or repair such inconsistencies.

This chapter explains transactions, the ACID properties, transaction states, isolation levels, locking, deadlocks, logging, backups, checkpoints, rollback, and recovery. It also considers practical trade-offs: stronger consistency can reduce concurrency, while faster performance may permit temporary anomalies.

Learning Objectives

By the end of this chapter, you should be able to:

• define a transaction and explain the ACID properties;
• describe transaction states and commit or rollback behavior;
• distinguish common isolation levels and their anomalies;
• explain shared and exclusive locks, two-phase locking, and deadlocks;
• describe write-ahead logging, checkpoints, backups, and recovery;
• select consistency and recovery strategies for practical applications;
• identify security, governance, and operational concerns in recovery design.

1. Transactions and ACID Properties

A transaction is a logical unit of database work. It may contain one SQL statement or many statements. The transaction boundary determines which changes are treated as one operation.

Consider a bank transfer from account A to account B:

BEGIN;
UPDATE Account SET balance = balance - 100 WHERE account_id = 'A';
UPDATE Account SET balance = balance + 100 WHERE account_id = 'B';
COMMIT;

If both updates succeed, the transaction commits. If either update cannot be completed, the transaction should roll back so that neither change remains.

Atomicity

Atomicity means that a transaction is treated as indivisible: either all its required changes become permanent, or none do. Atomicity protects against partial execution caused by errors, crashes, or explicit cancellation.

Consistency

Consistency means that a committed transaction preserves the database’s declared integrity rules. These may include primary-key uniqueness, foreign-key validity, data types, check constraints, and application rules such as “an account balance may not be negative.” Consistency is not created by the database alone; application logic and correct transaction design are also required.

Isolation

Isolation concerns the visibility of simultaneous transactions. A transaction should not incorrectly observe intermediate or conflicting results from another transaction. Database systems implement isolation through locks, multiversion techniques, validation, or combinations of these.

Durability

Durability means that once a commit is acknowledged, the committed effects survive an ordinary system failure. Durability normally depends on persistent logging and reliable storage. A system that reports success before safely recording the commit risks losing acknowledged work.

These properties are related but distinct. A system may provide atomicity without strong isolation, or durability without guaranteeing that the application’s business rules were correctly implemented.

2. Transaction States and Boundaries

A transaction commonly passes through the following states:

A transaction moves from active to partially committed after its last operation. It becomes committed only after the system completes the required commit protocol. A failed transaction enters recovery or rollback and becomes aborted. An aborted transaction may be restarted if the failure is temporary and retrying is safe.

Transaction boundaries should be deliberate. A web request that performs several related writes should usually use one transaction, but a long transaction should be avoided because it holds resources and increases the probability of conflicts. External actions such as sending an email or calling a payment service cannot automatically be rolled back by a database transaction. Such workflows require idempotency keys, an outbox pattern, compensation, or a distributed transaction protocol.

3. Schedules and Isolation Anomalies

A schedule is the order in which operations from concurrent transactions are interleaved. A schedule is serial if one transaction completes before another begins. Serial execution is easy to reason about but may waste available processing capacity.

A concurrent schedule is serializable if its final effect is equivalent to some serial schedule. Serializability is a central correctness goal because it permits concurrency while preserving a well-defined result.

Common anomalies include:

• Dirty read: transaction T2 reads data written by T1 before T1 commits. If T1 rolls back, T2 used data that never officially existed.
• Non-repeatable read: T1 reads a row twice and obtains different committed values because T2 changed it between the reads.
• Phantom read: T1 repeats a predicate query and finds new or missing rows because T2 inserted or deleted rows matching the predicate.
• Lost update: two transactions read the same value and later overwrite one another’s changes.
• Write skew: two transactions read related data, each concludes that its own update is safe, and together violate a cross-row rule.

Isolation Levels

Isolation levels are a practical way to choose consistency and concurrency. Names and exact behavior can vary by database product, so implementation documentation must be consulted.

Read Committed is common for ordinary web applications. Serializable is appropriate when correctness rules involve a set of rows and cannot safely tolerate anomalies. Snapshot approaches allow readers to proceed without blocking writers, but they do not automatically prevent every write-write conflict or business-rule violation.

Isolation is separate from durability. Increasing isolation does not ensure that a committed transaction survives disk failure, and durable storage does not prevent dirty reads.

4. Locking and Concurrency Control

A lock is a control placed on a database object or version to regulate concurrent access. A shared lock permits compatible reads. An exclusive lock is required for a write and normally conflicts with both shared and exclusive locks.

Lock granularity may be a database, table, page, partition, or row. Fine-grained row locks generally improve concurrency but require more memory and management. Coarse locks are simpler but may block unrelated work. A system may escalate many fine-grained locks into a coarser lock.

Two-Phase Locking

Under two-phase locking (2PL), a transaction has:

1. a growing phase, during which it acquires locks but does not release them; and
2. a shrinking phase, during which it releases locks but does not acquire new ones.

Strict 2PL holds write locks until commit or rollback. This helps prevent other transactions from reading uncommitted changes and simplifies recovery. Locking does not guarantee freedom from deadlock; it guarantees a particular form of conflict control when correctly applied.

Alternative approaches include optimistic concurrency control, which allows work to proceed and validates conflicts at commit, and multiversion concurrency control (MVCC), which maintains multiple row versions so that readers can often avoid waiting for writers. Each approach has workload-dependent advantages.

Deadlocks

A deadlock occurs when transactions wait for one another indefinitely. For example, T1 holds a lock on row A and requests row B, while T2 holds row B and requests row A.

Deadlock handling includes:

• prevention, such as requiring locks to be acquired in a fixed order;
• avoidance, which grants a request only if the resulting state remains safe;
• detection and recovery, in which a wait-for graph is checked for cycles and one transaction is selected as a victim;
• timeouts, which abort a transaction that waits too long.

Applications must be prepared to retry transactions aborted because of deadlock or serialization failure. Retries should use bounded backoff and must be safe against duplicate external effects.

5. Logging and Recovery

A database management system uses a log to record enough information to reconstruct a correct state after failure. Log records may contain a transaction identifier, data item, old value, new value, and operation type. The precise format differs among systems.

Write-Ahead Logging

Write-ahead logging (WAL) requires that the relevant log record reach stable storage before the corresponding modified data page is written. Before acknowledging commit, the system must also persist the information needed to establish that commit. WAL supports both undo and redo recovery.

• Undo removes effects of transactions that did not commit.
• Redo reapplies effects of committed transactions whose data pages were not yet written to durable storage.

A simplified recovery rule is:

• committed transactions are redone when necessary;
• incomplete transactions are undone.

This rule assumes a correct log, identifiable transaction boundaries, and storage that preserves the required log records.

Checkpoints

A checkpoint records recovery information so that the system does not need to examine the entire history of the log after every failure. A checkpoint may record active transactions and relevant dirty pages. During restart, recovery can begin from an appropriate checkpoint and then process later log records.

Checkpoints improve recovery time but do not replace backups. A checkpoint is an internal recovery aid; it is not necessarily an independent copy of the database.

Failure Types

• Transaction failure: a constraint violation, application error, or deadlock abort affects one transaction.
• System crash: memory contents are lost, but durable storage may remain available.
• Media failure: disks or storage become damaged or unavailable.
• Site or disaster failure: an entire facility or region is affected.

The more serious the failure, the more important backup copies, replication, off-site storage, and tested restoration procedures become.

6. Backup, Restore, Rollback, and Recovery

A rollback reverses uncommitted work within a transaction or restores a database to a previous logical point. A restore copies data from a backup into a usable database environment. Recovery is the broader process of returning the system to a consistent operational state, potentially using backups, logs, redo, undo, and replicas.

Common backup types include:

A recovery plan should define:

• Recovery Point Objective (RPO): the maximum acceptable amount of lost data measured in time. An RPO of 15 minutes means the organization may accept losing up to approximately 15 minutes of updates after a disaster.
• Recovery Time Objective (RTO): the target time for restoring service.
• backup frequency and retention;
• encryption, access control, and key management;
• restoration order and dependency handling;
• testing frequency and evidence of successful tests.

A backup that has never been restored in a test is an assumption, not proof of recoverability. Backups should be protected from accidental deletion, ransomware, unauthorized access, and corruption. At least one copy should normally be isolated from the primary failure domain.

7. Worked Example: Inventory Reservation

Suppose a product has stock of 1 unit. Two customers submit orders simultaneously. Each transaction performs:

1. read stock;
2. if stock is greater than zero, subtract one;
3. insert an order.

Without appropriate concurrency control, both transactions can read stock = 1 and both insert orders. The resulting stock may be 0 while two orders have been accepted. This is a lost-update or business-rule anomaly.

A safer approach is an atomic conditional update:

BEGIN;
UPDATE Product
SET stock = stock - 1
WHERE product_id = 42 AND stock > 0;

-- Application checks that exactly one row was updated.
INSERT INTO OrderLine(order_id, product_id, quantity)
VALUES (9001, 42, 1);
COMMIT;

The application should insert the order only if the conditional update succeeded. If zero rows were updated, the product was unavailable and the transaction should roll back or return an out-of-stock result. A suitable unique constraint and idempotency key should also prevent a client retry from creating duplicate orders.

This example demonstrates that transaction correctness depends on more than selecting an isolation level. The SQL operation, constraints, transaction boundary, retry policy, and external communication must work together.

8. Illustrative Case: Recovery in an Online Learning Platform

Background. An online learning platform records course enrolments, payment status, and available seats. During registration, a payment is authorized and a seat is reserved.

Problem. A database server crashes after the seat update but before the enrolment transaction commits. At the same time, several students are attempting to register for the final available seat.

Analysis. The platform needs atomicity for the seat and enrolment records, isolation to prevent two students from claiming the same seat, and durability for completed enrolments. Payment authorization is an external operation and cannot be undone automatically by database rollback.

Approach. The platform uses a transaction with a conditional seat update, a uniqueness constraint on the student-course pair, and a short lock duration. WAL records transaction changes before data pages are flushed. After restart, the database redoes committed transactions and undoes incomplete ones. Payment requests carry an idempotency key. An outbox table records a post-commit event, which a separate worker publishes to notification services. Full backups are taken regularly, incremental logs are retained, and restoration is tested.

Outcome. An incomplete seat reservation disappears during recovery, while a committed enrolment remains. If a client retries, the idempotency key prevents a duplicate payment or enrolment. The system accepts slightly more implementation complexity in exchange for correctness and operational resilience.

Learning points. Database transactions protect database state, not every external side effect. Constraints, logs, backups, retry logic, and monitoring form one recovery design. Correctness must be evaluated at the level of the complete business process.

9. Practical Consistency Trade-offs

Strong consistency is not always necessary for every feature. A payment ledger, seat allocation, and account balance generally require strict correctness. A product-view counter, search index, or recommendation feed may tolerate delayed updates.

Useful design questions include:

• What incorrect result would be unacceptable?
• Can the operation be retried safely?
• Is temporary staleness acceptable?
• Does the rule involve one row or many rows?
• What are the latency and throughput requirements?
• What is the cost of blocking, aborting, or repairing a transaction?

A common architecture keeps authoritative records in a transactional database and distributes derived data asynchronously. This improves responsiveness but introduces eventual consistency. Users may briefly see an old search result even though the authoritative order is correct. The interface should communicate meaningful states rather than falsely claiming completion.

10. Governance, Security, and Contemporary Practice

Recovery data often contains personal, financial, or confidential information. Backups therefore require authentication, authorization, encryption in transit and at rest, controlled key access, retention rules, and audit logs. Privileged recovery operations should be separated from ordinary application accounts.

Organizations should document who may restore data, who approves production recovery, how sensitive records are handled, and how long backups are retained. Retention must balance legal obligations, privacy requirements, storage cost, and the risk of retaining data longer than necessary.

Modern systems increasingly combine relational transactions with replicas, cloud-managed storage, distributed services, and event-driven processing. Replication can improve availability but is not automatically a backup: a corrupted or deleted record may be replicated everywhere. Distributed transactions may use coordination protocols, but many systems instead use sagas and compensating actions. Automated monitoring can detect replication lag, failed backups, long-running transactions, and deadlock rates; it cannot replace sound transaction boundaries or human-approved recovery procedures.

Best Practices

1. Keep transactions short and focused.
2. Enforce critical rules with database constraints as well as application validation.
3. Acquire locks in a consistent order where practical.
4. Handle deadlock and serialization failures with bounded, safe retries.
5. Use idempotency for retried requests and external operations.
6. Select the weakest isolation level that still protects the business rule.
7. Enable and monitor durable logging.
8. Maintain encrypted, access-controlled, geographically appropriate backups.
9. Test complete restoration and disaster procedures regularly.
10. Measure RPO and RTO against actual business requirements.
11. Treat replicas as availability mechanisms, not substitutes for independent backups.
12. Monitor long transactions, lock waits, failed commits, and recovery duration.

Chapter Summary

Transactions group related database operations into logical units. The ACID properties describe atomicity, consistency, isolation, and durability, although each depends on both database mechanisms and application design. Transaction states explain how work moves from execution to commit or rollback. Concurrent schedules can produce dirty reads, non-repeatable reads, phantoms, lost updates, and write skew; isolation levels offer different protections and costs.

Locks, two-phase locking, MVCC, and optimistic validation provide alternative concurrency-control strategies. Deadlocks require prevention, detection, timeouts, or safe retries. Write-ahead logs support undo and redo recovery, while checkpoints reduce restart work. Backups, restoration tests, RPO, and RTO determine whether an organization can survive serious failures. Finally, strong consistency should be applied where business correctness requires it, while less critical derived data may use eventual consistency for improved performance.

Key Terms

Transaction: A logical unit of database work.

Atomicity: The all-or-nothing property of a transaction.

Consistency: Preservation of integrity rules after a committed transaction.

Isolation: Control over how concurrent transactions observe one another.

Durability: Survival of committed changes after ordinary failure.

Commit: The successful completion that makes transaction effects permanent.

Rollback: Reversal of uncommitted transaction effects.

Schedule: The interleaved order of operations from transactions.

Serializability: Equivalence of a concurrent schedule to a serial schedule.

Dirty read: Reading data written by an uncommitted transaction.

Phantom read: Seeing different rows for a repeated predicate query.

Lock: A mechanism controlling concurrent access to data.

Shared lock: A lock generally compatible with other readers.

Exclusive lock: A lock required for a conflicting write.

Deadlock: Cyclic waiting among transactions.

MVCC: Multiversion concurrency control using multiple data versions.

Write-ahead logging: Requiring log persistence before related data pages are written.

Checkpoint: A recovery marker that limits restart scanning.

RPO: Maximum acceptable amount of data loss measured in time.

RTO: Target time for restoring service.

Review Questions

Short Answer Questions

1. What is a transaction, and why are transaction boundaries important?
2. Define each of the four ACID properties.
3. What is the difference between a shared lock and an exclusive lock?
4. Why does a database use write-ahead logging?
5. Distinguish RPO from RTO.

Long Answer Questions

1. Explain the transaction states from active through committed or aborted.
2. Compare Read Committed, Repeatable Read, and Serializable isolation.
3. Explain how deadlocks arise and describe two ways to handle them.
4. Describe how undo, redo, checkpoints, and backups contribute to recovery.
5. Discuss why database rollback cannot automatically reverse an external payment.

Analytical / Application Questions

1. Two transactions reserve the last available product simultaneously. Design a transaction and constraint strategy that prevents over-selling.
2. A company can tolerate losing five minutes of data but must restore service within one hour. What RPO and RTO should its recovery plan target, and what operational practices would support them?
3. A reporting query is blocking order processing. Recommend an isolation or architectural approach, and explain the consistency trade-off.

Multiple Choice Questions with Answers

Question 1

Which ACID property ensures that a transaction’s changes survive a system crash after successful commit?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 2

A transaction reads a value written by another transaction before that transaction commits. What anomaly is this?

A. Dirty read

B. Phantom read

C. Deadlock

D. Lost backup

Correct Answer: A. Dirty read

Question 3

Which isolation level provides behavior closest to executing transactions one at a time?

A. Read Uncommitted

B. Read Committed

C. Serializable

D. Snapshot only

Correct Answer: C. Serializable

Question 4

What is the main purpose of an exclusive lock?

A. To permit all simultaneous writes

B. To control conflicting access when data is written

C. To create a database backup

D. To compress transaction logs

Correct Answer: B. To control conflicting access when data is written

Question 5

A deadlock exists when:

A. A transaction commits twice

B. A backup contains duplicate rows

C. Transactions wait in a cycle for resources

D. A reader sees a committed value

Correct Answer: C. Transactions wait in a cycle for resources

Question 6

Under write-ahead logging, what must generally happen before a modified data page is written to durable storage?

A. The related log record is persisted

B. All active transactions are terminated

C. A full backup is completed

D. Every reader releases its lock

Correct Answer: A. The related log record is persisted

Question 7

Which statement best describes a checkpoint?

A. It permanently replaces all backups

B. It records recovery information to reduce restart work

C. It prevents every possible deadlock

D. It guarantees serial execution

Correct Answer: B. It records recovery information to reduce restart work

Question 8

A system may lose up to 15 minutes of updates after a disaster. Which target describes this requirement?

A. An RTO of 15 minutes

B. An RPO of 15 minutes

C. A lock timeout of 15 minutes

D. A checkpoint interval of zero

Correct Answer: B. An RPO of 15 minutes

Question 9

Why is an idempotency key useful when a client retries a payment request?

A. It increases database lock conflicts

B. It prevents a repeated request from creating an unintended duplicate effect

C. It disables transaction logging

D. It guarantees that external services support rollback

Correct Answer: B. It prevents a repeated request from creating an unintended duplicate effect

Question 10

Which approach is most suitable when a business rule requires that no two transactions claim the same final seat?

A. Ignore concurrent updates

B. Use an atomic conditional update and check its affected-row count

C. Use Read Uncommitted for maximum speed

D. Perform the seat update outside any transaction

Correct Answer: B. Use an atomic conditional update and check its affected-row count