With BEGIN, COMMIT and ROLLBACK you can already work, but TCL has more pieces, and some of them solve problems you couldn't name until now. What do you do if you're loading a hundred orders at once and number forty-seven fails: throw away the forty-six good ones? How do you get out of 09-01's aborted state without losing the work done? And why can you ROLLBACK a CREATE TABLE in PostgreSQL and not in MySQL? This lesson is the full repertoire: all of BEGIN's options, the SAVEPOINTs and their three operations, how to set the session's default level, what psycopg's, JDBC's or SQLAlchemy's autocommit really does —because in your application the BEGIN is probably placed by the framework without you having written it—, transactional DDL, prepared transactions and the usage patterns from code. And it all flows into the module's integrating example: confirming a GreenStore order from start to finish, with its savepoint and its COMMIT.

1. The TCL repertoire
2. BEGIN and its options
3. COMMIT and ROLLBACK
4. SAVEPOINT: undoing only part of it
5. A savepoint to escape the aborted state
6. What savepoints cost
7. SET TRANSACTION and the session's default level
8. Implicit against explicit: the drivers' autocommit
9. DDL inside transactions
10. Prepared transactions and two-phase commit
11. Usage patterns from an application
12. The integrating example: confirming an order
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

You'll use all of them except the last, which exists for a very specific case (section 10).

The options can be combined in any order, separated by commas or by spaces:

The typical case of the last one is GreenStore's monthly report run against the production database:

The canonical €118.25 of shipping, broken down by status, read from a perfectly coherent snapshot of the system and with no possibility of the report aborting after ten minutes or of blocking anybody while it runs. Three extra words in the BEGIN.

Little to add to 09-01, other than the synonyms —COMMIT = END = COMMIT WORK; ROLLBACK = ABORT = ROLLBACK WORK— and one behavioural detail: COMMIT or ROLLBACK outside a transaction aren't an error, just a WARNING: there is no transaction in progress.

It looks harmless, and it's exactly the opposite: it's the classic symptom of a script whose BEGIN didn't run —because it was inside an if that wasn't entered, or because a tool had already committed on its own. Everything you thought was transactional ran under autocommit. If you see that WARNING in a production log, investigate it.

A SAVEPOINT is a mark inside the transaction you can go back to without closing it. SAVEPOINT s1; creates the mark (if one with that name already existed, the new one hides it); ROLLBACK TO SAVEPOINT s1; undoes everything done after s1 and leaves the transaction open, with s1 still available; and RELEASE SAVEPOINT s1; removes the mark without undoing anything, merging what was done after s1 into the outer transaction.

GreenStore imports the marketplace's orders every night. Today three come in, and the second brings an item that doesn't exist in the catalogue. Objective: load the two good ones and discard only the bad one, all in a single transaction.

The first one's RELEASE confirms a decision: "this order is fine, I no longer need to be able to discard it separately". It doesn't make it permanent —only the final COMMIT does that— but it merges it into the outer transaction. The second, on the other hand, blows up:

The transaction is aborted (greenstore=!>). Without savepoints, the night would have ended here and the next 46 orders would have been lost. With a savepoint, it's rescued — and the prompt goes back to greenstore=*>: the transaction is alive again, order 22 has disappeared and 21 is still there.

The state after each step, which is what has to be looked at:

Notice the gap: there's no order 22, and no line 49. It's exactly what exercise 2 of 09-01 anticipated: sequences aren't undone, not by a ROLLBACK and not by a ROLLBACK TO SAVEPOINT. Value 22 was consumed by the discarded header, and 49 was consumed by the INSERT of the line that violated the foreign key — because PostgreSQL builds the complete row, evaluating the id's nextval, before checking the constraint. Gaps in identifiers are normal and don't mean data is missing.

You've just seen it in passing, but it deserves saying out loud, because it solves 09-01's most frustrating message:

ROLLBACK TO SAVEPOINT is the only way of recovering from the aborted state without losing the transaction. It undoes back to the mark, clears the error state and returns the transaction to active. On an aborted transaction, ROLLBACK and COMMIT undo it entirely and close it; ROLLBACK TO SAVEPOINT s revives it.

And out of this comes a pattern you'll see a lot in generated code: wrapping every risky operation in its own savepoint, so that a foreseeable failure —a duplicate key, a missing FK— doesn't throw away the whole job. It's literally what an ORM's nested try/except blocks do: every atomic() block in Django or begin_nested() in SQLAlchemy inside another isn't a new transaction, it's a SAVEPOINT.

They aren't free. Each one opens a subtransaction, and PostgreSQL only caches 64 per transaction: go beyond that and resolving each row's visibility forces a trip to disk (pg_subtrans) and the whole server's performance can drop sharply. On top of that, every failed attempt leaves its dead rows for VACUUM (09-02) and burns sequence values, as you've just seen with 22 and 49.

The practical rule: one savepoint per business unit you want to be able to discard —an order within a batch— and not one per statement. If you need thousands, what you actually need is to split the load into several transactions and keep a log of what's been processed (05-03's idempotency).

Two similar statements with very different scopes:

Form (a) is equivalent to putting the options in the BEGIN, with the same limitation: once the transaction has read or written anything, the level can no longer be changed. Form (b) affects the whole session and survives the COMMITs, so it's the way to configure a connection dedicated to reports. To know where you are, SHOW transaction_isolation; (the current transaction's) and SHOW default_transaction_isolation; (the following ones'), which on a fresh installation return read committed — PostgreSQL's default value and 09-04's starting point. The same settings are fixed per user or per database with ALTER ROLE ... SET and ALTER DATABASE ... SET, or for the whole server in postgresql.conf.

This is what causes the most problems in real work, and the reason is simple: in your application, the BEGIN is almost never written by you.

Three consequences to burn into your memory. With psycopg or SQLAlchemy, your process can spend hours with a transaction open without anybody asking for it: a script that opens a connection, reads a table and then spends twenty minutes processing in Python is idle in transaction for twenty minutes, with everything that implies (09-01, 09-02); the solution is autocommit = True for the reads, or closing the transaction before computing. With JDBC the opposite happens: every executeUpdate() commits itself, and a four-step operation isn't atomic unless somebody calls setAutoCommit(false). And nested blocks aren't nested transactions: they're savepoints — SQL has no real nested transactions, and that's why an inner atomic() can fail and be undone without bringing the outer one down.

Operational tip: before debugging a lock or a piece of data that "doesn't get saved", find out where your framework puts the BEGIN and where the COMMIT. It takes a minute to check by turning on log_statement = 'all' and reading the log.

Here PostgreSQL wins hands down, and it's the promise 05-06 left open.

In PostgreSQL, DDL is transactional. CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ADD CONSTRAINT… it can all be undone:

The table never existed. That's the property that makes schema migrations safe in PostgreSQL: a fifteen-step migration that fails at step twelve is undone entirely and leaves the database exactly as it was. It's the foundation of 05-06's versioned migration tools.

The exceptions —what can't be run inside a transaction— are few and all for the same reason, that they need to act outside transactional control: CREATE DATABASE · DROP DATABASE · CREATE TABLESPACE · ALTER SYSTEM · VACUUM · CREATE INDEX CONCURRENTLY · REINDEX CONCURRENTLY · CLUSTER over several tables.

Dialect note — the module's most expensive divergence. MySQL has no transactional DDL. Any schema statement causes an implicit commit: it silently commits everything pending and can't be undone. The list is worth knowing: CREATE/ALTER/DROP TABLE · TRUNCATE TABLE · RENAME TABLE · CREATE/DROP INDEX · CREATE/ALTER/DROP DATABASE · CREATE/ALTER/DROP EVENT, FUNCTION, PROCEDURE, TRIGGER, VIEW · CREATE/ALTER/DROP USER · GRANT / REVOKE · LOCK TABLES / UNLOCK TABLES · START TRANSACTION · SET autocommit = 1.

Practical consequences: a migration that fails halfway leaves the schema half-done and you have to write the undo by hand; and an ALTER TABLE slipped inside a transactional block commits without warning everything that block had written. MySQL 8 introduced atomic DDL, which guarantees a schema statement isn't left half-done after a crash — but that's not the same as being able to undo it with a ROLLBACK. Oracle behaves the same; SQL Server and SQLite do have transactional DDL, like PostgreSQL.

When an operation has to be atomic across two different databases, a normal COMMIT won't do: each server would commit on its own and one of them could fail. The classic answer is the two-phase commit:

The prepared transaction survives disconnection and a server restart: it sits on disk waiting to be resolved, and you can query it in pg_prepared_xacts.

⚠️ You should almost never use it by hand, and in PostgreSQL it ships disabled (max_prepared_transactions = 0). The reason is that a prepared transaction that's never resolved is the worst thing that can happen to a database: it holds its locks forever, it prevents VACUUM from cleaning anything and it ignores every timeout, because it isn't attached to any session you could kill. It only makes sense with a distributed transaction manager guaranteeing resolution (JTA, XA, postgres_fdw). If you think you need it, the right answer is almost always a different architecture: an outbox pattern, a message queue or a saga with compensations.

The canonical block, in pseudocode valid for any language:

Four rules that go with it:

1. The rollback() isn't optional. Without it, the connection goes back to the pool in the aborted state and the next request that gets it will fail with 25P02 without having done anything wrong. It's one of the most baffling errors to debug.
2. No waiting and no external services inside. Not the payment gateway, not the confirmation email, not a file upload. 09-02 already justified it: ACID ends at the database's edge. The correct pattern is to charge outside and record the result inside, or to write the intent into an outbox table another process consumes.
3. A retry has to be idempotent. If the transaction aborts through a serialization conflict (09-04) or a deadlock (09-05), it's retried in full — and that's only safe with 05-03's property: absolute values and bounded WHEREs, or a unique business key (the basket's identifier) that makes it impossible to insert the same order twice.
4. Prepare the data outside and open the transaction as late as possible. Validating the basket and computing amounts doesn't need an open transaction. What goes inside is only the writing.

A note on scope: in a real system this logic usually lives in a stored procedure invoked with a single call, so the transaction doesn't depend on network latency. Procedures are 10-04 and the triggers that go with them, 10-05. Here we carry on with plain SQL.

All together. Pau Llorens (customer 6) buys through the web two olive oils and one matcha tea, pays by card and is charged €4.95 of shipping.

The AND stock >= N is 09-02's pattern: if there were no stock, the UPDATE would return UPDATE 0 without aborting the transaction, and the application could decide what to do —ROLLBACK TO SAVEPOINT before_stock and leave the order as pending as a reservation, or a full ROLLBACK and notify the customer— instead of crashing into the CHECK.

And the final check, which is the customer's invoice:

Four operations over three tables, a single unit of work. Section 3 of 09-01's disaster —an uncharged order, orphan lines, evaporated inventory— is now impossible: either there's a paid order with its stock reduced, or there's absolutely nothing.

The only thing this order still doesn't solve is what happens if another customer buys the last matcha at the very same instant. Step 3's two UPDATEs could read the same stock and each reduce it on its own. That's the lost update, and it's the subject of the next two lessons.

• Changing the isolation level halfway through a transaction. SET TRANSACTION ISOLATION LEVEL has to go before the first query; afterwards, PostgreSQL rejects it.
• Confusing RELEASE SAVEPOINT with ROLLBACK TO SAVEPOINT. RELEASE undoes nothing: it just discards the mark. Anyone wanting to cancel the work needs ROLLBACK TO.
• Putting one savepoint per statement. Beyond 64 subtransactions, visibility resolution goes to disk and the whole server's performance can collapse.
• Ignoring the WARNING: there is no transaction in progress. It means your BEGIN didn't run and everything ran under autocommit.
• Forgetting the rollback() in the except. The connection goes back to the pool aborted and blows up another user's request with an incomprehensible 25P02. And don't assume the ORM doesn't open transactions: psycopg and SQLAlchemy open one on the first query; JDBC opens none.
• Writing a migration for MySQL as if DDL could be undone. It can't: every ALTER TABLE commits implicitly. Always write the down by hand.
• Putting the card charge inside the transaction. The ROLLBACK will undo your order, not the charge. And PREPARE TRANSACTION with no manager guaranteeing it gets resolved leaves locks and prevents VACUUM indefinitely.
• Tip: one savepoint per discardable business unit, not per statement; and BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE for long reports against production.
• Tip: turn on log_statement = 'all' for a while in development and read where your framework puts the BEGINs and the COMMITs. It's revealing.

Work on the freshly reloaded database.

Reproduce section 4's batch load with four orders instead of three, in a single transaction and with one savepoint per order: (1) customer 6, 2 × product 1 at €12.50 — correct; (2) customer 11, 1 × product 99 at €15.00 — fails on a non-existent FK; (3) customer 12, 3 × product 14 at €3.25 — correct; (4) customer 13, 1 × product 13 at €13.75 and reduce its stock — fails on CHECK (stock >= 0).

1. Write the complete block, discarding only batch orders 2 and 4.
2. Say which id each header gets and why there are gaps.
3. How many rows do orders and order_lines have after the COMMIT?
4. Rewrite order 4 so it doesn't fail, using 09-02's pattern, and explain what changes in the application's flow.

A colleague shows you this Python code with psycopg and tells you that "sometimes it saves the same order twice and sometimes the connection goes daft":

1. Find four distinct problems.
2. Where is this transaction's BEGIN, given that nobody wrote it?
3. What does another session querying orders see while the gateway is responding?
4. Rewrite it in pseudocode fixing everything, and say what would be needed for the retry to be idempotent.

On transactional DDL: (1) inside BEGIN … ROLLBACK, run ALTER TABLE products ADD COLUMN weight_kg NUMERIC(6,3); and an UPDATE that fills it in; then check that the column doesn't exist. (2) try CREATE INDEX CONCURRENTLY idx_test ON orders (status); inside a BEGIN and explain why it's an exception. (3) say what changes in the deployment strategy if the same migration is for MySQL, has five steps and fails at the third.

Solution 1

1. The structure is exactly section 4's, with four SAVEPOINT batch_N blocks … (RELEASE if all went well, ROLLBACK TO if it failed) and a COMMIT at the end. Blocks 2 and 4 end in ROLLBACK TO SAVEPOINT: number 2 after product 99's foreign-key error, and number 4 after the CHECK that prevents leaving product 13's stock at −1.

2. The headers get 21, 22, 23 and 24 in order, but only 21 and 23 survive. The gaps (22 and 24) are sequence values consumed by rows that were then discarded, and sequences aren't undone. The same happens with order_lines: 48 and 50 survive, and 49 and 51 are lost.

3. orders has 22 rows (20 + 2) and order_lines has 49 (47 + 2).

4. The version that doesn't fail uses the conditional UPDATE:

It changes the whole flow: instead of an error that aborts the transaction and forces the ROLLBACK TO, you get an UPDATE 0 the application interprets as "out of stock". It can then decide on the fly: discard only that line, leave the order as pending as a reservation, or propose an alternative product. A foreseeable business case should never reach the engine as an exception.

Solution 2

1. The four problems:

2. The BEGIN is put in by psycopg, automatically, on running the first statement over the connection — that is, at the INSERT. That's why the transaction is already open when the gateway is called, even though the word doesn't appear in the code. 3. Another session sees 20 orders: the new header isn't committed and as far as it's concerned doesn't exist (09-01). During the gateway's eight seconds, that order is in a limbo visible only to its own session.

4. The corrected version:

For the retry to be idempotent, two things are needed: an idempotency key at the gateway (the basket's identifier), so that a second charge with the same key doesn't bill twice; and a UNIQUE constraint on that same key in orders, so the second INSERT collides instead of duplicating — or straight out ON CONFLICT DO NOTHING (05-05). With no business identity there's no possible idempotency.

Solution 3

1. After the ROLLBACK, the column doesn't exist: ERROR: column "weight_kg" does not exist. ALTER TABLE is transactional in PostgreSQL, so the ADD COLUMN and the subsequent UPDATE are undone together.

2. ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. That statement needs several internal transactions: it walks the table in two passes and waits between them for the in-flight transactions to finish, so as not to block writes. None of that fits inside a single user transaction. Why that variant exists is closed off in 09-05.

3. In MySQL, ALTER TABLE products ADD COLUMN weight_kg DECIMAL(6,3); causes an implicit commit and can't be undone, so the strategy changes completely. In PostgreSQL the whole file goes in one transaction: the third step fails, all three are undone and the schema is left intact; you fix it and rerun. In MySQL, the first two steps are already applied and committed and the third may have been left half-done: you have to write a reversal script (down) by hand for each step, apply them one at a time verifying in between, and design each one idempotent and backwards compatible — 05-06's expand/contract pattern stops being a good practice and becomes mandatory.

You now have the full control panel:

• The complete TCL repertoire, and BEGIN's options —ISOLATION LEVEL, READ ONLY and DEFERRABLE— with the golden combination for a report against production: SERIALIZABLE READ ONLY DEFERRABLE.
• The SAVEPOINTs: discarding one order from a batch without losing the previous ones, and —most useful of all— the only way of escaping 09-01's aborted state without closing the transaction. With their price: subtransactions, dead rows and lost sequence values. One per business unit, not one per statement.
• That sequences aren't undone by ROLLBACK or by ROLLBACK TO SAVEPOINT, and that's why the ids have gaps: in the batch, 21 and 23 survived, and 22 and 49 were lost.
• The drivers' autocommit, and the three surprises: psycopg and SQLAlchemy open a transaction on their own at the first query, JDBC opens none, and the ORMs' nested blocks are savepoints, not nested transactions.
• Transactional DDL: in PostgreSQL you can ROLLBACK a CREATE TABLE —and that's why its migrations are safe—, with seven exceptions headed by CREATE INDEX CONCURRENTLY. In MySQL you can't: every schema statement implicitly commits whatever was pending. It's what 05-06 announced. And prepared transactions, which you should almost never touch by hand.
• The application patterns: try / except / mandatory rollback(), idempotent retries with a business key, and no external call inside the transaction.
• And the integrating example: order 21 confirmed from start to finish, €47.00 + €4.95 = €51.95, with the stock levels at 118 and 39, and a savepoint before the reduction.

But that order still has a blind spot, and we've left it in plain sight on purpose: what happens if another customer buys the last matcha tea at the very same instant? Both sessions read 40, both subtract 1, and both write 39 — two units have been sold and only one has been deducted. In Isolation Levels and Concurrency Anomalies you'll see that lost update demonstrated step by step in two sessions, along with the dirty read, the non-repeatable read and the phantom read; the standard's four levels and which anomaly each one allows; what almost no course tells you —that PostgreSQL doesn't implement READ UNCOMMITTED and that its REPEATABLE READ already prevents phantoms, unlike the standard—; the 40001 error and why, if you use the high levels, your application has to know how to retry.