7. Transaction Management, Concurrency and Recovery

Introduction and Context

A database system is valuable not merely because it stores data, but because it allows many users and applications to read and modify that data reliably. Banking transfers, online purchases, hospital records, airline reservations, payroll processing, and analytical pipelines all involve operations that must be executed correctly even when several transactions run simultaneously or when hardware and software failures occur.

A transaction is a logical unit of database work. It may contain one or more operations, such as reading an account balance, updating a balance, and recording a transfer. Transaction management provides the mechanisms required to preserve correctness when transactions execute concurrently and when failures interrupt processing. The central concerns of this chapter are therefore closely connected: ACID properties define the desired behaviour of transactions; concurrency-control methods regulate simultaneous execution; and recovery techniques restore a consistent database after failure.

The topic is particularly important in data-intensive systems. A data scientist may use a database to create features, update data pipelines, maintain experiment metadata, or support real-time decision systems. Incorrect concurrency or incomplete recovery can produce duplicated records, lost updates, inconsistent reports, or irreversible financial errors.

Learning Objectives

After studying this chapter, the reader should be able to:

• explain transactions and the ACID properties;
• distinguish serial, concurrent, and serializable schedules;
• analyse conflict and view serializability;
• apply two-phase locking, timestamp ordering, and multiversion methods;
• explain isolation levels and their anomalies;
• identify, prevent, detect, and resolve deadlocks;
• describe write-ahead logging, checkpoints, backup, and crash recovery;
• select appropriate transaction-management strategies for professional applications; and
• evaluate the trade-offs among consistency, concurrency, performance, and availability.

Transactions as Units of Reliable Work

A transaction begins when an application starts a logical task and ends when that task is either committed or rolled back. Consider a transfer of amount x from account A to account B:

```text

read(A)

A := A - x

write(A)

read(B)

B := B + x

write(B)

commit

```

The two account updates are not independent business actions. If the system debits A but fails before crediting B, money appears to disappear. The transaction abstraction ensures that the transfer is treated as one unit.

A transaction typically passes through active, partially committed, committed, failed, and aborted states. It becomes partially committed when its final statement has executed but its effects are not yet guaranteed on stable storage. It becomes committed only when the system has safely recorded the commitment. An aborted transaction has had its effects undone and may be restarted or terminated.

The ACID Properties

Atomicity means that a transaction is indivisible from the database’s perspective. Either all of its relevant operations take effect, or none do. Atomicity is supported by undo information, logging, and rollback procedures.

Consistency means that a committed transaction takes the database from one valid state to another valid state, preserving declared constraints and application rules. The database system helps enforce consistency through keys, foreign keys, checks, triggers, and transaction protocols, but application logic also matters.

Isolation means that the intermediate state of one transaction is not improperly exposed to other transactions. In a fully isolated execution, concurrent transactions behave as though they had run one after another in some order.

Durability means that once a transaction commits, its effects survive subsequent failures. Durability depends on stable storage, replicated logs, recovery procedures, and appropriate backup policies.

ACID properties should not be treated as completely independent. Stronger isolation may reduce concurrency, while durability requires additional input/output operations. Database design is therefore an exercise in balancing correctness, performance, latency, and operational risk.

Schedules and the Meaning of Correct Concurrency

A schedule is an ordering of operations from a collection of transactions that preserves the internal order of each transaction. A serial schedule executes one transaction completely before starting another. Serial schedules are easy to reason about and are correct when each transaction is correct, but they may waste resources because transactions often wait for input/output or network operations.

A concurrent schedule interleaves operations from multiple transactions. Concurrency can improve throughput and resource utilisation, but arbitrary interleaving can cause anomalies.

Suppose T1 reads a product stock value of 10 and later writes 9 after selling one item. T2 reads the same original value of 10 and writes 8 after selling two items. If both write without coordination, the final value may be 8 rather than 7. This is a lost update: one transaction overwrites the effect of another.

A dirty read occurs when a transaction reads data written by another transaction that has not committed. If the writer later aborts, the reader has used invalid information. A non-repeatable read occurs when a transaction reads the same row twice and obtains different committed values because another transaction updated it between the reads. A phantom read occurs when repeated execution of a predicate query returns a different set of rows because another transaction inserted or deleted matching rows.

Serializability

The principal correctness criterion for concurrent schedules is serializability. A schedule is serializable if its effect is equivalent to that of some serial schedule.

For conflict serializability, two operations conflict when they belong to different transactions, access the same data item, and at least one is a write. Thus, read-read operations do not conflict, while read-write, write-read, and write-write operations do. A precedence graph represents each transaction as a node. An edge Ti → Tj is added when a conflicting operation of Ti occurs before the corresponding operation of Tj. The schedule is conflict-serializable if and only if the graph is acyclic. A topological ordering of the graph gives an equivalent serial order.

View serializability is a broader criterion. It considers which transaction reads the initial value, which writes are observed by later reads, and which transaction performs the final write. Every conflict-serializable schedule is view-serializable, but some view-serializable schedules are not conflict-serializable, particularly schedules involving blind writes. Conflict serializability is more commonly used because it is easier to test and enforce.

Serializability concerns correctness, but it does not by itself guarantee recoverability. A schedule may be serializable yet allow a transaction to commit after reading data produced by a transaction that later aborts. Recoverability therefore requires additional rules concerning commit order.

Concurrency-Control Approaches

Concurrency control coordinates transaction operations so that isolation and consistency are preserved. Three major approaches are locking, timestamp ordering, and multiversion concurrency control.

Lock-Based Control and Two-Phase Locking

A lock is a permission associated with a data item. A shared lock (S) permits reading and may be held by multiple transactions. An exclusive lock (X) permits writing and is incompatible with both shared and exclusive locks.

The basic compatibility rules are:

| Requested / Existing | Shared | Exclusive |

|---|---:|---:|

| Shared | Compatible | Not compatible |

| Exclusive | Not compatible | Not compatible |

Under two-phase locking (2PL), a transaction has a growing phase, during which it acquires locks but does not release them, and a shrinking phase, during which it releases locks but acquires no new locks. Basic 2PL guarantees conflict serializability. Strict 2PL holds all exclusive locks until commit or abort, preventing other transactions from reading or overwriting uncommitted data. It simplifies recovery and prevents cascading rollback. Rigorous 2PL holds both shared and exclusive locks until completion.

Lock granularity may range from a database or table to a page or individual row. Coarse locks reduce lock-management overhead but reduce concurrency. Fine-grained locks permit more parallelism but require more memory and coordination. Intention locks allow a system to combine table-level and row-level locks safely.

Locking can cause blocking. A transaction requesting an incompatible lock must wait, which leads to the possibility of deadlock. Thus, a locking design must be considered together with deadlock management and workload characteristics.

Timestamp-Ordering Protocols

Timestamp ordering assigns each transaction a unique timestamp, usually at its start. The system uses the timestamp to enforce an order on conflicting operations. Each data item may maintain the timestamp of the most recent transaction that successfully read it and the most recent transaction that successfully wrote it.

If a transaction attempts an operation that would violate the timestamp order, it may be rolled back and restarted. Timestamp protocols are generally deadlock-free because transactions do not wait for locks, but they may produce repeated aborts under contention. They can be useful when avoiding blocking is more important than minimising restarts.

Variants such as wait-die and wound-wait combine timestamps with locking to control waiting. In wait-die, an older transaction may wait for a younger one, whereas a younger transaction requesting a resource held by an older one is aborted. In wound-wait, an older transaction may force a younger holder to roll back; a younger requester waits for an older holder. These policies reduce circular waiting.

Multiversion Concurrency Control

Multiversion concurrency control (MVCC) maintains multiple committed versions of a data item. Readers can often access a version that was valid at the reader’s snapshot time while writers create a newer version. Consequently, ordinary reads do not necessarily block writes, and read-heavy workloads can achieve high concurrency.

MVCC supports snapshot-style isolation, but it does not automatically eliminate every anomaly. Write-write conflicts still require control, and long-running transactions can retain old versions and increase storage or cleanup costs. Implementations differ in whether they use transaction timestamps, snapshots, garbage collection, or validation at commit.

A useful decision principle is that locking is attractive when precise blocking control and strong isolation are required, timestamp methods when deadlock avoidance is central, and MVCC when readers and writers should interfere as little as possible. The actual choice depends on the database engine and workload.

Isolation Levels and Practical Trade-offs

SQL isolation levels expose a controlled trade-off between correctness and concurrency. The names and exact guarantees can vary among database products, so implementation documentation must be consulted.

• Read Uncommitted permits the weakest isolation and may allow dirty reads.
• Read Committed prevents dirty reads, but repeated reads may see different committed values.
• Repeatable Read generally prevents non-repeatable reads, although phantom behaviour varies by implementation.
• Serializable provides behaviour equivalent to some serial execution, usually through locking, validation, or serializable snapshots.
• Snapshot or multi-version isolation, where offered, gives a transaction a consistent view of data at a chosen point, but may require additional controls for write conflicts and business invariants.

The weakest level should not be selected merely for speed. A reporting query that tolerates small variations may use a less restrictive level, while inventory allocation, financial settlement, and regulatory records generally require stronger guarantees. Isolation should be selected according to the business consequence of anomalies, not only benchmark throughput.

Deadlocks: The Cost of Mutual Waiting

A deadlock occurs when transactions wait indefinitely for one another. If T1 holds a lock on A and requests B while T2 holds B and requests A, neither can proceed.

Deadlock management has four broad strategies. Prevention structures the protocol so that at least one necessary condition for deadlock cannot occur. Consistent lock ordering is a practical example: all transactions request resources in the same global order. Timestamp policies such as wait-die and wound-wait are also prevention-oriented.

Avoidance examines whether granting a request could lead to an unsafe state. This approach requires knowledge of possible future resource demands and is less common in general-purpose database workloads.

Detection and recovery allow waiting but periodically construct a wait-for graph. A cycle indicates deadlock. The system then selects a victim, rolls it back, releases its locks, and allows other transactions to continue. Victim selection may consider transaction age, amount of work completed, number of locks held, and rollback cost.

Timeouts treat excessive waiting as evidence of deadlock or system distress. They are simple but can abort transactions unnecessarily when a long-running operation is merely slow. Applications should also use bounded retries and idempotent operations, because a transaction can be aborted for reasons other than deadlock.

Recovery: Making Failure Tolerable

Failures include transaction errors, system crashes, media failures, communication failures, and catastrophic site loss. Recovery aims to preserve atomicity and durability while returning the database to a consistent state.

Logging and Write-Ahead Rules

A log is a sequential record of database changes and transaction events. A typical update log contains the transaction identifier, data item, old value, and new value. The log may support UNDO, which restores old values for incomplete transactions, REDO, which reapplies committed changes not yet written to the database, or both.

The write-ahead logging (WAL) rule requires that the relevant log record reach stable storage before the changed database page is written. A second rule requires that the commit record be safely stored before the system reports commit to the client. These rules ensure that recovery has sufficient information to undo incomplete work and redo committed work.

In a deferred-update system, database pages are not updated until a transaction commits, so redo is typically required and undo may be unnecessary. In an immediate-update system, pages may be written before commit, so both undo and redo are commonly needed.

Checkpoints and Crash Recovery

Without checkpoints, recovery may need to inspect the complete log from the beginning of the database’s life. A checkpoint records a known recovery position and information about active transactions or dirty pages. It reduces recovery time, although it does not replace logging or backup.

A simplified restart-recovery process is:

1. identify transactions committed before the crash and transactions active or incomplete at the crash;

2. redo necessary updates of committed transactions to guarantee durability;

3. undo updates of incomplete transactions to guarantee atomicity; and

4. complete recovery and make the database available when consistency has been restored.

Modern recovery algorithms may use analysis, redo, and undo phases, transaction tables, dirty-page tables, compensation log records, and repeated passes over the log. The details differ by system, but the conceptual objectives remain the same.

Backup and Media Recovery

Crash recovery assumes that the database files and log are still available. A disk failure or site disaster requires media recovery from backups and archived logs. Full backups copy the complete database. Incremental backups copy changes since a reference point, while differential backups copy changes since the last full backup. Replication improves availability but should not be treated as a substitute for independent, tested backups; accidental deletion or corruption may otherwise be replicated.

Recovery planning should define a recovery point objective (RPO), which states how much data loss is acceptable, and a recovery time objective (RTO), which states how quickly service must be restored. Backups must be encrypted where appropriate, access-controlled, retained according to policy, and regularly tested through restoration exercises.

An Illustrative Case: Concurrent Inventory Reservation

Background

A fictional online retailer maintains an inventory table containing product identifiers and available quantities. During a promotional event, thousands of customers attempt to purchase a limited-stock product.

Situation and Problem

Two application servers read quantity 1 at nearly the same time. Each concludes that the item is available and writes quantity 0. The database ends with quantity 0, but two orders have been accepted. The system has produced an overselling error because the read-check-write sequence was not protected as one logically consistent operation.

Analysis

The operations of the two transactions conflict. A simple application-level check is insufficient because another transaction can change the row between the check and the update. Read Committed isolation may prevent dirty reads while still permitting this business anomaly. The required invariant is that successful reservations must not exceed stock.

Approach

One solution is an atomic conditional update such as:

```sql

UPDATE inventory

SET available_quantity = available_quantity - 1

WHERE product_id = :p AND available_quantity >= 1;

```

The application accepts the reservation only when the affected-row count is one. The update obtains an appropriate write lock or participates in the database’s concurrency protocol. The order record and inventory change should be committed in one transaction. If payment processing is external, the design may use a reservation status and compensating workflow rather than holding a database lock during a network call.

For high contention, the system may use short transactions, queueing, partitioning by product, or an MVCC-based conditional write with conflict detection. Deadlock retries must be bounded, and order creation should be idempotent so that a retry does not create duplicates.

Outcome

The inventory invariant is preserved: no more successful reservations exist than available units. Failed transactions can be retried or reported, and recovery mechanisms ensure that committed reservations survive a crash while incomplete reservations are rolled back.

Learning Points

The case demonstrates that isolation is a business requirement, not merely a database setting. It also shows the importance of atomic predicates, short transaction duration, idempotency, appropriate retry policies, and recovery-aware application design.

Professional, Governance, and Technical Considerations

Transaction design affects financial accuracy, auditability, service-level agreements, and regulatory compliance. Systems handling personal, medical, or financial data should maintain controlled access to logs and backups because logs may contain sensitive values. Audit trails should distinguish committed actions from attempted or rolled-back actions. Retention periods, legal holds, data residency, and deletion requirements must be reconciled with backup copies and immutable logs.

Common mistakes include treating a replica as a backup, holding locks while calling external services, selecting the weakest isolation level without analysing anomalies, ignoring deadlock retries, assuming a successful client response always means durable commit, and failing to test restoration. Another mistake is confusing database consistency with application correctness: a database may preserve all declared constraints while an unmodelled business rule is violated.

Observability is increasingly important. Administrators monitor lock waits, deadlock frequency, transaction latency, log growth, checkpoint duration, rollback volume, replication lag, and recovery time. Automated anomaly detection can identify unusual abort rates or workload contention, but automated tuning should remain subject to safeguards and human review. AI-generated transactions or data-manipulation scripts require validation because a syntactically valid operation may still violate business invariants.

Emerging distributed systems often provide configurable consistency, distributed transactions, consensus-based replication, and cloud-managed recovery. These technologies improve scalability and availability but introduce network partitions, clock uncertainty, cross-region latency, and more complex failure modes. The fundamental questions remain unchanged: what must be atomic, which orderings are acceptable, what data may be observed, and how will the system recover?

Best Practices for Transaction Processing

1. Define business invariants explicitly before selecting an isolation level.

2. Keep transactions short and avoid user interaction or slow external calls inside them.

3. Use atomic database operations for read-check-write decisions.

4. Apply strict concurrency protocols where recovery simplicity is important.

5. Establish a consistent lock order and implement bounded retry logic.

6. Use WAL-compatible configuration and verify that commit acknowledgements reflect durable state.

7. Combine full or incremental backups with archived logs and independent copies.

8. Test crash recovery, deadlock handling, backup restoration, and failover—not only normal execution.

9. Monitor contention, log utilisation, recovery objectives, and replication behaviour.

10. Document assumptions about isolation, idempotency, consistency, and failure handling.

Chapter Summary

Transactions provide a logical unit of database work and are governed by atomicity, consistency, isolation, and durability. Concurrent execution improves performance but can produce lost updates, dirty reads, non-repeatable reads, and phantoms. Serializability provides a principal correctness criterion; conflict serializability can be tested using a precedence graph. Locking, timestamp ordering, and MVCC offer different ways to control concurrency. Isolation levels express practical trade-offs, while deadlock prevention, detection, and recovery manage mutual waiting. Write-ahead logging, checkpoints, undo and redo processing, backups, and archived logs support crash and media recovery. Effective professional design connects these mechanisms to business invariants, security, governance, observability, and tested operational procedures.

Key Terms

Transaction: A logical unit of database work that ends in commit or abort.

Atomicity: The property that a transaction’s operations all take effect or none do.

Consistency: Preservation of database constraints and valid business states.

Isolation: Protection of a transaction from inappropriate effects of concurrent transactions.

Durability: Persistence of committed effects despite subsequent failures.

Schedule: An ordering of operations from one or more transactions.

Serial schedule: A schedule in which transactions execute one after another without interleaving.

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

Conflict serializability: Serializability determined by the ordering of conflicting operations.

Precedence graph: A directed graph used to test conflict serializability.

Shared lock: A lock that permits reading and is compatible with other shared locks.

Exclusive lock: A lock required for writing and incompatible with shared or exclusive locks.

Two-phase locking: A locking protocol with growing and shrinking phases.

MVCC: A method that maintains multiple data versions for concurrent access.

Dirty read: Reading data written by an uncommitted transaction.

Deadlock: A cycle of transactions waiting for resources held by one another.

Write-ahead logging: A rule requiring log records to be stored before corresponding data pages.

Checkpoint: A recovery marker that limits the amount of log processing after failure.

Recovery point objective: The maximum acceptable amount of data loss after a failure.

Recovery time objective: The targeted time to restore service after a failure.

Review Questions

Short Answer Questions

1. What is a transaction, and why is it useful in database systems?

2. Distinguish atomicity from durability.

3. What is a dirty read?

4. State the two phases of two-phase locking.

5. What is the purpose of a checkpoint?

Long Answer Questions

1. Explain the ACID properties using a fund-transfer example.

2. Describe conflict serializability and explain how a precedence graph is used to test it.

3. Compare locking, timestamp ordering, and MVCC as concurrency-control approaches.

4. Explain deadlock prevention, avoidance, detection, and recovery.

5. Describe write-ahead logging, checkpoints, and the major steps in crash recovery.

Analytical / Application Questions

1. Two transactions read the same inventory quantity and both update it based on the old value. Identify the anomaly and design a database-level solution.

2. A reporting workload has many readers, few writers, and long-running queries. Which concurrency-control approach and isolation considerations would you evaluate, and why?

3. A company has daily full backups but no restoration tests and no archived logs. Evaluate its recovery readiness using RPO and RTO concepts.

22. Multiple Choice Questions with Answers

Question 1

Which property ensures that a committed transaction remains effective after a system crash?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 2

A precedence graph for a schedule contains a cycle. What can be concluded?

A. The schedule is conflict-serializable

B. The schedule is not conflict-serializable

C. All transactions must be aborted

D. The schedule contains only read operations

Correct Answer: B. The schedule is not conflict-serializable

Question 3

Under strict two-phase locking, exclusive locks are normally held until:

A. The first read operation

B. The growing phase ends

C. Commit or abort

D. A checkpoint begins

Correct Answer: C. Commit or abort

Question 4

T1 reads a value written by T2 before T2 commits, and T2 later aborts. What anomaly has occurred?

A. Dirty read

B. Lost update

C. Phantom read

D. Deadlock

Correct Answer: A. Dirty read

Question 5

Which technique primarily avoids blocking readers by allowing them to read an appropriate committed version?

A. Coarse-grained locking

B. MVCC

C. Wait-die

D. Immediate rollback

Correct Answer: B. MVCC

Question 6

In a wait-for graph, a cycle indicates:

A. A checkpoint

B. A serial schedule

C. A deadlock

D. A committed transaction

Correct Answer: C. A deadlock

Question 7

What does the write-ahead logging rule require?

A. Data pages must always be written before log records

B. Log records must reach stable storage before corresponding data pages

C. Only aborted transactions may be logged

D. Checkpoints must be disabled during updates

Correct Answer: B. Log records must reach stable storage before corresponding data pages

Question 8

Two transactions update the same stock row using a conditional update that succeeds only when quantity is at least one. The main benefit is that the operation:

A. Removes the need for backups

B. Makes the business decision atomic

C. Guarantees no deadlocks in the database

D. Allows dirty reads safely

Correct Answer: B. Makes the business decision atomic

Question 9

Which isolation level generally provides behaviour equivalent to some serial execution?

A. Read Uncommitted

B. Read Committed

C. Repeatable Read

D. Serializable

Correct Answer: D. Serializable

Question 10

A company wants to reduce data loss after media failure. Which combination is most directly relevant?

A. Larger row locks and lower isolation

B. Full backups and archived logs

C. More shared locks and fewer checkpoints

D. Longer transactions and timeout-only detection

Correct Answer: B. Full backups and archived logs