WEB-201 · Lesson 8 of 12 · 30 min

Transactions and integrity constraints

▶ Video coming soon A video version of this lesson is on the way.

Lucy's worst Saturday at the Food Hub had been in June 2025, when five people turned up for a kitchen shift with room for two. Two had booked by phone with Mira, two by email with Lucy, and one had been added by a staff member who saw an empty cell. "On the new site, that can't happen," Lucy said. On Friday, Theo showed Mira why it still could, even with a database, unless the booking code used transactions and constraints properly. "The database gives you the tools," he said. "You still have to use them."

This lesson explains transactions, which make a group of changes all-or-nothing and keep concurrent users from corrupting each other's work, and integrity constraints, the rules the database checks on every change. You will see the ACID properties, how to commit and roll back from code, the anomalies that concurrency causes, the isolation levels that prevent them, and a booking method that can never overfill a shift.

What a transaction is

A transaction is a sequence of statements that the database treats as a single unit. Either every change in it is made permanent (committed), or none of them is (rolled back). When Ben swaps his Monday grocery shift for Wednesday's kitchen shift, the application cancels one signup and creates another. If the second step fails, because the kitchen shift filled up a moment ago, the first must be undone; otherwise Ben has lost his Monday shift and gained nothing.

Ben's shift swap as one transaction
BEGIN;

UPDATE signups SET status = 'cancelled'
WHERE volunteer_id = 2 AND shift_id = 4;

INSERT INTO signups (volunteer_id, shift_id)
VALUES (2, 6);

COMMIT;   -- or ROLLBACK; to undo both changes

Outside an explicit transaction, most databases run in autocommit mode: each statement is its own transaction and is committed as soon as it succeeds. That is fine for single statements and wrong for any change that takes more than one. The guarantees a transaction gives are summed up by the acronym ACID.

The ACID properties
PropertyMeaningIn the shift swap
AtomicityAll the changes happen, or none doBen never ends up with the old shift canceled and no new one
ConsistencyEvery committed state satisfies all constraintsNo signup ever refers to a shift that does not exist
IsolationConcurrent transactions do not see each other's unfinished workAnother volunteer never sees Ben half-way through the swap
DurabilityOnce committed, a change survives crashesAfter the confirmation page appears, a power cut does not lose the swap

Durability comes from the database writing each committed change to a log on disk before it reports success: PostgreSQL's write-ahead log, MySQL InnoDB's redo log, SQLite's journal or write-ahead log file. After a crash, the database replays the log to recover committed work and discards uncommitted work.

Transactions from code

Every driver has a way to begin, commit and roll back. The pattern is always the same: begin, do the work, commit if everything succeeded, roll back if anything raised an error. In Python's sqlite3, using the connection in a with block does exactly that: it commits when the block ends normally and rolls back if an exception escapes.

Python Example · A with block makes the two-step swap all-or-nothing
import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute("PRAGMA foreign_keys = ON")
conn.executescript("""
    CREATE TABLE shifts (shift_id INTEGER PRIMARY KEY, spots INTEGER NOT NULL);
    CREATE TABLE signups (volunteer_id INTEGER, shift_id INTEGER NOT NULL REFERENCES shifts(shift_id),
                          status TEXT NOT NULL DEFAULT 'booked', PRIMARY KEY (volunteer_id, shift_id));
    INSERT INTO shifts VALUES (4, 4), (6, 2);
    INSERT INTO signups (volunteer_id, shift_id) VALUES (2, 4);
""")

def swap(volunteer_id, old_shift, new_shift):
    try:
        with conn:   # one transaction: commit on success, roll back on error
            conn.execute("UPDATE signups SET status = 'cancelled' WHERE volunteer_id = ? AND shift_id = ?",
                         (volunteer_id, old_shift))
            conn.execute("INSERT INTO signups (volunteer_id, shift_id) VALUES (?, ?)",
                         (volunteer_id, new_shift))
        return 'Swapped'
    except sqlite3.IntegrityError as err:
        return f'Swap undone ({err})'

print(swap(2, 4, 99))
print(conn.execute("SELECT * FROM signups").fetchall())
print(swap(2, 4, 6))
print(conn.execute("SELECT * FROM signups ORDER BY shift_id").fetchall())
Output
Swap undone (FOREIGN KEY constraint failed)
[(2, 4, 'booked')]
Swapped
[(2, 4, 'canceled'), (2, 6, 'booked')]

What this code does

Sets up two shifts and one booking for Ben, then defines a swap that cancels his old booking and adds a new one as a single unit. Swapping to shift 99, which does not exist, fails on the second step, so the cancellation is undone too and Ben's original booking is still there. Swapping to shift 6 succeeds, and both changes are kept.

The other languages look alike. PDO has beginTransaction(), commit() and rollBack(); JDBC has setAutoCommit(false), commit() and rollback(); with the pg package in Node.js you take one client from the pool and send BEGIN, then COMMIT or ROLLBACK on that same client. All statements in a transaction must use the same connection, which is a common mistake with connection pools: running BEGIN on one pooled connection and the next statement on another means the transaction is not doing anything.

Keep transactions short. A transaction that is open holds locks that can make other users wait, so never keep one open while waiting for a person to click something, or while calling a slow outside service such as an email provider. Do the database work, commit, then send the confirmation email.

A savepoint marks a point inside a transaction that you can roll back to without abandoning the whole transaction: SAVEPOINT before_extras; ... ROLLBACK TO before_extras;. Savepoints are useful when a long import should skip bad rows but keep the good ones, and they are how many frameworks implement nested transactions.

Integrity constraints

Constraints are rules declared in the schema and checked by the database on every INSERT and UPDATE, whichever application, script or person makes the change. Validation in the application gives users friendly messages; constraints in the database guarantee the rules hold. You need both. The table lists the constraints you will use, all of which appeared in the sample schema.

Integrity constraints
ConstraintRuleFood Hub example
NOT NULLThe column must have a valueEvery volunteer has an email
UNIQUENo two rows have the same value (NULLs aside)An email belongs to one volunteer; an area name is used once
PRIMARY KEYUNIQUE and NOT NULL; identifies each rowvolunteer_id; the pair (volunteer_id, shift_id) in signups
FOREIGN KEYThe value must match a row in the referenced tableEvery signup refers to a real shift and a real volunteer
CHECKA condition on the row must be truespots > 0; end_time > start_time; status from a fixed list
DEFAULTThe value used when none is given (not strictly a constraint)status starts as 'booked'

A CHECK constraint can compare columns of the same row. A shift whose end time is before its start time is nonsense, and the database can refuse it. CHECK constraints cannot look at other rows or other tables, so "no more signups than spots" cannot be a CHECK; that rule needs the transaction technique later in this lesson.

A cooking classes table with table-level CHECK constraints
CREATE TABLE classes (
  class_id  INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  starts_at TEXT NOT NULL,
  ends_at   TEXT NOT NULL,
  capacity  INTEGER NOT NULL DEFAULT 12,
  CHECK (ends_at > starts_at),
  CHECK (capacity BETWEEN 1 AND 30)
);

INSERT INTO classes (title, starts_at, ends_at)
VALUES ('Soups for winter', '2026-11-24 18:00', '2026-11-24 20:00');

SELECT class_id, title, capacity FROM classes;

-- Result: 1 row
-- class_id | title            | capacity
-- ---------+------------------+---------
-- 1        | Soups for winter | 12

UNIQUE constraints treat NULLs specially: in most databases two rows may both have NULL in a UNIQUE column, because NULL is not equal to NULL. A UNIQUE constraint can also cover several columns, such as UNIQUE (area_id, shift_date, start_time) to stop the same shift being entered twice.

What happens on delete

A foreign key also says what should happen to referencing rows when the referenced row is deleted or its key is changed. Choosing deliberately is part of designing each relationship.

Foreign key actions
ActionEffect when the parent row is deletedSuits
NO ACTION or RESTRICT (default)The delete is refused while child rows existMost relationships; forces you to deal with the children first
CASCADEThe child rows are deleted tooRows that are meaningless without the parent, such as the lines of an order
SET NULLThe foreign key in child rows is set to NULLOptional links, such as an area whose lead staff member leaves
SET DEFAULTThe foreign key is set to its default valueRare; for example reassigning to a general area

Theo uses CASCADE sparingly. Declaring signups ON DELETE CASCADE from volunteers would make deleting a volunteer silently erase their hours from every report, which is why lesson 7 anonymizes volunteers rather than deleting them. A shift's signups, on the other hand, really are meaningless without the shift, so a canceled shift could cascade to its signups, though the Food Hub prefers to mark the shift canceled and notify the volunteers.

Concurrency: when users collide

A busy database runs many transactions at once. Without isolation, they could interfere in four classic ways, called anomalies.

Concurrency anomalies
AnomalyWhat happensFood Hub example
Dirty readA transaction reads another's uncommitted change, which is later rolled backA page shows Ben on the kitchen shift, then his swap fails
Non-repeatable readReading the same row twice gives different values because another transaction committed a changeThe spots on a shift change between two reads in one report
Phantom readRepeating a query returns new rows that another transaction insertedA count of signups changes during a transaction
Lost updateTwo transactions read, change and write the same value; one change is overwrittenTwo bookings both read "1 spot left" and both book it
A timeline with two transactions, A for Esther and B for Diego. Both read that the kitchen shift has one spot left. A inserts a signup and commits; B also inserts a signup and commits. The shift ends with three signups for two spots.
How a check-then-act booking overfills a shift. Both transactions see one spot left before either has committed.

Isolation levels

The SQL standard defines four isolation levels, which trade protection against anomalies for concurrency. Higher levels prevent more anomalies but can make transactions wait for each other or fail and need retrying.

Standard isolation levels
LevelDirty readNon-repeatable readPhantomDefault in
Read uncommittedPossiblePossiblePossibleRarely used (PostgreSQL treats it as read committed)
Read committedPreventedPossiblePossiblePostgreSQL, SQL Server, Oracle
Repeatable readPreventedPreventedPossible in the standard (prevented in PostgreSQL)MySQL InnoDB
SerializablePreventedPreventedPreventedSQLite (which allows one writer at a time)

The key lesson is that the common default, read committed, does not prevent the overbooking in the figure. Each booking transaction reads the count of signups, sees a free spot, and inserts. Both reads happen before either insert is committed, so both see the same free spot. Running in a transaction is necessary but not sufficient; the booking must also be written so that the check and the change cannot be split.

Booking without overbooking

There are three reliable ways to make "book if there is a spot" safe. Each makes the check and the change happen as one indivisible step.

  • Lock the shift row. In PostgreSQL or MySQL, start a transaction and read the shift with SELECT ... FOR UPDATE. That locks the row, so a second booking for the same shift waits until the first commits, and then sees the updated count.
  • Conditional update of a counter. Keep a booked count on the shift and run UPDATE shifts SET booked = booked + 1 WHERE shift_id = ? AND booked < spots. The database applies the condition and the change to the row atomically; if the row count is 0, the shift was full. Then insert the signup in the same transaction.
  • Serializable isolation. Run the booking at the serializable level. The database detects that two transactions conflicted and makes one fail with a serialization error, which the application catches and retries.
Row locking with FOR UPDATE in PostgreSQL
-- PostgreSQL: lock the shift, count, then insert, in one transaction
BEGIN;
SELECT spots FROM shifts WHERE shift_id = 6 FOR UPDATE;   -- a second booker waits here
SELECT COUNT(*) FROM signups WHERE shift_id = 6 AND status = 'booked';
-- the application compares the count with spots, then either:
INSERT INTO signups (volunteer_id, shift_id) VALUES (8, 6);
COMMIT;
-- or ROLLBACK; and tells the volunteer the shift is full

SQLite allows only one writing transaction at a time, so a single statement that checks and inserts is already safe there. The runnable example uses that form. Each attempt is one INSERT ... SELECT that inserts a row only if the number of booked signups is below the number of spots, and the row count says whether it worked.

Python Example · A check-and-insert in one statement cannot overfill the shift
import sqlite3

conn = sqlite3.connect(':memory:')
conn.executescript("""
    CREATE TABLE shifts (shift_id INTEGER PRIMARY KEY, spots INTEGER NOT NULL);
    CREATE TABLE signups (volunteer_id INTEGER, shift_id INTEGER,
                          status TEXT NOT NULL DEFAULT 'booked', PRIMARY KEY (volunteer_id, shift_id));
    INSERT INTO shifts VALUES (6, 2);
""")

BOOK = """
    INSERT INTO signups (volunteer_id, shift_id)
    SELECT ?, sh.shift_id FROM shifts AS sh
    WHERE sh.shift_id = ?
      AND (SELECT COUNT(*) FROM signups AS s
           WHERE s.shift_id = sh.shift_id AND s.status = 'booked') < sh.spots
"""

def book(volunteer_id, shift_id):
    with conn:
        cur = conn.execute(BOOK, (volunteer_id, shift_id))
    return 'Booked' if cur.rowcount == 1 else 'Shift full'

for person in (4, 5, 8):
    print(f'Volunteer {person}:', book(person, 6))
print(conn.execute("SELECT COUNT(*) FROM signups WHERE shift_id = 6").fetchone()[0], 'of 2 spots taken')
Output
Volunteer 4: Booked
Volunteer 5: Booked
Volunteer 8: Shift full
2 of 2 spots taken

What this code does

Makes a kitchen shift with two spots and a booking statement that adds a booking only when fewer people are booked than there are spots. Three volunteers try in turn: the first two get a spot and the third is told the shift is full. The final line confirms exactly two of the two spots are taken.

In PostgreSQL the same single statement is not enough at read committed, because two transactions can evaluate the subquery at the same moment. Add the FOR UPDATE lock on the shift row first, or use the counter update, or serializable isolation. Theo's notes for the PHP back end say: lock the shift row, then count and insert, and test it by firing twenty simultaneous booking requests at a two-spot shift.

Deadlocks and retries

When two transactions each hold a lock the other needs, neither can continue: a deadlock. Databases detect deadlocks and cancel one of the transactions with an error. Serializable transactions can also fail with a serialization error. Both are normal under load, and the right response is to roll back and retry the whole transaction a few times. You make deadlocks less likely by keeping transactions short and by always locking rows in the same order, such as always the shift before the volunteer.

Worked problem

Choose the foreign key actions

For each link, choose an ON DELETE action: (a) signups to volunteers; (b) the lines of a donation receipt to the receipt; (c) areas.lead_staff_id to staff.

Show solution
  1. (a) Signups carry the hours history, and volunteers are anonymized rather than deleted, so RESTRICT (the default) protects the history.
  2. (b) A receipt line means nothing without its receipt, so CASCADE is appropriate.
  3. (c) The area should survive its lead leaving, with the lead unknown until a new one is chosen, so SET NULL, with the column allowed to be NULL.

Answer: (a) RESTRICT, (b) CASCADE, (c) SET NULL.

Worked problem

Is this booking code safe?

PHP code on PostgreSQL at read committed: begin; SELECT COUNT(*) of booked signups; if the count is below spots, INSERT; commit. Two volunteers book the last spot at the same moment. What happens, and what is the smallest fix?

Show solution
  1. Both transactions run the COUNT before either INSERT commits, so both see one spot left.
  2. Both insert and commit, and the shift is overbooked by one.
  3. Add SELECT spots FROM shifts WHERE shift_id = :id FOR UPDATE as the first statement. The second transaction waits at that line until the first commits, then counts the new signup and finds the shift full.

Answer: Unsafe: a lost update. Lock the shift row with FOR UPDATE before counting.

Common mistakes

  • MistakeRunning the statements one after another in autocommit mode is the same as a transaction.

    InsteadIn autocommit each statement commits on its own. A failure part way leaves earlier changes in place. Use BEGIN and COMMIT.

  • MistakeBeing inside a transaction prevents overbooking.

    InsteadAt the common read committed level, two transactions can both read the same free spot. Lock the row, use a conditional update or use serializable isolation.

  • MistakeValidation in the application makes database constraints unnecessary.

    InsteadOther code, scripts and people change data too. Constraints guarantee the rules; application validation gives friendly messages.

  • MistakeKeep the transaction open while the confirmation email is sent.

    InsteadCommit first, then send the email. Long transactions hold locks and block other users.

  • MistakeON DELETE CASCADE everywhere keeps things tidy.

    InsteadCascades can silently erase history. Choose each action deliberately; RESTRICT is the safe default.

Try it yourself

Book only if there is room

The setup builds the Food Hub sample database in conn. Shift 6 (kitchen) has 2 spots and already has 2 booked signups; shift 7 (youth garden) has 5 spots and 1 booked signup.

Complete book(volunteer_id, shift_id) so it inserts a signup only when the number of 'booked' signups on the shift is less than its spots, in one INSERT ... SELECT statement inside a with conn: block, and returns 'Booked' or 'Full' using the row count. The program then tries to book Hamza (8) on shift 6 and on shift 7.

def book(volunteer_id, shift_id):
    return 'Full'

print('Shift 6:', book(8, 6))
print('Shift 7:', book(8, 7))

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. Name the four ACID properties and give a one-line Food Hub example of each.

    Show answer

    Atomicity: a shift swap is never half done. Consistency: no signup refers to a missing shift. Isolation: nobody sees a swap half way through. Durability: a confirmed booking survives a server crash.

  2. Why can't "no more signups than spots" be a CHECK constraint?

    Show answer

    A CHECK constraint can only test values in the row being changed. Counting signups needs other rows in another table, so the rule is enforced with a locked or conditional write inside a transaction.

  3. Which isolation level is the default in PostgreSQL, and does it prevent the lost update in the booking example?

    Show answer

    Read committed. It does not prevent it: two transactions can both read the same count before either commits.

  4. A booking transaction fails with a deadlock error. What should the application do?

    Show answer

    Roll back and retry the whole transaction, a limited number of times, then show an error if it still fails. Keeping transactions short and locking rows in a consistent order makes deadlocks rarer.

Summary and key terms

  • A transaction groups statements so they commit together or roll back together; autocommit makes each statement its own transaction.
  • ACID means atomicity, consistency, isolation and durability, the last provided by a log written before each commit.
  • From code, begin, work and commit, rolling back on any error, all on one connection, and keep transactions short; savepoints allow partial rollback.
  • Constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK) are enforced on every change and complement application validation.
  • Foreign key actions RESTRICT, CASCADE and SET NULL decide what happens on delete.
  • Concurrent transactions can cause dirty reads, non-repeatable reads, phantoms and lost updates; isolation levels prevent progressively more of them.
  • Read committed, the PostgreSQL default, does not stop check-then-insert overbooking, so lock the row with FOR UPDATE, use a conditional update, or use serializable isolation with retries.
  • Deadlocks and serialization failures are handled by retrying.
Commit
Making all of a transaction's changes permanent.
Rollback
Undoing all of a transaction's changes.
Autocommit
A mode in which each statement is committed as soon as it succeeds.
ACID
Atomicity, consistency, isolation and durability, the guarantees of a transaction.
Savepoint
A named point inside a transaction that can be rolled back to.
CHECK constraint
A rule that a condition on each row must be true.
ON DELETE CASCADE
A foreign key action that deletes child rows when the parent row is deleted.
Isolation level
A setting that controls which concurrency anomalies a transaction is protected from.
Lost update
An anomaly in which one transaction overwrites or ignores another's concurrent change.
Row lock
A lock on a row, such as one taken by SELECT ... FOR UPDATE, that makes other writers wait.
Deadlock
Two transactions each waiting for a lock the other holds; the database cancels one.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz