Transactions

What you will be able to do

Make a change that touches two rows happen completely or not at all. Then stop two requests that arrive at the same moment from erasing each other’s change, without an error that anybody sees.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• The rules of migration 005 and the deliberate copy message_count of migration 006, from Integrity constraints.

What you need on your computer

• The project after the last lesson, with migrations 005 and 006 applied. python -m pytest prints 49 passed.
• PostgreSQL running, with the small data loaded. If you are not sure, run python -m scripts.load --reset.

This lesson adds scripts/lost_update.py and tests/test_transactions.py. Then your project matches the snapshot end-of-m02.

Half a bank transfer

You move 50 dollars from your savings account to your current account. For the bank, that is two changes: take 50 from savings, and add 50 to current. If the bank’s computer stops between them, 50 dollars are gone. Nobody would accept “most of a transfer”. The bank makes both changes, or neither.

The help desk has the same kind of change. When a customer writes to ticket T-30002, two rows change: a new row in messages, and message_count + 1 on the ticket. Since the last lesson, the count is a copy, and the copy must stay right.

The problem: the program stops in the middle

Try it in psql. Insert a message, and then do not run the UPDATE. That stands for a program that crashed, or lost its connection, after the first statement:

INSERT INTO messages (ticket_id, author, body)
VALUES ('T-30002', 'agent', 'We have refunded the second charge.');
SELECT message_count,
       (SELECT count(*) FROM messages m WHERE m.ticket_id = t.ticket_id) AS messages
FROM tickets t WHERE ticket_id = 'T-30002';

INSERT 0 1
 message_count | messages
---------------+----------
             1 |        2
(1 row)

The ticket says 1 message, and the table has 2. The ticket list will show the wrong number to every agent, and no error tells anybody. This crash is constructed: you stopped yourself. But programs do stop between two statements, for example when a deploy restarts them.

This query finds every ticket whose copy is wrong. It returns only T-30002:

SELECT ticket_id, message_count FROM tickets t
WHERE message_count <> (SELECT count(*) FROM messages m WHERE m.ticket_id = t.ticket_id);

Repair the copy now, so the next examples start from a correct count:

UPDATE tickets t
SET message_count = (SELECT count(*) FROM messages m WHERE m.ticket_id = t.ticket_id)
WHERE ticket_id = 'T-30002';

The idea: a transaction

A transaction is a group of statements that the database treats as one change. You start it with BEGIN. COMMIT saves all of it. ROLLBACK undoes all of it, and an error inside a transaction leads to a rollback too. Until the commit, other sessions do not see any of it.

Here is the same change in a transaction, with a mistake in the third statement. A program sends an automatic reply with the author robot, which the rule messages_author_known refuses:

BEGIN;
INSERT INTO messages (ticket_id, author, body) VALUES ('T-30002', 'customer', 'Thank you!');
UPDATE tickets SET message_count = message_count + 1, last_message_at = now()
WHERE ticket_id = 'T-30002';
INSERT INTO messages (ticket_id, author, body) VALUES ('T-30002', 'robot', 'Auto-reply: we got your message.');
SELECT count(*) FROM messages WHERE ticket_id = 'T-30002';
COMMIT;

BEGIN
INSERT 0 1
UPDATE 1
ERROR:  new row for relation "messages" violates check constraint "messages_author_known"
DETAIL:  Failing row contains (334, T-30002, robot, Auto-reply: we got your message., 2026-10-10 04:46:03.681432+00).
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

After the error, PostgreSQL ignores every statement until the transaction ends, and the COMMIT turns into a ROLLBACK. (Your message number and time differ.) Check the ticket: message_count is still equal to the number of messages. The first INSERT and the UPDATE worked, and both are gone. Run the transaction again without the robot’s line, and it ends with COMMIT: now both numbers go up by one together.

The transaction boundary of 'add a message': the insert and the count change are saved together, or not at all.

Text description of the diagram

A request to add a message to T-30002 enters a transaction. Inside the boundary are BEGIN, the INSERT into messages, and the UPDATE of message_count on tickets. If both statements work, COMMIT saves the new message and the new count together. If a step fails, ROLLBACK leaves nothing: no message and the same count. Other sessions see none of the changes until the COMMIT.

Database people sum up what a transaction gives with four letters, ACID:

• Atomic: all of it or nothing, as you just saw. “Atom” is Greek for “cannot be cut”.
• Consistent: every rule of the last lesson holds when the transaction commits.
• Isolated: sessions that run at the same time do not see each other’s unfinished changes.
• Durable: after COMMIT, the change survives a crash. PostgreSQL writes it to a log on disk before it says COMMIT.

Isolation is easy to see with two terminals. In each one, open psql. In the first (A), run BEGIN; and UPDATE tickets SET message_count = 5 WHERE ticket_id = 'T-30002';. In the second (B), read the count with SELECT message_count FROM tickets WHERE ticket_id = 'T-30002';. B sees the old value, not 5. Run ROLLBACK; in A, and the 5 never existed for anybody.

When you finish, put the data back as it was:

python -m scripts.load --reset

Amira asks: “How do I do this from Python?” With psycopg, a with conn.transaction(): block is one transaction. If the block ends normally, psycopg commits. If an exception leaves the block, psycopg rolls back. This test from tests/test_transactions.py checks exactly the robot case:

tests/test_transactions.py (part)

def test_a_failed_transaction_changes_nothing(db_url):
    with psycopg.connect(db_url) as conn:
        count = "SELECT message_count FROM tickets WHERE ticket_id = 'T-30002'"
        before = conn.execute(count).fetchone()
        with pytest.raises(psycopg.errors.CheckViolation):
            with conn.transaction():
                conn.execute(
                    "INSERT INTO messages (ticket_id, author, body)"
                    " VALUES ('T-30002', 'agent', 'First part')"
                )
                conn.execute(
                    "UPDATE tickets SET message_count = message_count + 1"
                    " WHERE ticket_id = 'T-30002'"
                )
                conn.execute(
                    "INSERT INTO messages (ticket_id, author, body)"
                    " VALUES ('T-30002', 'robot', 'x')"
                )
        after = conn.execute(count).fetchone()
        n = conn.execute("SELECT count(*) FROM messages WHERE body = 'First part'").fetchone()[0]
    assert after == before
    assert n == 0

In Module 3, the API’s “add a message” endpoint uses one such block for the insert and the count.

The second problem: two waiters and one cake

A café has a whiteboard: “Chocolate cake: 3 slices left”. Two waiters take an order at the same moment. Each one reads “3”, sells a slice, and writes “2” on the board. The board now says 2, but only 1 slice is left. Nobody made a mistake on their own, and nobody saw an error. One of the two changes is gone.

This is a lost update: two sessions read the same value, each computes a new value from it, and the second write replaces the first. Transactions alone do not stop it. Each waiter’s change was “all or nothing”; the problem is the moment of the read.

The project has a script that makes it happen on T-30002. It opens two real database connections, A and B. Each one adds one to message_count the way a careless program would: read the count, add one in Python, write it back. The script runs their steps in a fixed order, so you get the same result every time. In real life, the order depends on timing, and it goes wrong only sometimes, which makes it hard to find. The script changes only the count, and it puts the count back at the end.

Explore

Two sessions add a message to T-30002 at the same time

Step

8 of 8

Saved message_count

2

It should end at

3

Updates lost

1

Two messages were added, but the count is 2: 1 update lost. Nothing failed, and nobody saw an error.

• a change that is not saved yet
• waits for a lock, or a lost change
• COMMIT: the change is saved

Read, then write: ticket T-30002, step 8 of 8

#	Session A	Session B	Saved
1	Start: message_count = 1 is saved		1
2	SELECT: message_count = 1		1
3		SELECT: message_count = 1	1
4	UPDATE … SET message_count = 2 (what it read + 1)		1
5	COMMIT		2
6		UPDATE … SET message_count = 2 (what it read + 1)	2
7		COMMIT	2
8	End: message_count = 2. It should be 3.		2

Recorded from python -m scripts.lost_update --json on the small data: two real database connections, run in a fixed order by the script (a constructed case; in real life, the timing is luck).

Two real database connections, A and B, each add one to message_count, which starts at 1. The steps and values are the output of scripts/lost_update.py on the small data; the script fixes the order of the steps.

Text description of the explorer

The explorer has one main control with three choices: read, then write; SELECT … FOR UPDATE; and one UPDATE statement. The Next step, Back and All steps buttons move through the steps. A table shows what session A and session B do at each step, and the message_count that is saved after it.

Read, then write (8 steps): A reads 1, B reads 1, A writes 2 and commits, B writes 2 and commits. The saved count goes 1, 2, 2. It should be 3: one update is lost, and there is no error.

SELECT … FOR UPDATE (9 steps): A reads 1 and locks the row. B asks for the same row and waits. A writes 2 and commits, which frees the lock. B’s read returns 2. B writes 3 and commits. The count is 3; nothing is lost.

One UPDATE statement (7 steps): A runs UPDATE … SET message_count = message_count + 1, which makes 2 and locks the row. B’s UPDATE waits. A commits. B’s UPDATE runs on the saved 2 and makes 3. B commits. The count is 3; nothing is lost.

The explorer’s first mode is the café. B read 1 before A’s commit, so B’s “1 + 1” is 2, and it writes 2 over A’s 2.

The idea: lock the row, or let the database add

There are two ways out. Both use a row lock: a mark that one transaction puts on a row, so that other transactions that want to change it wait. PostgreSQL locks every row that a transaction changes, until COMMIT or ROLLBACK.

Lock before you read: SELECT … FOR UPDATE. This reads the row and locks it at once. A second session that asks for the same row with FOR UPDATE waits until the first one ends. Then its read returns the new value. It is the waiter who takes the marker and holds it, so that the other waiter must wait for the board.

Let the database add: one UPDATE statement. SET message_count = message_count + 1 reads and writes in one step, while it holds the row lock. The program never holds an old number. B’s UPDATE waits for A’s commit, and then adds one to the saved value.

Which one to choose? If the new value can be computed in SQL, use one statement: it is shorter, and there is no read to forget. If the program must look at the row before it decides, use FOR UPDATE. The API’s “add a message” endpoint in Module 3 does both. It locks the ticket with SELECT status … FOR UPDATE, which also tells it whether the ticket exists. Then it inserts the message and runs one UPDATE with message_count + 1. Until it commits, no other message can change that ticket.

Isolation levels, in plain words. PostgreSQL’s default is read committed: each statement sees what was committed when it started. That is why session B read 1. Stricter levels exist. With BEGIN ISOLATION LEVEL REPEATABLE READ, the same café order does not lose the update, but B’s write fails:

ERROR:  could not serialize access due to concurrent update

This is a real run with two psql sessions. A committed its change first, so B’s UPDATE refused to overwrite a value that B had not seen. Nothing is lost, but the program must catch the error and run its transaction again. The strictest level, SERIALIZABLE, works the same way. This course uses the default level with row locks, because a wait is easier to handle than a retry.

When to use transactions and locks, and what they cost

Use a transaction for every change that touches more than one row: a message and its count, a refund and its record. Use a row lock or a single UPDATE whenever two requests can change the same row at the same time.

They cost something:

• Waiting. While A holds a lock, B waits. A transaction that holds a lock for long makes every request for that row wait. Keep transactions short, and never wait for a person or a slow service inside one. Connection management shows what a long transaction does to the API.
• Retries with the stricter isolation levels: the program must catch the error and run again.
• Care with several rows. If two transactions lock the same two rows in a different order, each can wait for the other. PostgreSQL detects this deadlock and stops one of them with an error. Lock rows in a fixed order, for example by ticket ID. (Described here, not run.)

Common mistake

'Read the count, add one, write it back'

Symptom: after two messages were added at the same moment, message_count of T-30002 is 2, not 3. No request failed, and the log has no error. python -m scripts.lost_update shows it: Two messages were added: the count should be 3. It is 2: 1 update lost.

Cause: both sessions read 1 before either one committed. Each computed 2 in the program, and the second write replaced the first. A transaction around each session does not help, because the problem is the moment of the read.

Diagnose: look for code that reads a value, changes it in the program and writes it back, with no lock. Compare the copy with the source: the query of this lesson finds every ticket whose message_count differs from its messages.

Fix: let the database compute the value in one UPDATE (message_count = message_count + 1), or read with SELECT … FOR UPDATE in the same transaction as the write. tests/test_transactions.py keeps all three versions as tests.

Guided practice: finish the module

1. Copy the script and the last test file from the snapshot:

   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/scripts/lost_update.py scripts/
   cp ../nextia-learning-labs/C20/snapshots/end-of-m02/tests/test_transactions.py tests/

   On Windows, in PowerShell: Copy-Item ..\nextia-learning-labs\C20\snapshots\end-of-m02\scripts\lost_update.py scripts\, then the same for tests\test_transactions.py into tests\.

2. Run the lost update:

   python -m scripts.lost_update

    -  start  message_count = 1
    A  read  message_count = 1
    B  read  message_count = 1
    A  write  message_count = 2
    A  COMMIT
    B  write  message_count = 2
    B  COMMIT
    -  end  message_count = 2
   Two messages were added: the count should be 3. It is 2: 1 update lost. (0.30 s)

   The seconds at the end differ on your computer. If the count does not start at 1, run python -m scripts.load --reset first.

3. Run it with a row lock:

   python -m scripts.lost_update --lock

    -  start  message_count = 1
    A  read (lock the row)  message_count = 1
    B  read: waits, because A has locked the row
    A  write  message_count = 2
    A  COMMIT
    B  read (after A's commit)  message_count = 2
    B  write  message_count = 3
    B  COMMIT
    -  end  message_count = 3
   Two messages were added: the count should be 3. It is 3. (0.53 s)

   This run is longer, because the script waits half a second to see whether B is blocked.

4. Run it with one UPDATE statement:

   python -m scripts.lost_update --atomic

    -  start  message_count = 1
    A  UPDATE ... SET message_count = message_count + 1 (locks the row)
    B  UPDATE ... SET message_count = message_count + 1: waits, because A has locked the row
    A  COMMIT
    B  the UPDATE runs now, on A's committed value
    B  COMMIT
    -  end  message_count = 3
   Two messages were added: the count should be 3. It is 3. (0.55 s)

5. Run all the tests:

   python -m pytest

   tests/test_classify.py ..................                                [ 33%]
   tests/test_contract.py ..                                                [ 37%]
   tests/test_database.py ...                                               [ 43%]
   tests/test_history.py ........                                           [ 58%]
   tests/test_load.py ...                                                   [ 64%]
   tests/test_schema.py ...............                                     [ 92%]
   tests/test_transactions.py ....                                          [100%]
   
   ============================== 53 passed in 2.65s ==============================

   Only the end of the output is shown, and your time differs. Your project now matches the snapshot end-of-m02.

Module practice: a many-to-many table with its rules

Build the ticket_citations table that you designed in Entities and relationships, and prove its rules in psql:

1. Create the table, with a primary key on the three columns and the two foreign keys.
2. Insert two good citations for T-30002: refunds-and-payments version 3, and refund-approval-procedure version 2.
3. Insert three bad rows, and predict each error first: the same citation again, a citation for T-99999, and returns-policy version 5.
4. Remove the table with DROP TABLE ticket_citations;, so your project stays equal to the snapshot.

Show an example answer

CREATE TABLE ticket_citations (
    ticket_id   text    NOT NULL REFERENCES tickets (ticket_id) ON DELETE CASCADE,
    doc_id      text    NOT NULL,
    doc_version integer NOT NULL,
    PRIMARY KEY (ticket_id, doc_id, doc_version),
    FOREIGN KEY (doc_id, doc_version) REFERENCES documents (doc_id, version)
);
INSERT INTO ticket_citations VALUES ('T-30002', 'refunds-and-payments', 3);
INSERT INTO ticket_citations VALUES ('T-30002', 'refund-approval-procedure', 2);
INSERT INTO ticket_citations VALUES ('T-30002', 'refunds-and-payments', 3);
INSERT INTO ticket_citations VALUES ('T-99999', 'refunds-and-payments', 3);
INSERT INTO ticket_citations VALUES ('T-30002', 'returns-policy', 5);
SELECT * FROM ticket_citations;
DROP TABLE ticket_citations;

CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "ticket_citations_pkey"
DETAIL:  Key (ticket_id, doc_id, doc_version)=(T-30002, refunds-and-payments, 3) already exists.
ERROR:  insert or update on table "ticket_citations" violates foreign key constraint "ticket_citations_ticket_id_fkey"
DETAIL:  Key (ticket_id)=(T-99999) is not present in table "tickets".
ERROR:  insert or update on table "ticket_citations" violates foreign key constraint "ticket_citations_doc_id_doc_version_fkey"
DETAIL:  Key (doc_id, doc_version)=(returns-policy, 5) is not present in table "documents".
 ticket_id |          doc_id           | doc_version
-----------+---------------------------+-------------
 T-30002   | refunds-and-payments      |           3
 T-30002   | refund-approval-procedure |           2
(2 rows)

DROP TABLE

The rules that you did not name got names from PostgreSQL, such as ticket_citations_ticket_id_fkey. Version 5 of the returns policy does not exist (the newest is 4), so the two-column foreign key refuses it. A foreign key on doc_id alone could not catch that.

Stretch: PostgreSQL can also undo a CREATE TABLE. Put the whole practice between BEGIN; and ROLLBACK;. After it, \d ticket_citations says Did not find any relation named "ticket_citations". Inside the transaction, the first error stops everything after it, so test one bad row per transaction.

Module check

You have finished the module when you can show these on your own project:

• python -m pytest prints 53 passed. tests/test_schema.py proves that the database itself rejects duplicates, orphans and impossible values, with no help from the API.
• You can draw the help desk’s ERD, and explain the decision at each end of a line. One example: the AI runs keep their cost.
• You can explain why migration 005 keeps three messages in orphaned_messages, and what the first draft’s error said.
• You can explain, with the output of python -m scripts.lost_update, why one update was lost, and why --lock and --atomic lose none.

Reset. If your project is broken, rename it and copy the snapshot end-of-m02, as the snapshots README shows. In ~/projects:

mv ticket-api ticket-api-old
cp -R nextia-learning-labs/C20/snapshots/end-of-m02 ticket-api
cp ticket-api-old/.env ticket-api/

On Windows, in PowerShell: Rename-Item ticket-api ticket-api-old, then Copy-Item -Recurse nextia-learning-labs\C20\snapshots\end-of-m02 ticket-api, then Copy-Item ticket-api-old\.env ticket-api\. Make the virtual environment again, then run python -m scripts.load --reset and python -m pytest: 53 passed. These steps were tested on macOS. The Windows commands are not tested.

Knowledge check

Check your understanding

A transaction inserts a message, adds one to message_count, and then fails on a rule. What is in the database afterwards, and what did psql print for the COMMIT?

Show the answer

Nothing of the transaction: no new message, and the same message_count. After the error, PostgreSQL ignored every statement (current transaction is aborted), and the COMMIT was turned into a rollback, so psql printed ROLLBACK. That is atomicity: all of the change or none of it.

Check your understanding

Three sessions add a message to a ticket whose count is 4. Each reads the count, adds one in Python and writes it back, and all three read before any of them commits. What can the count be at the end? What should it be?

Show the answer

It should be 7. But each session read 4 and writes 5, so the count ends at 5: two updates lost, and no error. With other timings, the result can be 5, 6 or 7, which is why the bug shows up only sometimes. With message_count = message_count + 1 in one statement, or with SELECT … FOR UPDATE before the read, it is always 7.

Check your understanding

Imagine a refunds table (it is not in the project). An agent may add a goodwill refund to a ticket only if the ticket's refunds stay at 50 dollars or less in total. The program reads the total, checks the limit in Python, then inserts the refund. Two agents add 20 dollars to a ticket with 30 dollars of refunds at the same moment. What can happen, and which protection fits?

Show the answer

Both read 30, both see that 30 + 20 is within the limit, and both insert: the total becomes 70, with no error. It is the same mistake as the lost update: a decision on a value that changed after the read. One UPDATE does not fit, because the program inserts a row in another table after a check. Lock the ticket first, in the same transaction: SELECT … FROM tickets WHERE ticket_id = … FOR UPDATE. The second agent waits, then reads 50, and the program refuses the refund.

Recap

• A transaction makes several statements one change: COMMIT saves all of it, and ROLLBACK, or an error, undoes all of it. The robot’s reply left no half change behind.
• ACID: atomic, consistent (the rules hold), isolated (nobody sees unfinished changes) and durable (a committed change survives a crash).
• A lost update happens when two sessions read the same value and the second write replaces the first: 2 instead of 3, with no error.
• A row lock stops it. Use one UPDATE … SET n = n + 1 when SQL can compute the value, and SELECT … FOR UPDATE when the program must read first. The default isolation level, read committed, needs one of them; stricter levels refuse the write, and the program must retry.
• Your project matches end-of-m02: 53 passed. Module 3 calls the database from the API: parameters, a connection pool and safer migrations.

Next: Safe queries