Transactions, Concurrency, and Data Integrity

Introduction

A database is valuable only when its stored data remains correct while many users and application processes access it simultaneously. In a banking system, an order platform, or a student-registration application, operations rarely consist of one isolated SQL statement. They usually involve a sequence of reads and writes that must succeed together or have no lasting effect. Transactions provide this unit of work.

This chapter examines transactions, the ACID properties, concurrency problems, isolation levels, locking, deadlocks, commit and rollback, and integrity controls. MySQL examples use the InnoDB storage engine, because transaction support, row-level locking, foreign keys, and crash recovery depend on the storage engine. MongoDB is mentioned for comparison, but the practical implementation focus is MySQL.

Learning objectives

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

• explain atomicity, consistency, isolation, and durability;
• distinguish logical integrity constraints from transaction controls;
• identify dirty reads, non-repeatable reads, phantom reads, and lost updates;
• compare MySQL isolation levels and locking mechanisms;
• design commit, rollback, and error-handling workflows;
• diagnose and reduce deadlocks;
• implement safe transactional workflows in MySQL;
• evaluate integrity, security, and operational risks in database applications.

1. Transactions as units of work

A transaction is a logically related sequence of database operations treated as one unit. For example, transferring 100 units from account A to account B requires both a debit and a credit. A successful transfer must not leave only one account changed.

A transaction normally moves through states:

1. Active: statements are executing.
2. Partially committed: the final statement has completed, but the transaction has not necessarily been made durable.
3. Committed: the database guarantees that its effects are permanent under the system's durability assumptions.
4. Failed: an error prevents successful completion.
5. Aborted: changes are undone through rollback.

A transaction boundary is defined by commands such as START TRANSACTION, COMMIT, and ROLLBACK. In MySQL, many data-definition statements cause implicit commits, so schema changes should not be mixed casually with business transactions.

The ACID model

ACID is a set of guarantees, not a promise that an application is automatically correct. A transaction can be atomic and durable while still storing an incorrect price if application validation is wrong. Likewise, a database constraint can preserve a rule, but it cannot by itself decide whether a business process is appropriate.

2. Consistency and integrity controls

Consistency has two related meanings. At the database level, it means that every committed state satisfies defined rules. At the application level, it means that the result makes business sense. Strong database design uses both.

Entity, domain, and referential integrity

• Entity integrity: each row has a stable identity, normally enforced with a primary key.
• Domain integrity: values belong to an allowed domain, enforced through data types, NOT NULL, CHECK, and suitable defaults.
• Referential integrity: a foreign-key value refers to an existing parent row, unless nullability and the model explicitly permit absence.
• Uniqueness integrity: values such as usernames or invoice numbers are not duplicated when uniqueness is required.

Example:

CREATE TABLE customers (
    customer_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    status ENUM('active', 'blocked') NOT NULL DEFAULT 'active'
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    customer_id BIGINT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_order_total CHECK (total >= 0),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE = InnoDB;

A DECIMAL(10,2) column is preferable to floating-point arithmetic for monetary values because it represents fixed decimal precision. The CHECK constraint protects the database even when a second application writes to it. The exact behavior of CHECK constraints should be verified against the deployed MySQL version; modern MySQL 8.0 versions enforce them, whereas older versions accepted but ignored some definitions.

Constraints should be preferred for rules that belong to the data model. Application validation remains necessary for messages, workflows, and rules requiring external information. A unique constraint, for example, is the final protection against two concurrent requests registering the same email; checking first in application code is not sufficient.

3. Concurrency and anomalies

Concurrency allows several transactions to make progress without waiting for every other transaction to finish. It improves throughput and responsiveness, but uncontrolled concurrency can produce incorrect observations or updates.

Consider two transactions, T1 and T2, operating on the same row:

Isolation is a spectrum. Preventing every anomaly can require more locking and can reduce concurrency. The appropriate level depends on the business risk and workload.

4. MySQL isolation levels

MySQL provides four standard isolation levels. InnoDB uses consistent, non-locking reads for ordinary SELECT statements under its multiversion concurrency control (MVCC) model. Locking reads such as SELECT ... FOR UPDATE behave differently.

The precise behavior depends on access type, indexes, predicates, and InnoDB's locking rules. Isolation level is not a substitute for explicit protection of a critical read-modify-write operation.

A session can select an isolation level as follows:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- transactional work
COMMIT;

Use the narrowest scope necessary. A global change affects other workloads and should be made only after measurement and review.

MVCC and consistent reads

MVCC maintains versions so that a reader can view a logically consistent committed state while writers proceed. This often avoids blocking ordinary readers. It does not mean that all operations are conflict-free: updates, deletes, locking reads, unique checks, and foreign-key checks can still wait for locks.

A transaction that must reserve a row for subsequent modification should use a locking read:

SELECT available
FROM inventory
WHERE product_id = 42
FOR UPDATE;

The selected row is locked until commit or rollback. The statement must use a suitable index; otherwise, more rows may be examined and locked than intended.

5. Locking mechanisms

A lock controls concurrent access to a data item. InnoDB commonly uses row-level locks, although the physical and logical effects depend on indexes and predicates. Important concepts include:

• Shared lock: permits compatible reads but generally prevents conflicting writes.
• Exclusive lock: required for changes and prevents incompatible access.
• Record lock: protects an indexed record.
• Gap lock: protects a range between index records under relevant conditions.
• Next-key lock: combines a record lock with a preceding gap lock.
• Intention lock: signals that a transaction intends to place row locks within a table.

A plain SELECT normally does not lock rows for later updating. SELECT ... FOR UPDATE requests exclusive-style protection for rows that will be changed. SELECT ... FOR SHARE requests a shared lock where supported and appropriate.

Locking is useful in two common patterns.

Pessimistic control locks a resource before changing it:

START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 10 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 10;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 20;
COMMIT;

The application must also validate that account 10 has sufficient funds and that both rows exist. A production transfer should lock both accounts in a consistent order, such as ascending account_id, to reduce deadlocks.

Optimistic control allows concurrent work but verifies that the row has not changed. A version column is useful:

UPDATE products
SET price = 19.99, version = version + 1
WHERE product_id = 7 AND version = 3;

If the affected-row count is zero, the application reports a conflict or retries. This approach can reduce waiting when conflicts are rare.

6. Commit, rollback, and savepoints

COMMIT ends the transaction successfully. ROLLBACK reverses changes made since the transaction began, subject to the storage engine and transaction boundaries. A savepoint permits partial rollback:

START TRANSACTION;
INSERT INTO orders(customer_id, total) VALUES (5, 49.90);
SAVEPOINT after_order;

-- Optional operation
INSERT INTO order_audit(order_id, event) VALUES (LAST_INSERT_ID(), 'created');

-- If the optional operation fails:
ROLLBACK TO SAVEPOINT after_order;

COMMIT;

A savepoint does not release all locks acquired by the transaction, and it does not make earlier work durable. Long transactions should be avoided because they retain locks, consume undo resources, and increase the chance of contention.

Applications should use a transaction template:

1. Validate request shape and authorization outside or before the critical transaction where possible.
2. Start a transaction.
3. Read and lock the necessary rows in a deterministic order.
4. Check current values and business rules.
5. Perform all related writes.
6. Commit only after every required operation succeeds.
7. Roll back on exceptions and return a safe error.
8. Retry only errors known to be transient, such as deadlock victims or lock wait timeouts.

Never report success before the commit has succeeded. A connection pool must also reset transaction state before returning a connection to another request.

7. Deadlocks and lock waits

A lock wait occurs when one transaction must wait for another to release a conflicting lock. A deadlock is a cycle of waiting: T1 holds a resource needed by T2, while T2 holds a resource needed by T1. No transaction can proceed without intervention.

Illustrative sequence:

• T1 locks account 10 and requests account 20.
• T2 locks account 20 and requests account 10.
• T1 and T2 wait for one another.

InnoDB detects many deadlocks and rolls back one transaction, called the victim. The application must treat the resulting error as a failed transaction and may retry the complete unit of work with bounded backoff. It must not retry only the last SQL statement, because the transaction may already have been rolled back.

Deadlock prevention and reduction include:

• access shared tables and rows in a consistent order;
• keep transactions short;
• use indexes that match search predicates;
• avoid user interaction or network calls inside a transaction;
• update only necessary rows;
• avoid unnecessary range locks;
• inspect deadlock reports and query plans;
• set sensible lock-wait timeouts;
• implement bounded, observable retries.

Deadlocks are not necessarily evidence of a defective database; they are a normal possibility in concurrent systems. The design goal is to make them rare, quickly recoverable, and visible.

8. Safe MySQL workflow: order placement

The following illustrative case shows how transaction design combines integrity constraints, locking, and error handling.

Background and problem

An online retailer must create an order, reserve stock, and record payment status. Two customers may attempt to buy the last unit of the same product at nearly the same time. A naive workflow reads stock in application code, waits, and then updates it. Both requests may observe one unit and create orders, producing overselling.

Analysis

The stock check and decrement are one business operation. A unique order identifier is useful, but it does not by itself protect inventory. The inventory row must be changed conditionally within a transaction.

Approach

A robust schema might include:

CREATE TABLE inventory (
    product_id BIGINT PRIMARY KEY,
    quantity INT NOT NULL,
    CONSTRAINT chk_quantity CHECK (quantity >= 0)
) ENGINE = InnoDB;

CREATE TABLE order_items (
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT chk_item_quantity CHECK (quantity > 0),
    FOREIGN KEY (product_id) REFERENCES inventory(product_id)
) ENGINE = InnoDB;

A concise transactional implementation is:

START TRANSACTION;

INSERT INTO orders(customer_id, total)
VALUES (5, 29.90);
SET @order_id = LAST_INSERT_ID();

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

-- Application checks that ROW_COUNT() = 1.
INSERT INTO order_items(order_id, product_id, quantity)
VALUES (@order_id, 42, 1);

COMMIT;

The application must roll back if the update affects zero rows, if the product does not exist, if an integrity constraint fails, or if payment authorization is unsuccessful. In a real system, external payment calls are usually not held inside a long database transaction. Instead, an order state machine and an idempotency key coordinate payment attempts and later fulfillment.

Outcome and learning points

The conditional update makes the stock decrement atomic with the availability test. The transaction ensures that an order without its corresponding inventory reservation is not committed. The foreign key prevents an item from referring to an unknown product. A unique idempotency key prevents a client retry from creating duplicate business operations.

This case also illustrates an important boundary: database transactions provide atomicity inside the database, not automatically across an external payment provider, email service, or message broker. Such distributed workflows require patterns such as an outbox table, explicit states, reconciliation, and idempotent consumers.

9. MongoDB comparison

MongoDB provides atomicity for a single-document write and supports multi-document transactions in replica sets and sharded deployments under documented operational conditions. Its document model often permits related data to be embedded so that one business operation affects one document. When data is distributed across documents or collections, a transaction may be appropriate, but it introduces coordination cost.

The same design principles remain relevant: identify the unit of work, enforce schema and validation rules, keep transactions short, handle transient errors, and make retries safe. MongoDB's schema flexibility is not the absence of integrity requirements; validation rules, unique indexes, application checks, and careful modeling are still needed.

10. Testing, observability, and governance

Transaction correctness should be tested under concurrency, not only with single-user unit tests. Useful tests include simultaneous stock purchases, duplicate submissions, forced deadlocks, connection interruption during commit, rollback after constraint failure, and retry behavior.

Operational evidence may include transaction duration, lock-wait time, deadlock count, rollback rate, failed commits, and query-plan changes. MySQL diagnostic tools such as SHOW ENGINE INNODB STATUS, performance schema views, and slow-query analysis can help identify contention. Monitoring must avoid exposing personal or financial data in logs.

Integrity is also a governance concern. Use least-privilege database accounts, parameterized queries, encrypted connections, protected credentials, audited administrative actions, and carefully controlled backups. Constraints reduce accidental corruption, while authorization controls reduce unauthorized change. Neither replaces the other.

11. Limitations and best practices

No isolation level can correct flawed business requirements. Serializable execution may reduce throughput, and read committed may be insufficient for a multi-step decision. Locks can protect rows but cannot automatically coordinate external services. Rollback cannot undo an email already sent. A committed transaction can still contain an incorrect value if the application supplied incorrect input.

Recommended practices are:

• choose InnoDB for transactional MySQL tables;
• define primary keys, foreign keys, unique constraints, suitable types, and check constraints;
• keep transactions short and focused;
• use parameterized SQL;
• lock rows explicitly for read-modify-write operations when required;
• access multiple resources in a consistent order;
• check affected-row counts and commit results;
• handle deadlocks and transient failures with complete bounded retries;
• design idempotent APIs and use idempotency keys;
• test concurrency and failure paths;
• monitor locks, transaction duration, and rollback behavior;
• document isolation assumptions and connection-pool cleanup.

Chapter Summary

Transactions define reliable units of database work. The ACID properties describe atomicity, consistency, isolation, and durability, but each depends on appropriate storage-engine behavior, constraints, and application design. Concurrency improves performance while introducing dirty reads, non-repeatable reads, phantoms, lost updates, and write skew. MySQL isolation levels provide different trade-offs, while MVCC and explicit locking coordinate readers and writers.

Primary keys, foreign keys, unique rules, data types, and check constraints protect structural integrity. Commit makes a completed transaction durable under configured recovery guarantees; rollback removes its uncommitted effects, and savepoints support limited partial recovery. Deadlocks are cycles of lock waits and should be reduced through consistent access order, short transactions, suitable indexes, and complete bounded retries. Safe workflows also require idempotency, observability, security, and explicit coordination with external systems.

Key Terms

ACID: The four properties of reliable transactions: atomicity, consistency, isolation, and durability.

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

Commit: The operation that successfully ends a transaction and makes its effects durable under system guarantees.

Consistency: Preservation of declared and business integrity rules.

Deadlock: A cyclic wait in which transactions hold resources needed by one another.

Durability: Persistence of committed changes after permitted failures.

Foreign key: A constraint linking a child value to a referenced parent key.

Gap lock: A lock protecting an index interval between records.

Isolation: The degree to which concurrent transactions are separated.

Lost update: An update that unintentionally overwrites another transaction's update.

MVCC: Multiversion concurrency control, which provides version-based consistent reads.

Optimistic control: Conflict handling that validates whether data changed before applying an update.

Pessimistic control: Conflict handling that locks data before a critical operation.

Rollback: Reversal of uncommitted transaction changes.

Savepoint: A named position to which part of a transaction can be rolled back.

Serializable: The strongest standard isolation level, approximating serial execution.

Shared lock: A lock compatible with certain reads but generally incompatible with conflicting writes.

Transaction: A logically related sequence of database operations treated as one unit.

Unique constraint: A rule preventing duplicate values in a specified key or column combination.

Review Questions

Short Answer Questions

1. Define a database transaction and explain why an order placement may require one.
2. State the four ACID properties.
3. What is the difference between a dirty read and a non-repeatable read?
4. Why is SELECT ... FOR UPDATE useful in a read-modify-write workflow?
5. What is a deadlock, and how does InnoDB generally respond to one?

Long Answer Questions

1. Compare MySQL's four standard isolation levels and discuss their concurrency trade-offs.
2. Explain how primary keys, foreign keys, unique constraints, data types, and CHECK constraints contribute to data integrity.
3. Describe a complete commit and rollback workflow for a service using a connection pool.
4. Explain MVCC and distinguish ordinary consistent reads from locking reads.
5. Discuss why database transactions do not automatically provide atomicity across a database and an external payment service.

Analytical / Application Questions

1. Two transactions transfer money between the same two accounts, but one locks the source account first and the other locks the destination account first. Identify the risk and propose a precise redesign.
2. An inventory application first executes SELECT quantity, then waits for payment confirmation, and finally decrements quantity. Analyze the concurrency risks and redesign the workflow.
3. A service retries a failed request after a network timeout, but the original transaction may have committed. Design an idempotent strategy that prevents duplicate orders and supports reconciliation.

Multiple Choice Questions with Answers

Question 1

Which ACID property requires all operations in a transaction to succeed together or have no lasting effect?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 2

Which constraint prevents two customers from using the same value where uniqueness is required?

A. Foreign key

B. UNIQUE constraint

C. CHECK constraint

D. DEFAULT clause

Correct Answer: B. UNIQUE constraint

Question 3

What is the principal purpose of SELECT ... FOR UPDATE in InnoDB?

A. To create a backup copy of a table

B. To read rows and lock them for a later modification

C. To change the transaction isolation level

D. To release all existing locks

Correct Answer: B. To read rows and lock them for a later modification

Question 4

Which anomaly occurs when a transaction reads data written by another transaction that has not yet committed?

A. Phantom read

B. Lost update

C. Dirty read

D. Write skew

Correct Answer: C. Dirty read

Question 5

What is a deadlock?

A. A transaction that contains no update statements

B. A cyclic wait among transactions holding conflicting resources

C. A committed transaction whose data is durable

D. A query that uses an index

Correct Answer: B. A cyclic wait among transactions holding conflicting resources

Question 6

Which command permanently completes a successful transaction under the database's durability guarantees?

A. SAVEPOINT

B. ROLLBACK

C. COMMIT

D. TRUNCATE

Correct Answer: C. COMMIT

Question 7

Why should multiple resources commonly be locked in a consistent order?

A. To increase the number of duplicate rows

B. To reduce the likelihood of deadlocks

C. To disable foreign-key checking

D. To force every query to use a table scan

Correct Answer: B. To reduce the likelihood of deadlocks

Question 8

Which technique updates inventory only if sufficient stock still exists?

A. UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42 AND quantity >= 1

B. Reading quantity once and updating it later without a condition

C. Deleting the inventory row before creating the order

D. Using a non-transactional table for the update

Correct Answer: A. UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42 AND quantity >= 1

Question 9

What does a savepoint permit within a transaction?

A. Making part of the transaction durable immediately

B. Rolling back to an intermediate point without ending the whole transaction

C. Preventing all other transactions from reading any table

D. Changing a primary key automatically

Correct Answer: B. Rolling back to an intermediate point without ending the whole transaction

Question 10

Why is an idempotency key useful when a client retries after a network timeout?

A. It guarantees that external services never fail

B. It prevents a repeated request from creating duplicate business effects

C. It removes the need for database constraints

D. It converts a relational database into MongoDB

Correct Answer: B. It prevents a repeated request from creating duplicate business effects