Module 8 ended by confessing a lie: for eighty-odd lessons we've assumed we're the database's only user. This module dismantles that assumption, and it starts with the piece that makes it manageable: the transaction, a unit of work the engine treats as indivisible. Either it all happens, or nothing does. Here you'll see why confirming an order in GreenStore —four operations over three tables— is a disaster waiting to happen if it isn't atomic; you'll understand autocommit, the number-one source of misunderstandings about transactions; you'll learn BEGIN, COMMIT and ROLLBACK with their full life cycle; and you'll meet the two-session format used to demonstrate everything else in the module. By the end you'll have two psql terminals open at once and you'll have seen, for the first time in the course, that two sessions don't see the same thing at the same time.

1. What a transaction is
2. The GreenStore case: confirming an order is four operations
3. What's left if the third step fails
4. Autocommit: every statement is already a transaction
5. BEGIN, COMMIT and ROLLBACK: the life cycle
6. How to know whether you're inside a transaction
7. The two-session format
8. Closing the session without committing, and what happens if the server goes down
9. The aborted state
10. Read-only transactions
11. Duration: short transactions and the idle in transaction problem
12. Common Mistakes and Tips
13. Exercises
14. Conclusion

A transaction is a set of SQL statements the engine runs as one single indivisible operation: either they're all applied or none is.

The textbook example is the bank transfer. Moving €100 from one account to another is two operations:

If the system goes down between the two, the €100 has ceased to exist. And there's no query that can detect it afterwards: both rows are individually valid; only the relationship between them is broken, and that relationship doesn't live in any column.

The transaction solves exactly that: it turns the two statements into one. The sublanguage that controls it is TCL (Transaction Control Language), the fourth of the ones named in 01-01 and the only one you hadn't used in depth yet.

Forget the banks: in GreenStore the case is richer and you have it in the database. A customer confirms their basket and the system has to do four things over three tables:

Pau Llorens Vidal (customer 6) buys through the web two olive oils, one matcha tea and —without knowing it's out of stock— a pack of soy wax candles. The four steps, exactly as the application runs them:

Sixty euros seventy-five of product plus €4.95 of shipping: €65.70. Now the step that fails:

Product 13 has stock = 0 and 05-01's CHECK (stock >= 0) prevents it going down to −1. Step 3 has failed halfway through. And step 4 —marking the order as paid— never gets to run at all.

This is the question to look at head-on. With no transaction, each statement committed itself and this is what's now in the database:

Three units of inventory have disappeared from the system without anybody buying them. Multiply that by a hundred orders a day and in a month the ERP's inventory looks nothing like the warehouse's. And there's no error logged anywhere: the application returned a message to the customer, the customer closed the tab, and the rows stayed put. The same thing, inside a transaction:

As if it had never happened. 20 orders, 47 lines, the stock levels untouched. That's the whole idea.

Here's misunderstanding number one, and it's worth saying without hedging:

In SQL there's no such thing as "being outside a transaction". Every statement runs inside one. If you don't open one explicitly, the engine opens an implicit one that lasts exactly as long as that statement and commits itself when it finishes. That's autocommit.

Two consequences to internalise:

1. A single statement is always atomic. 05-03's UPDATE products SET price = price * 1.05; affects 20 rows: either all 20 change, or none does. That's why INSERT ... ON CONFLICT (05-05) is safe and SELECT + INSERT isn't: one statement is atomic and two aren't.
2. ROLLBACK doesn't exist for what's already committed. When you see UPDATE 20 under autocommit, it's already on disk. There's no going back.

And the problem is that autocommit doesn't behave the same everywhere:

Dialect note: Oracle is the most surprising case: there an UPDATE with no subsequent COMMIT hasn't happened for anybody but you, and if you close the session it's lost. And at the opposite extreme, psycopg and SQLAlchemy do the opposite of what most people expect: your Python application is already inside an open transaction from the first query, even though you haven't written BEGIN anywhere. We'll come back to this in 09-03, because it explains half the mysterious locks in production.

Three words and you know it all:

START TRANSACTION is the SQL standard form and it works in PostgreSQL, MySQL and SQL Server; BEGIN is the shortest and the one everybody uses in PostgreSQL. Careful: in MySQL, BEGIN is also the start of a code block in procedures, so there it's better to write START TRANSACTION so there's no ambiguity.

The full life cycle, with the aborted state we'll see in section 9:

Notice the most dangerous transition in the diagram: a COMMIT on an aborted transaction commits nothing, it does a ROLLBACK. PostgreSQL replies ROLLBACK instead of COMMIT, and if your script doesn't look at that reply, it'll believe it saved the data.

It's the most frequent practical question, and psql answers it in the prompt itself:

The final character is > for a normal user and # for a superuser, so an administrator inside a transaction sees greenstore=*#. What always matters is the asterisk: it appears right after the BEGIN and disappears with the COMMIT or the ROLLBACK. From SQL:

It returns NULL as long as the transaction hasn't written anything, because PostgreSQL doesn't spend a transaction identifier on somebody who only reads; the moment you do an INSERT or an UPDATE, a number appears. Its sibling pg_current_xact_id() (formerly txid_current(), which still works) forces the assignment, so it always returns a number — and that's why it's no good for diagnosis: by asking, it changes the answer.

And two psql helpers: \echo :ROW_COUNT prints the rows affected by the last statement —05-03's UPDATE N, but usable inside a script— and \set ON_ERROR_STOP on makes psql abort the file at the first error instead of carrying on firing statements at an already dead transaction. It's mandatory in any migration script.

Everything left in the module is about what happens when two people work at the same time, and that can't be seen in a normal psql output. From here on we'll always show it like this:

How to reproduce it yourself. Open two terminals and in each one run psql -h localhost -U sql_course -d greenstore. We'll call the first one Session A and the second Session B. Run the statements in the order of the instants t1, t2, t3… alternating terminals. Every example in the module is designed to be done this way, and you won't really understand any of them until you type them.

The first demonstration: what each session sees before and after the COMMIT.

Read it slowly, because those seven lines contain the whole module:

• At t4, A sees 39. It's its own change: every transaction always sees what it has done itself.
• At t5, B sees 40. A's change exists, it's written, but it isn't committed, and as far as B is concerned it may as well not exist. B doesn't block, doesn't wait, gets no warning: it simply reads the previous good value.
• At t7, after the COMMIT, B sees 39. The change has been made public all at once, and as far as B is concerned it happened in its entirety at the instant of the COMMIT, not spread between t3 and t6.

What you've just seen is isolation, ACID's third letter, and we'll study it in depth in 09-02 and 09-04. And the mechanism that makes it possible without B having to wait —two values of the same row coexisting— is MVCC, the answer to the question 08-05 left open about bloat.

When the example needs the full SQL rather than a summary, we'll use two blocks labelled with the instant:

And for 09-05's deadlocks, mermaid sequence diagrams. Whatever the format, the rule doesn't change: it always shows what each session sees at each instant and which one is left waiting.

The two unexpected endings have the same answer, and it's a reassuring one:

The mental rule: what's committed survives everything; what isn't committed survives nothing. There's no intermediate state, and no way for a transaction to end up "half applied" after a crash.

You're going to see this message, for certain, and probably today:

It happens like this:

Notice the prompt: it went from =*> to =!> the moment there was an error. From then on PostgreSQL rejects any statement, even a harmless SELECT COUNT(*), with code 25P02.

Why it's like that, and why it's the right thing. The transaction promised atomicity: all or nothing. The moment a statement fails, "all" is already impossible, so the only promise the engine can still keep is "nothing". Letting you carry on would mean allowing you to commit a partial result believing it's complete — exactly section 3's disaster, but with a transaction's quality seal on top.

How to get out: there are two doors and both end the transaction. ROLLBACK; undoes everything, and it's the honest exit. COMMIT; replies ROLLBACK and undoes everything just the same. There's a third that doesn't close the transaction and saves the work already done: going back to a SAVEPOINT from before the error, one of the reasons savepoints exist (09-03).

Dialect note — and it's an enormous divergence. MySQL/InnoDB doesn't abort the whole transaction. If a statement fails, only that statement is undone and the transaction stays alive and accepting orders; you can COMMIT and you'll commit everything from before the error. SQL Server sits in the middle: it depends on the error's severity and on SET XACT_ABORT ON (which makes it behave like PostgreSQL, and is what's recommended). Oracle also undoes only the failed statement. Practical consequence: a script tested on MySQL that "works" may be committing partial results; the same script on PostgreSQL will fail noisily. The noisy version is the good one.

They're declared like this:

And if you try to write inside: ERROR: cannot execute UPDATE in a read-only transaction. Four reasons to declare them:

1. It's a safety net against yourself. A monthly report shouldn't be able to modify anything; with READ ONLY, an UPDATE pasted in by mistake is an error and not an incident.
2. It doesn't consume a transaction identifier, which reduces the pressure on MVCC and on transaction freezing (09-02).
3. It's mandatory on a read-only replica. If your report points at a secondary, better for the write to fail on your laptop.
4. It enables DEFERRABLE, which lets a long report run in SERIALIZABLE mode with no risk of aborting through a conflict (09-03).

This is the one operational rule worth memorising from this lesson:

A transaction should be opened as late as possible and closed as soon as possible. Never, ever wait inside an open transaction: no user input, no HTTP call, no file read, no sleep.

An open, idle transaction (the idle in transaction state) does three kinds of damage at once:

The real case is always the same: a form that opens a transaction, shows a confirmation screen and waits. The user goes for lunch. The shop stops. How to detect it:

One hour and forty-two minutes with the matcha's row locked. The automatic defence is a parameter every production server should have set —SET idle_in_transaction_session_timeout = '5min';— settable per session, per user, per database or in postgresql.conf. Together with statement_timeout and lock_timeout it forms the survival kit 09-05 details.

• Believing that "I'm not using transactions". You are: every standalone statement is one. The question isn't whether there's a transaction, but where it starts and where it ends.
• Assuming autocommit behaves the same everywhere. In Oracle it's off; in psycopg and SQLAlchemy your application is already inside an open transaction without you having written BEGIN.
• Doing a BEGIN and forgetting to close it. The module's most expensive operational error: you lock rows, you prevent VACUUM and you occupy a connection. Look at the prompt's asterisk before you get up.
• Ignoring the aborted state, or not checking the COMMIT's reply. After an error everything fails with 25P02 until the ROLLBACK; and on an aborted transaction, COMMIT returns ROLLBACK and saves nothing. If your script doesn't look at it, it'll believe it saved.
• Running a .sql file without \set ON_ERROR_STOP on. psql will carry on firing a hundred statements at a dead transaction and the real error will be buried among a hundred 25P02 messages.
• Reducing stock line by line with no transaction. It's section 3: broken inventory, silently and with no trace.
• Waiting inside an open transaction. User input, a call to the payment gateway, reading a large file. Prepare the data outside, open, write, close.
• Tip: adopt BEGIN … check … COMMIT/ROLLBACK as a reflex on any manual UPDATE or DELETE, and BEGIN TRANSACTION READ ONLY for your reports: it costs two words and makes the accident impossible.
• Tip: always keep two psql terminals open while you study this module. It's the only way to see concurrency.

Work on the freshly reloaded database (greenstore.sql) and with two psql terminals open.

Reproduce section 3's disaster and then its correct version.

1. With no transaction, run the four steps of confirming Pau's order with the three lines (products 1, 15 and 13). Note what fails.
2. Write a query that demonstrates the inconsistency: orders, lines and stock levels of the three products involved.
3. Reload the script and repeat everything inside BEGIN … ROLLBACK. Check with the same query that no trace remains.
4. What would have happened if step 3 had been written as a single UPDATE ... FROM order_lines (05-03) instead of three statements? Would the transaction still be needed?

Calling the statement INSERT INTO orders (customer_id, employee_id, order_date, status, payment_method, shipping_cost) VALUES (6, NULL, DATE '2026-03-05', 'pending', 'card', 4.95) RETURNING id; INSERT_ORDER, predict what each session will see before running anything, and then check it with two terminals:

The t6 question is the interesting one: which identifier does B's order get, given that A's was undone?

In one session, deliberately trigger the aborted state and get out of it in both possible ways.

1. BEGIN;, a valid UPDATE on products and then an INSERT that violates a foreign key (for example, an order with customer_id = 999).
2. Try to run SELECT 1;. Copy the exact message.
3. Exit with COMMIT; and note what the server replies. Then check whether the valid UPDATE was applied.
4. Repeat everything, exiting with ROLLBACK;, and compare.
5. Explain in two sentences why MySQL would behave differently and which of the two behaviours you'd prefer for a billing script.

Solution 1

1 and 2. The third UPDATE fails with products_stock_check, because product 13 is at 0. The query that reveals the mess:

An order nobody is going to charge for, three lines orphaned of purpose and three units of inventory evaporated.

3. With BEGIN at the start and ROLLBACK at the end, the same query returns 20, 47, (null), 120, 40, 0. The exact initial state.

4. With a single UPDATE ... FROM, step 3 would be atomic in itself: on violating the CHECK on one of the three rows, none would be applied, and the stock levels would stay at 120 and 40. But the transaction is still needed, for two reasons: order 21 and its three lines have already been inserted and committed by steps 1 and 2, so the inconsistency persists; and step 4 doesn't run either. One statement's atomicity doesn't give the process atomicity: the unit of work is the order, not the UPDATE.

Solution 2

At t3, B sees 20: A's INSERT isn't committed and as far as B is concerned it doesn't exist. At t5, B still sees 20, because A did a ROLLBACK and the order never existed for anybody. And at t6, the id B gets is 22, not 21. That 22 is the important part. Sequences aren't undone by a ROLLBACK, as 05-02 and 05-05 already warned: A consumed the value 21 when inserting and that value was lost when it was undone. It's deliberate — if nextval respected transactions, two sessions would have to wait for each other to get an identifier, and that would destroy the performance of any system with concurrent inserts. The price is that the ids have gaps, and the practical consequence (why a PK shouldn't be used as an invoice number) is developed in 09-05.

Solution 3

2. The message, literally: ERROR: current transaction is aborted, commands ignored until end of transaction block.

3. The server replies to COMMIT; with ROLLBACK, and the valid UPDATE hasn't been applied: the whole transaction was undone. That mismatched reply —you ask for COMMIT and you're answered ROLLBACK— is the signal that you're committing a dead transaction.

4. With ROLLBACK; the result is identical, but honest: you asked to undo and it undid. The difference isn't in the data, but in the fact that in the first case a script that doesn't read the reply will believe it saved.

5. In MySQL/InnoDB, the failed INSERT would have undone only itself and the transaction would have stayed alive; the COMMIT would have committed the UPDATE on products. For a billing script PostgreSQL's behaviour is preferable: a half-finished invoice is worse than no invoice, and the noisy error forces you to look. MySQL's is more convenient in bulk loads where rejected rows are tolerated, but it demands checking each statement's result by hand.

You now have the unit of work that was missing:

• A transaction is a set of statements applied all or none. It's controlled with TCL: BEGIN, COMMIT and ROLLBACK.
• The GreenStore case: confirming an order is four operations over three tables, and if the third fails halfway you're left with an uncharged order, three orphan lines and three units of inventory evaporated — with no error logged anywhere.
• Autocommit isn't the absence of transactions: it's one transaction per statement. That's why one statement is always atomic and two never are. And that's why it matters to know that Oracle doesn't turn it on, and that psycopg and SQLAlchemy do exactly the opposite of what almost everybody assumes.
• The life cycle: active → committed / rolled back, with the detour into the aborted state the moment a statement fails. There PostgreSQL rejects everything with 25P02 and a COMMIT replies ROLLBACK; MySQL, by contrast, undoes only the failed statement. And to know where you are: the prompt's asterisk (greenstore=*>), the exclamation mark if it's aborted (=!>) and pg_current_xact_id_if_assigned().
• The two-session format we'll use throughout the module, and its first lesson: before the COMMIT, B sees the old value without waiting or even noticing; afterwards, it sees it whole and all at once.
• Closing the session or the server going down are equivalent to a ROLLBACK; what's committed always survives. Read-only transactions for your reports, and the golden rule: short, and never waiting for anybody. An idle in transaction transaction locks rows, prevents VACUUM —08-05's bloat— and occupies a connection.

You've seen what a transaction does. What's missing is what exactly it guarantees, and that answer has four letters. In ACID Properties we'll take apart one by one atomicity (and the mechanism that makes undoing possible), consistency (where the database's responsibility ends and yours begins, with the stock that can't go negative as the case study), isolation (why B saw 40 while A saw 39) and durability (the WAL, fsync and why your data survives a power cut). And at last MVCC will arrive: how PostgreSQL keeps several versions of each row, how to see them with SELECT xmin, xmax, *, and why dead rows, bloat and the need for VACUUM that 08-05 left pending come out of that.