Transactions, Concurrency, and Recovery

Introduction

A database is valuable because it supports reliable shared access to data. In a full-stack application, many users and services may read and modify the same records simultaneously. Payments, inventory reservations, examination results, account balances, and order processing all require more than individual SQL statements: they require coordinated units of work that remain correct despite failures and concurrency.

A transaction is a logical unit of database work. It may contain one statement or many statements. The database management system (DBMS) must ensure that transactions do not produce invalid intermediate results, interfere in unacceptable ways, or lose committed work when a process or machine fails. These responsibilities are addressed by transaction management, concurrency control, and recovery.

This chapter examines the ACID properties, transaction states, isolation levels, locking, multiversion concurrency control (MVCC), anomalies, deadlocks, write-ahead logging (WAL), checkpoints, backup, point-in-time recovery, and practical differences between MySQL and PostgreSQL.

Learning Objectives

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

• explain atomicity, consistency, isolation, and durability;
• describe transaction states and commit or rollback processing;
• identify dirty reads, non-repeatable reads, phantom reads, and lost updates;
• compare isolation levels, locks, two-phase locking, and MVCC;
• analyze and prevent deadlocks;
• explain WAL, checkpoints, crash recovery, backup, and point-in-time recovery;
• distinguish important transaction behavior in MySQL/InnoDB and PostgreSQL; and
• design transaction boundaries and recovery procedures for production applications.

1. The ACID Model

Atomicity

Atomicity means that a transaction is treated as an indivisible unit. Either all of its required changes become visible, or none do. If a transfer debits one account and credits another, a failure must not leave only the debit applied.

Atomicity is implemented through transaction logging, undo information, rollback mechanisms, and commit protocols. Atomicity does not mean that every statement succeeds automatically; an application may explicitly roll back after detecting a business-rule violation.

Consistency

Consistency means that a committed transaction takes the database from one valid state to another valid state. Validity is defined by constraints and business rules: primary keys remain unique, foreign keys remain valid, balances obey permitted rules, and application invariants are preserved.

The DBMS enforces declarative constraints, but application code is also responsible for rules that cannot be expressed easily in SQL. Consistency is therefore a property of the combined database design, transaction logic, and constraint system.

Isolation

Isolation controls how concurrent transactions observe one another. An uncommitted change should not be exposed in a way that causes incorrect application behavior. Complete serial execution gives the strongest conceptual isolation, but concurrency control allows transactions to overlap while producing an outcome equivalent, or close, to an acceptable serial order.

Isolation is not a single universal setting. Different levels trade correctness guarantees against throughput and waiting time.

Durability

Durability means that once a transaction successfully commits, its effects survive ordinary failures such as process termination or operating-system restart. Durability depends on correctly flushing commit-related log records to stable storage, reliable storage hardware, and a tested recovery procedure. A successful response from an application is not by itself proof of durable persistence if the database or storage configuration is unsafe.

2. Transaction States and Boundaries

A useful transaction-state model contains the following states:

A transaction begins with BEGIN or an equivalent API call. It ends with COMMIT, which makes its changes permanent, or ROLLBACK, which removes its effects. Many client libraries use autocommit: each individual statement is committed automatically unless an explicit transaction is opened.

Transaction boundaries should correspond to one coherent business operation. A transaction that is too short may allow an invariant to be broken between statements. One that is too long holds locks or snapshots longer than necessary and increases contention. External operations such as email delivery, payment-provider calls, and file uploads should generally not remain inside a database transaction; an outbox or workflow pattern is safer.

3. Concurrent Anomalies

Consider two transactions, T1 and T2, executing at overlapping times.

Dirty read

T1 updates a row but has not committed. T2 reads the new value. T1 then rolls back. T2 has used data that never became part of the database's committed history. Dirty reads are permitted by the SQL READ UNCOMMITTED level in some systems, but are usually unacceptable for business applications.

Non-repeatable read

T1 reads a row. T2 changes and commits that row. T1 reads it again and obtains a different value during the same transaction.

Phantom read

T1 evaluates a predicate such as WHERE status = 'open'. T2 inserts or deletes a row matching that predicate and commits. Repeating the predicate in T1 returns a different set of rows. The new or disappearing rows are phantoms.

Lost update

T1 and T2 both read a value, calculate new values, and write results. The later write overwrites the earlier one, even though both operations were logically intended to contribute. Atomic SQL updates, version columns, or locking can prevent this anomaly.

Write skew

Two transactions read the same condition and update different rows. Each transaction sees the condition as valid, but their combined committed changes violate an invariant. Snapshot isolation may permit write skew; serializable isolation or explicit predicate protection is needed when the invariant requires it.

4. Isolation Levels

The SQL standard describes four named isolation levels, although implementations differ in precise behavior.

The standard names do not completely specify implementation behavior. For example, phantom protection and snapshot semantics vary. An isolation level is therefore a contract to be tested against the chosen DBMS, not merely a label.

A serial schedule runs transactions one after another. A concurrent schedule is conflict-serializable when its conflicting operations can be rearranged into a serial order without changing the result. Conflicts occur when operations from different transactions access the same item and at least one is a write. A precedence graph can be used: create one node per transaction and an edge from T1 to T2 when T1's conflicting operation precedes T2's. If the graph is acyclic, the schedule is conflict-serializable. This test applies to schedules modeled using read and write operations; real MVCC systems may use additional validation rules.

5. Locking and Two-Phase Locking

A lock is permission to access a data item under a specified compatibility rule. The basic lock types are:

• Shared (S) lock: permits reading; multiple transactions may hold compatible shared locks.
• Exclusive (X) lock: permits writing; it conflicts with shared and exclusive locks.

A simplified compatibility table is:

In two-phase locking (2PL), a transaction has a growing phase, during which it acquires locks, and a shrinking phase, during which it releases locks and acquires no new locks. Strict 2PL retains write locks until commit or rollback. It prevents other transactions from reading or overwriting uncommitted changes and simplifies recovery. Locks may be placed on rows, pages, tables, index records, or key ranges. Larger lock granularity reduces lock-management overhead but increases contention.

A locking read can be requested explicitly, for example with SELECT ... FOR UPDATE where supported. Such a read is appropriate when an application will modify the selected rows and must prevent competing modifications. It should not be used indiscriminately: unnecessary locking reduces concurrency.

Lock escalation, where many fine-grained locks become a coarser lock, can reduce memory consumption but cause unexpected blocking. MySQL and PostgreSQL differ in implementation details, so production behavior should be observed using their lock-monitoring views.

6. MVCC and Snapshot-Based Concurrency

Multiversion concurrency control stores or reconstructs multiple versions of rows. A reader can use a version visible to its transaction snapshot while a writer creates a newer version. Consequently, readers and writers often do not block one another.

MVCC requires visibility rules. A snapshot records, explicitly or conceptually, which transactions were committed and which were still active when the snapshot was created. A row version is visible only when its creating transaction is committed according to the snapshot and its deleting or replacing transaction is not visible. Exact metadata and cleanup mechanisms are DBMS-specific.

MVCC improves read concurrency but does not eliminate coordination. Concurrent updates to the same logical row may wait or fail. Long-running snapshots delay cleanup: PostgreSQL may retain dead row versions until vacuum can remove them, while InnoDB history may grow when old versions are needed. Applications should keep transactions short and avoid idle transactions in transaction state.

Snapshot isolation gives each transaction a consistent view, but it is not always serializable. Write skew is the classic counterexample. PostgreSQL's serializable mode uses Serializable Snapshot Isolation (SSI) to detect dangerous dependency patterns and abort a transaction when necessary. MySQL/InnoDB's serializable behavior uses stronger locking semantics than its ordinary consistent reads.

7. Deadlocks and Starvation

A deadlock occurs when transactions wait cyclically. For example, T1 holds a lock on row A and requests row B, while T2 holds row B and requests row A. Neither can proceed.

A wait-for graph represents transactions as nodes and waiting relationships as directed edges. A cycle indicates deadlock. Systems may detect cycles and abort one victim, releasing its locks. Alternatively, timeout policies can terminate transactions that wait too long, although a timeout does not prove a cycle.

Deadlock prevention and handling practices include:

1. acquire resources in a consistent global order;
2. keep transactions short and avoid user interaction inside them;
3. access rows through selective indexed predicates;
4. retry transactions aborted for deadlock or serialization failure;
5. make retries safe through idempotent operations; and
6. monitor wait times, deadlock reports, and lock graphs.

Deadlock retry must rebuild the transaction from its beginning. Retrying only the failed statement may use assumptions or partial effects from a transaction that has already been rolled back.

8. Logging, WAL, and Crash Recovery

A DBMS cannot safely write arbitrary data pages before recording enough information to recover. Write-ahead logging requires that the relevant log record reach durable storage before the corresponding changed data page is written. At commit, the commit record must be durably flushed before the DBMS reports success, subject to configured durability guarantees.

A log commonly contains transaction identifiers, page or row references, before-images or after-images, and transaction status records. During recovery, the system determines which transactions committed and which did not. A simplified recovery procedure is:

1. Analysis: identify active transactions and dirty pages at the crash point.
2. Redo: reapply logged changes that may not have reached data files.
3. Undo: remove effects of transactions that had not committed.

Modern engines use variations of ARIES-style recovery, but exact algorithms differ. The essential principle is that logs provide a durable history from which a consistent state can be reconstructed.

A checkpoint records sufficient information to reduce recovery work. It does not necessarily mean that every dirty page has been written, and it is not a backup. A checkpoint establishes a recovery reference point; WAL generated after it may still be needed for redo and undo.

9. Backup and Point-in-Time Recovery

A sound recovery strategy has at least two dimensions:

• Recovery Point Objective (RPO): the maximum acceptable amount of data loss measured in time.
• Recovery Time Objective (RTO): the maximum acceptable time to restore service.

A full physical backup copies database files in a database-consistent manner. An incremental or differential strategy reduces backup time and storage but requires an appropriate chain of backups and logs. A logical backup exports schemas and data as SQL or another logical representation; it is portable but can be slower and may not preserve every physical property.

Point-in-time recovery (PITR) restores a base backup and replays archived WAL or binary logs until a selected time or transaction position. A general sequence is:

1. create and securely store a base backup;
2. continuously archive transaction logs;
3. restore the base backup to an isolated target;
4. configure recovery to replay logs up to the desired point;
5. validate constraints, row counts, and application behavior; and
6. document and rehearse the procedure.

Backups are not reliable merely because a backup command completed. They must be encrypted where appropriate, protected from unauthorized alteration, geographically or logically separated from the primary system, retained according to policy, and periodically restored in tests. Recovery procedures should address credentials, schema versions, extensions, application compatibility, and secrets management.

10. MySQL and PostgreSQL Transaction Differences

The following comparison assumes common current configurations: MySQL using the InnoDB engine and PostgreSQL using its standard table and transaction mechanisms. Versions and configuration settings matter.

In MySQL, a plain consistent read under InnoDB's default Repeatable Read generally sees a transaction-level snapshot, whereas a locking read reads current versions and may wait for locks. In PostgreSQL Read Committed, each statement generally sees a fresh snapshot, so two statements in one transaction can observe different committed data. PostgreSQL also uses MVCC tuple versions and requires vacuum-related maintenance; MySQL's storage-engine implementation has different cleanup and indexing behavior.

Neither product should be treated as a generic interchangeable SQL engine. Test transaction boundaries, generated SQL, lock behavior, retry conditions, timestamp functions, and migration scripts against the production engine.

11. Worked Application: Reserving Limited Inventory

Background

Consider an online store with an inventory row containing product_id and available_quantity. Two customers attempt to buy the final unit simultaneously.

Problem

A naive application performs:

1. read available_quantity;
2. if it is greater than zero, subtract one in application code; and
3. write the result.

If both transactions read 1 before either writes, both may calculate 0 and both orders may be accepted. This is a lost-update or overselling problem, depending on the exact SQL and constraints.

Analysis

A safe design must make the condition and decrement one coordinated operation. One option is an atomic conditional update:

UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 42
  AND available_quantity > 0;

The application checks the affected-row count. One affected row means the reservation succeeded; zero means the item was unavailable. The order and reservation record should be written in the same transaction, with a uniqueness constraint preventing duplicate reservation identifiers.

Another option is a transaction with a locking read:

BEGIN;
SELECT available_quantity
FROM inventory
WHERE product_id = 42
FOR UPDATE;
-- validate quantity, then insert order and decrement
COMMIT;

The lock must be held until the decision and update are complete. Under high contention, the atomic conditional update may hold locks for less application time. Under more complex rules, a locking read or serializable transaction may be appropriate.

Outcome and learning points

The design protects the invariant that a successful reservation consumes one available unit. It also demonstrates that isolation level alone is not a substitute for a correctly designed transaction. The application must inspect affected rows, handle deadlock or serialization retries, use a short transaction, and ensure that order creation and inventory decrement commit together.

12. Professional, Governance, and Ethical Considerations

Incorrect transaction design can produce financial loss, unfair allocation, corrupted records, or inaccurate academic and medical information. Engineers should identify business invariants explicitly, assign ownership for recovery objectives, and document the meaning of a successful commit.

Auditability requires appropriate logging of who performed sensitive operations and when, while respecting privacy and data-minimization principles. Transaction logs and backups may contain personal or confidential data; access should be restricted, encrypted, retained only as required, and securely destroyed. Recovery copies must receive protections comparable to the production database.

AI-assisted code generation can suggest transaction wrappers or SQL, but generated code may omit retry logic, use unsafe isolation assumptions, or place external calls inside transactions. Human review, integration tests, fault injection, and engine-specific documentation remain essential.

13. Best Practices Checklist

• Define the business invariant before choosing an isolation level.
• Keep transactions short, deterministic, and free of user interaction.
• Prefer atomic conditional statements for simple state transitions.
• Use constraints to enforce uniqueness and referential integrity.
• Select an isolation level deliberately rather than relying on defaults.
• Establish a consistent row-access order to reduce deadlocks.
• Retry deadlock victims and serialization failures from the transaction start.
• Monitor locks, long-running transactions, vacuum or purge health, and log volume.
• Configure and test WAL or binary-log archiving and backups.
• Perform documented restoration and PITR drills.
• Protect backups, logs, credentials, and audit data.
• Test behavior on the actual MySQL or PostgreSQL version and configuration.

Chapter Summary

Transactions group related database operations into reliable units. ACID describes atomicity, consistency, isolation, and durability, but each property depends on both DBMS mechanisms and application design. Concurrent schedules can cause dirty reads, non-repeatable reads, phantoms, lost updates, and write skew. Isolation levels provide different guarantees, while locks and MVCC provide alternative or complementary implementation techniques.

Two-phase locking can ensure serializable behavior but may cause blocking and deadlocks. MVCC improves read concurrency through multiple row versions, yet long transactions, write conflicts, and non-serializable anomalies remain important. Deadlocks require consistent access ordering, monitoring, and safe transaction retries.

WAL, checkpoints, and crash recovery preserve database correctness after failures. Backups and archived logs support restoration and point-in-time recovery; neither is useful unless restoration is tested. MySQL/InnoDB and PostgreSQL differ in defaults, snapshots, locking, cleanup, serializable execution, and recovery tooling. Professional database development therefore requires engine-specific testing, explicit invariants, secure recovery procedures, and disciplined transaction boundaries.

Key Terms

Transaction: A logical unit of database work.

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

Consistency: Preservation of database constraints and business invariants.

Isolation: Control over the visibility and interaction of concurrent transactions.

Durability: Persistence of committed effects across ordinary failures.

Commit: The successful completion of a transaction.

Rollback: Reversal of a transaction's effects.

Dirty read: Reading data written by an uncommitted transaction.

Non-repeatable read: Reading the same row twice and obtaining different values.

Phantom read: A changed result set caused by concurrent inserts or deletes.

Lost update: One concurrent write unintentionally overwriting another.

Write skew: An invariant violation caused by transactions updating different rows after seeing a shared condition.

Isolation level: A defined set of guarantees for concurrent visibility.

Shared lock: A lock that permits compatible reads.

Exclusive lock: A lock required for an incompatible write.

MVCC: Multiversion concurrency control using multiple row versions or snapshots.

Deadlock: A cyclic wait among transactions.

WAL: A durable log that must precede relevant data-page writes.

Checkpoint: A recovery reference point that limits recovery work.

Point-in-time recovery: Restoration to a selected time using a backup and transaction logs.

Review Questions

Short Answer Questions

1. What do atomicity and durability guarantee?
2. Distinguish a dirty read from a non-repeatable read.
3. What is the purpose of an exclusive lock?
4. Why can snapshot isolation permit write skew?
5. What is the difference between RPO and RTO?

Long Answer Questions

1. Explain the four ACID properties and show how they apply to a bank transfer.
2. Compare two-phase locking and MVCC, including their benefits and limitations.
3. Describe deadlock formation, detection, prevention, and safe retry handling.
4. Explain WAL, checkpoints, redo, undo, and crash recovery.
5. Compare MySQL/InnoDB and PostgreSQL transaction behavior, including their default isolation levels.

Analytical / Application Questions

1. Two transactions both read an inventory quantity of 1 and then write 0. Identify the anomaly and design a safe SQL transaction strategy.
2. A PostgreSQL application performs two reads in one Read Committed transaction and receives different results. Explain why this can occur and propose alternatives if a stable transaction-wide view is required.
3. A company requires an RPO of five minutes and an RTO of one hour. Design a backup, log-archiving, restoration-testing, and operational-monitoring plan that could satisfy these objectives.

Multiple Choice Questions with Answers

Question 1

Which ACID property ensures that a committed transaction survives a database restart?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 2

Which anomaly occurs when a transaction reads a value written by another transaction that later rolls back?

A. Dirty read

B. Phantom read

C. Lost update

D. Write skew

Correct Answer: A. Dirty read

Question 3

What does strict two-phase locking generally retain until commit or rollback?

A. Only shared locks

B. Write locks

C. Only table locks

D. Only predicate locks

Correct Answer: B. Write locks

Question 4

What does a cycle in a wait-for graph indicate?

A. A checkpoint

B. A serial schedule

C. A deadlock

D. A successful commit

Correct Answer: C. A deadlock

Question 5

What is the principal purpose of write-ahead logging?

A. To sort query results

B. To ensure recovery information is durable before changed pages are written

C. To eliminate all locking

D. To replace database backups

Correct Answer: B. To ensure recovery information is durable before changed pages are written

Question 6

What is PostgreSQL's usual default isolation level?

A. Read Uncommitted

B. Read Committed

C. Repeatable Read

D. Serializable

Correct Answer: B. Read Committed

Question 7

What is commonly true of MySQL InnoDB's usual default isolation level?

A. Repeatable Read

B. Serializable

C. Read Uncommitted

D. No isolation

Correct Answer: A. Repeatable Read

Question 8

Which technique uses multiple row versions so readers can often avoid blocking writers?

A. MVCC

B. Lock escalation

C. Checkpointing

D. Undo-only recovery

Correct Answer: A. MVCC

Question 9

A transaction is aborted because PostgreSQL serializable execution detects a dangerous dependency pattern. What should a robust application usually do?

A. Ignore the error and continue using the same transaction

B. Retry the complete transaction

C. Disable all constraints

D. Delete the conflicting row manually

Correct Answer: B. Retry the complete transaction

Question 10

What is required for point-in-time recovery in addition to a suitable base backup?

A. Only a database schema diagram

B. Archived transaction logs such as WAL or binary logs

C. A larger buffer pool

D. Read Uncommitted isolation

Correct Answer: B. Archived transaction logs such as WAL or binary logs