Transactions, Concurrency and Recovery

Introduction

A database is rarely used by one person or one program at a time. In a web application, hundreds or thousands of requests may simultaneously read and modify the same data. A banking transfer, an online order, a payroll update, and an inventory reservation may each involve several SQL statements. If one statement succeeds while another fails, or if two users modify the same row concurrently, the database must preserve correctness.

Transactions, concurrency control, and recovery provide the mechanisms for doing so. A transaction groups related operations into a logical unit. Concurrency control coordinates transactions that overlap in time. Recovery restores a consistent database after failures. Together, these mechanisms support the reliability expected from modern information systems.

This chapter examines the ACID properties, transaction-control statements, serializability, isolation levels, locking, multiversion concurrency control, anomalies, deadlocks, logging, backup, and recovery. The focus is both conceptual and practical: a full-stack developer must know not only how to issue SQL commands, but also how database behavior affects application correctness and performance.

Learning Objectives

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

• explain atomicity, consistency, isolation, and durability;
• begin, commit, roll back, and strategically structure transactions;
• distinguish serial and nonserial schedules and identify common anomalies;
• compare isolation levels, two-phase locking, and multiversion concurrency control;
• diagnose deadlocks, lock waits, and lost updates;
• explain write-ahead logging, checkpoints, backups, and crash recovery;
• select transaction and recovery strategies appropriate to an application;
• evaluate the operational, security, and governance implications of recovery design.

1. Transactions as Logical Units of Work

A transaction is a sequence of database operations that the database treats as one logical unit. Consider a transfer of 500 monetary units from account A to account B:

BEGIN;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 202;

COMMIT;

Both updates must be applied, or neither should be applied. If the first update succeeds and the server fails before the second, the transaction must be undone during rollback or recovery.

A transaction normally has one of three outcomes:

1. Commit: its changes become permanent and visible according to the database system's rules.
2. Rollback: its changes are discarded.
3. Failure followed by recovery: the system determines whether the transaction committed and either redoes or undoes its work.

SQL implementations differ. Some use START TRANSACTION, some use BEGIN TRANSACTION, and some operate in autocommit mode, in which each individual statement is committed automatically. Application developers must understand the configuration rather than assume that several statements form one transaction.

Transaction boundaries

A transaction should include all operations that must succeed together, but no unrelated work. Holding a transaction open while waiting for user input, calling a slow external service, or performing lengthy computation increases lock duration and reduces throughput. A good transaction is generally short, purposeful, and explicit.

Savepoints provide partial rollback:

SAVEPOINT before_discount;

UPDATE order_items
SET unit_price = unit_price * 0.90
WHERE order_id = 7001;

ROLLBACK TO SAVEPOINT before_discount;

A savepoint does not commit work. It marks a point to which the current transaction can return. It is useful when a transaction contains optional stages, but excessive savepoints can complicate application logic.

2. The ACID Properties

Atomicity

Atomicity means that a transaction's operations are treated as an indivisible unit: all required effects occur, or none occur. Atomicity protects against partial execution caused by statement errors, process failures, or machine crashes.

Atomicity does not mean that every transaction must contain only one SQL statement. It means that the database provides an all-or-nothing boundary for the transaction's changes.

Consistency

Consistency means that a committed transaction takes the database from one valid state to another valid state, preserving declared and application-level rules. Examples include:

• a primary key remains unique;
• an account balance cannot be negative where that rule is enforced;
• every order references an existing customer;
• the total debited equals the total credited in a transfer.

The database can enforce some rules with primary keys, foreign keys, unique constraints, checks, and triggers. Other rules must be enforced by carefully designed transactions and application logic. ACID consistency is not a guarantee that incorrect input becomes correct; a transaction that inserts a wrong but syntactically valid value may still commit.

Isolation

Isolation concerns the visibility and interference of concurrent transactions. Ideally, each transaction behaves as if it were executing alone. Complete isolation can reduce performance, so database systems provide levels that balance correctness and concurrency.

Isolation is often described using schedules. A schedule is the time-ordered sequence of operations from several transactions. A serial schedule executes one transaction completely before starting another. A nonserial schedule interleaves operations. Nonserial schedules can be safe if they are equivalent in effect to some serial schedule.

Durability

Durability means that once a transaction is committed, its effects survive subsequent failures, subject to the system's documented guarantees. Durability depends on stable storage, transaction logs, flushing policies, replication, and backup procedures. A success response should not be issued before the database has met the configured durability requirement.

3. Concurrency Anomalies

Concurrency improves throughput and responsiveness, but uncontrolled interleaving can produce incorrect results.

Lost update

Suppose two transactions read a product quantity of 10. Transaction T1 subtracts 2 and writes 8. T2, using its earlier read of 10, subtracts 3 and writes 7. The intended result is 5, but T1's update is lost. A safe design can use an atomic expression:

UPDATE products
SET quantity = quantity - 3
WHERE product_id = 45
  AND quantity >= 3;

The application should inspect the affected-row count. Alternatively, it can lock the row or use optimistic version checking, such as WHERE product_id = 45 AND version = 8.

Dirty read

A dirty read occurs when T2 reads data written by T1 before T1 commits. If T1 later rolls back, T2 has acted on a value that never became valid. Dirty reads are generally unacceptable for financial, inventory, and authorization decisions.

Nonrepeatable read

T1 reads a row. T2 changes and commits that row. When T1 reads it again during the same transaction, it obtains a different value. The transaction has not observed a stable version of the row.

Phantom read

T1 queries all rows satisfying a predicate, such as WHERE status = 'OPEN'. T2 inserts a new matching row and commits. If T1 repeats the query, an additional row appears—a phantom. Preventing phantoms requires protection of the predicate or an equivalent range of keys, not merely the rows already found.

Write skew

Under some snapshot-based isolation schemes, two transactions read related rows, each concludes that its own change is safe, and both commit. Their combined result violates a multi-row rule. For example, two doctors may each see that another doctor is on call and both remove themselves, leaving nobody on call. Snapshot visibility alone does not automatically enforce every business constraint.

4. Serializability and Isolation Levels

A schedule is conflict-serializable when its conflicting operations can be rearranged into a serial schedule without changing the result. Two operations conflict when they belong to different transactions, access the same data item, and at least one is a write.

A precedence graph represents transactions as nodes. An edge from T1 to T2 indicates that T1's conflicting operation occurs before T2's. If the graph contains a cycle, the schedule is not conflict-serializable. Acyclicity is therefore a practical test for conflict serializability.

SQL standards describe isolation levels, but exact behavior can vary between database products. The commonly discussed levels are:

The names should not be treated as identical implementations. For example, one system's repeatable-read mode may use locking, while another uses snapshots. Consult the product documentation and test the actual workload.

Choosing an isolation level

Read committed is often suitable for ordinary web requests where each statement can observe current committed data. Repeatable read or snapshot isolation is useful when a transaction performs several related reads and needs a consistent view. Serializable is appropriate when correctness depends on a strong invariant and the application can tolerate blocking or serialization failures.

Isolation is not a substitute for constraints. A uniqueness constraint, foreign key, or check constraint expresses a rule directly and allows the database to protect it reliably.

5. Locking and Two-Phase Locking

A lock is a control mechanism that regulates access to a database object. Common lock modes include:

• Shared lock (S): permits compatible reads but generally prevents conflicting writes.
• Exclusive lock (X): permits the holder to read and write while excluding incompatible access.

A simplified compatibility table is:

Actual DBMS lock matrices include intent locks, update locks, key-range locks, and other modes. Locks may apply to rows, pages, tables, index ranges, or metadata.

Two-phase locking (2PL) divides a transaction into a growing phase, during which it acquires locks, and a shrinking phase, during which it releases locks and acquires no new ones. Strict 2PL holds write locks until commit or rollback. Strict 2PL supports serializability and prevents other transactions from reading uncommitted writes, although it can produce waits and deadlocks.

Lock escalation replaces many fine-grained locks with a coarser lock, such as a table lock. It can reduce lock-management overhead but may reduce concurrency. Developers should avoid assuming that a row-level request always results in only one physical row lock.

Explicit locking may be expressed differently across systems. A common pattern is:

BEGIN;

SELECT balance
FROM accounts
WHERE account_id = 101
FOR UPDATE;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 101;

COMMIT;

The FOR UPDATE syntax is product-dependent. It requests protection appropriate for a subsequent update, but its exact interaction with other isolation modes must be verified.

6. Multiversion Concurrency Control

Multiversion concurrency control, or MVCC, maintains multiple committed versions of data. A reader can use a version that was valid at its snapshot time, while a writer creates a newer version rather than immediately overwriting the reader's view.

MVCC can greatly reduce read-write blocking and is well suited to web workloads with many reads. It does not eliminate all coordination. Writers may conflict, old versions must eventually be cleaned up, and long-running transactions can prevent version reclamation or increase storage pressure.

MVCC implementations differ in whether they use transaction identifiers, timestamps, undo records, or combinations of these. A snapshot often guarantees that a transaction sees a consistent set of committed data as of a point in time, but snapshot isolation is not always serializable. Applications that enforce cross-row invariants may need explicit locks, serializable isolation, constraints, or retry logic.

7. Deadlocks and Lock Management

A deadlock occurs when transactions wait for one another in a cycle. For example:

• T1 locks row A and requests row B.
• T2 locks row B and requests row A.

Neither can proceed. A wait-for graph models transactions as nodes and waiting relationships as directed edges. A cycle indicates a deadlock.

Database systems commonly detect deadlocks and abort one victim transaction. The application must then retry when the error is classified as retryable. A retry should use bounded attempts and backoff; blindly retrying can amplify overload.

Best practices include:

1. Access shared resources in a consistent order.
2. Keep transactions short.
3. Avoid user interaction and remote calls inside transactions.
4. Index search predicates so updates do not scan and lock excessive rows.
5. Lock only what is needed.
6. Make retry logic idempotent and observable.
7. Distinguish deadlocks from ordinary validation failures.

Deadlock prevention and deadlock detection are different. Consistent lock ordering prevents many cycles; detection identifies cycles after they form. Timeout-based cancellation is useful but may cancel a slow transaction that is not part of a cycle.

8. Logging and Crash Recovery

A transaction log records changes and transaction events so that the system can recover after failure. A central principle is write-ahead logging (WAL): the relevant log record must reach stable storage before the corresponding changed data page is written to permanent storage. This allows the system to redo committed work and undo incomplete work.

A simplified log may contain records such as:

<T1, begin>
<T1, account 101, old 2000, new 1500>
<T1, account 202, old 800, new 1300>
<T1, commit>

During recovery, the system examines log records and determines which transactions committed. It may:

• redo changes of committed transactions that were not yet reflected in data pages;
• undo changes of transactions that were active but incomplete at the time of failure.

Many systems use variations of the ARIES recovery approach, involving analysis, redo, and undo phases. The details differ, but the general goals are the same: establish the transaction state, repeat necessary logged actions, and remove incomplete effects.

A checkpoint records recovery information and reduces how far back the system must scan the log. A checkpoint does not replace a backup. It is a recovery optimization, whereas a backup is a separate copy of database data.

Recovery objectives

Two useful operational measures are:

• 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.

For example, an RPO of 15 minutes means that losing up to 15 minutes of committed changes may be acceptable. An RTO of 60 minutes means service should be restored within one hour. These are business requirements, not properties guaranteed automatically by a DBMS.

9. Backup and Restore Strategies

A full backup copies the complete database at a point in time. An incremental backup copies changes since a previous backup, while a differential backup copies changes since a selected full backup. A practical strategy may combine full backups, transaction-log archival, replication, and tested restore procedures.

Replication improves availability but is not automatically a backup. A mistaken deletion or corrupted transaction may be replicated to every online copy. Backups should be isolated, access-controlled, encrypted where appropriate, and tested through restoration exercises. The rule that a backup exists is weaker than evidence that it can be restored.

A recovery plan should specify:

• backup frequency and retention;
• log archival and storage locations;
• encryption and key management;
• restoration order and dependencies;
• validation of recovered data;
• responsibilities and escalation procedures;
• periodic disaster-recovery tests.

10. Worked Application: Inventory Reservation

Consider an online retailer with 12 units of a product. Two customers concurrently request 8 units each. A correct system must not reserve 16 units.

A weak implementation performs these steps independently:

1. read quantity, obtaining 12;
2. check that 8 ≤ 12;
3. write quantity 4.

Both requests can read 12 and both write 4. The database ends with a quantity that appears plausible, but one reservation is not represented correctly.

A safer atomic update is:

BEGIN;

UPDATE inventory
SET available_quantity = available_quantity - 8
WHERE product_id = 91
  AND available_quantity >= 8;

-- Application checks that exactly one row was updated.
-- If zero rows were updated, the reservation fails.

INSERT INTO reservations(order_id, product_id, quantity)
VALUES (5012, 91, 8);

COMMIT;

The predicate makes the quantity test and decrement one database operation. The insert and decrement are in one transaction, so a failure in either operation rolls back both. A unique constraint on a reservation identifier can prevent duplicate retries. Under high contention, the application should handle deadlocks or serialization failures by retrying the complete transaction, not merely the last statement.

This example illustrates an important design principle: correctness often comes from expressing a business rule as an atomic database operation and enforcing it with constraints, rather than relying on a read-then-write sequence in application code.

11. Illustrative Case Study: University Registration

Background

A university registration service allows students to enroll in courses with limited capacity. Course CS301 has 30 seats and several students submit requests at the same time.

Problem

A naive application reads the remaining capacity, confirms that a seat exists, and then inserts an enrollment. During a burst of requests, more than 30 enrollments may be accepted. A second problem occurs when a student double-clicks the registration button and receives duplicate enrollments.

Analysis

The capacity check and enrollment insert are a business transaction. Separate requests may perform the check concurrently. The duplicate-submission problem is distinct: even a perfectly isolated transaction could accept two different requests unless student-course uniqueness is enforced.

Approach

The database design includes:

ALTER TABLE enrollments
ADD CONSTRAINT uq_student_course
UNIQUE (student_id, course_id);

Registration then uses a transaction that either locks the course row or performs a guarded atomic update:

BEGIN;

UPDATE courses
SET seats_remaining = seats_remaining - 1
WHERE course_id = 301
  AND seats_remaining > 0;

-- Continue only if one row was updated.

INSERT INTO enrollments(student_id, course_id)
VALUES (8842, 301);

COMMIT;

If the insert violates the unique constraint, the transaction is rolled back or handled according to the DBMS's transaction semantics. The application reports a clear outcome to the student. For high-demand courses, serializable transactions or a queue may be appropriate, but both introduce latency and operational complexity.

Outcome

The guarded update prevents the seat count from becoming negative, and the uniqueness constraint prevents duplicate enrollment. Monitoring records lock waits, deadlocks, transaction duration, and rejected requests. Restore testing protects registration data against infrastructure failure.

Learning points

• A check followed by a later update can be unsafe under concurrency.
• Constraints are essential even when application validation exists.
• User-facing retries require idempotency.
• Performance and correctness must be evaluated together.

12. Professional, Ethical, and Governance Considerations

Transaction design affects more than technical performance. Incorrect concurrency control can charge customers twice, oversell products, deny access unfairly, or corrupt academic and medical records. Recovery procedures must protect confidentiality as well as availability. Backups may contain personal data and therefore require access controls, retention policies, encryption, and legally appropriate disposal.

Audit logs should distinguish business events from low-level database logs. A transaction log is primarily a recovery mechanism and may not provide a complete explanation of who authorized a business action. Systems handling regulated data should define retention, auditability, least privilege, separation of duties, and incident-response procedures.

AI-assisted coding tools can generate transaction code, but generated code must be reviewed. Common risks include omitted rollback handling, accidental autocommit, retries that duplicate side effects, and use of an isolation level inappropriate for the invariant. Tests should include concurrent execution, failure injection, deadlock simulation, restore validation, and boundary conditions.

13. Best Practices Checklist

• Define the business unit of work before writing SQL.
• Make transaction boundaries explicit.
• Keep transactions short and avoid external calls within them.
• Use database constraints for invariants whenever possible.
• Select isolation based on correctness requirements, not habit.
• Use atomic conditional updates for counters and quotas.
• Establish a consistent order for acquiring multiple locks.
• Treat deadlocks and serialization failures as controlled retry cases.
• Make externally retried operations idempotent.
• Monitor transaction duration, lock waits, deadlocks, log growth, and rollback rates.
• Maintain tested backups and documented RPO and RTO targets.
• Protect backups and logs as sensitive data.
• Test crash recovery and restoration regularly.

Challenges and Limitations

No concurrency method removes every trade-off. Strong isolation can reduce throughput and increase aborts. Fine-grained locking improves concurrency but increases management overhead. MVCC reduces read blocking but can consume storage and does not automatically prevent write skew. Distributed transactions introduce coordination costs and failure modes across services. Replication can improve availability while increasing consistency complexity.

Furthermore, database guarantees stop at the transaction boundary. If a transaction commits and then an email or payment request fails, the database cannot automatically undo the external side effect. Outbox patterns, message queues, compensation, and idempotency are commonly used to coordinate databases with external systems, but each requires deliberate design.

Chapter Summary

Transactions provide a logical unit of work and are governed by atomicity, consistency, isolation, and durability. Atomicity prevents partial results; consistency preserves rules; isolation controls interference among concurrent transactions; and durability preserves committed work after failure.

Concurrent schedules can cause lost updates, dirty reads, nonrepeatable reads, phantoms, and write skew. Serializability provides a strong correctness model, while practical systems offer isolation levels that trade protection against concurrency. Locking and two-phase locking coordinate access through shared and exclusive locks, whereas MVCC allows readers to use committed versions with less read-write blocking.

Deadlocks arise from cyclic waiting and require prevention, detection, or retry. Write-ahead logging, checkpoints, redo and undo processing, backups, and restoration procedures support crash recovery and disaster readiness. Effective database engineering combines SQL mechanisms with constraints, observability, idempotent application behavior, security controls, and tested operational procedures.

Key Terms

Transaction: A logical unit of database work.

Commit: The successful completion of a transaction.

Rollback: The cancellation of a transaction's uncommitted changes.

Savepoint: A named point within a transaction used for partial rollback.

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

Consistency: Preservation of database and business rules across committed transactions.

Isolation: Control over the effects transactions have on one another.

Durability: Persistence of committed changes despite failure.

Schedule: The time-ordered operations of one or more transactions.

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

Dirty read: Reading data written by an uncommitted transaction.

Lost update: An update overwritten by a concurrent update.

Phantom read: A changed result set caused by inserted or deleted matching rows.

Lock: A mechanism that controls concurrent access to data.

Deadlock: Cyclic waiting among transactions.

MVCC: Multiversion concurrency control using multiple data versions.

Write-ahead logging: Requiring log records to be stored before changed data pages.

Checkpoint: A recovery marker that limits log-processing work.

RPO: Maximum acceptable data loss measured in time.

RTO: Maximum acceptable restoration time.

Review Questions

5 Short Answer Questions

1. What does atomicity guarantee in a transaction?
2. Distinguish a dirty read from a nonrepeatable read.
3. What is the purpose of a savepoint?
4. Why can a replication system fail to serve as a backup?
5. What condition in a wait-for graph indicates a deadlock?

5 Long Answer Questions

1. Explain all four ACID properties using a bank-transfer example.
2. Compare read committed, repeatable read, snapshot-style isolation, and serializable isolation.
3. Explain strict two-phase locking and discuss its effects on correctness and performance.
4. Describe write-ahead logging and the roles of redo, undo, and checkpoints in recovery.
5. Discuss how backup design, RPO, RTO, encryption, and restore testing contribute to operational resilience.

3 Analytical / Application Questions

1. Two transactions read the same inventory quantity and then independently subtract different quantities. Identify the anomaly and design a safe SQL-based solution.
2. A system experiences frequent deadlocks because one transaction locks customer then order, while another locks order then customer. Diagnose the cause and propose at least three remedies.
3. Design a registration transaction for a course with limited seats and unique student enrollment. Identify required constraints, transaction boundaries, failure cases, and retry behavior.

Multiple Choice Questions with Answers

Question 1

Which ACID property ensures that a committed transaction survives a database-server crash?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: D. Durability

Question 2

Two transactions read the same value, and the later write of one transaction overwrites the other's update. What anomaly occurred?

A. Dirty read

B. Lost update

C. Phantom read

D. Deadlock

Correct Answer: B. Lost update

Question 3

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

A. A serial schedule

B. A successful checkpoint

C. A deadlock

D. A committed transaction

Correct Answer: C. A deadlock

Question 4

Which isolation level provides the strongest usual guarantee that concurrent execution behaves like serial execution?

A. Read Uncommitted

B. Read Committed

C. Repeatable Read

D. Serializable

Correct Answer: D. Serializable

Question 5

What is the primary purpose of a shared lock?

A. To permit compatible reads while controlling conflicting writes

B. To force every transaction to roll back

C. To delete old row versions

D. To archive transaction logs

Correct Answer: A. To permit compatible reads while controlling conflicting writes

Question 6

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

A. The transaction must be retried

B. The relevant log record must be stored

C. All other transactions must commit

D. A full backup must finish

Correct Answer: B. The relevant log record must be stored

Question 7

Which design best prevents overselling inventory in a concurrent system?

A. Read the quantity, wait, and update later

B. Use an atomic conditional update and check affected rows

C. Allow dirty reads for greater speed

D. Disable transaction logging

Correct Answer: B. Use an atomic conditional update and check affected rows

Question 8

What is a defining characteristic of MVCC?

A. It stores multiple versions so readers can use an appropriate committed version

B. It prevents all forms of write conflict

C. It requires every read to acquire an exclusive table lock

D. It eliminates the need for backups

Correct Answer: A. It stores multiple versions so readers can use an appropriate committed version

Question 9

What does RPO measure?

A. The maximum acceptable time to restore service

B. The number of replicas required

C. The maximum acceptable amount of data loss measured in time

D. The average transaction duration

Correct Answer: C. The maximum acceptable amount of data loss measured in time

Question 10

Which practice most directly reduces deadlocks caused by inconsistent lock ordering?

A. Holding transactions open during user input

B. Accessing shared resources in a consistent order

C. Disabling all indexes

D. Using read uncommitted for every transaction

Correct Answer: B. Accessing shared resources in a consistent order