First look at transactions
On This Lesson 11 topics
Lesson Overview
Constraints judge one row at a time. Some jobs, though, take several statements that only make sense together. Lending a book to a member needs two changes: one fewer copy on the shelf, and one new loan record. If the first change happens and the second fails, the library "loses" a book that nobody holds.
A transaction solves this. It groups statements into one unit that either happens completely or not at all. In the library picture, it is a librarian who keeps the work on a tray and only walks it to the shelf when everything on the tray is correct; if something is wrong, the tray is emptied and the shelves are untouched.
This is a first look: the three commands BEGIN, COMMIT, and ROLLBACK, and the all-or-nothing idea. The deeper topics (isolation, locks) come in later levels.
This lesson builds on Lesson 4 (constraints and their errors) and BEG-050 (UPDATE ... WHERE, INSERT).
Learning Objectives
By the end of this lesson you will be able to:
- Explain what a transaction is and what "all or nothing" means.
- Use
BEGIN,COMMIT, andROLLBACKinpsql. - Show that a rolled-back change leaves the data as it was.
- Describe what happens after an error inside a transaction block.
- Explain why a single statement is already atomic without
BEGIN. - Choose a realistic case where several statements need one transaction.
Written Explanation
What a transaction is
According to the PostgreSQL tutorial, a transaction bundles several steps into a single all-or-nothing operation. Other sessions do not see the in-between states, and if the transaction cannot finish, none of its steps take effect. The word for this is atomic: from the outside, it either happened entirely or not at all.
The tutorial's own example is a bank transfer: debiting one account and crediting another must not be split. Our library example is the same shape.
Three commands
| Command | What it does |
|---|---|
BEGIN; | Start a transaction block |
COMMIT; | Make all changes since BEGIN permanent and visible to others |
ROLLBACK; | Cancel all changes since BEGIN |
Between BEGIN and the end, you are looking at your own changes, which nobody else can see yet. COMMIT publishes them. ROLLBACK throws them away.
Try a rollback
The books table has id = 1 with 3 copies. In psql:
BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 1;
SELECT id, copies FROM books WHERE id = 1;
ROLLBACK;
SELECT id, copies FROM books WHERE id = 1;Real output:
BEGIN
UPDATE 1
id | copies
----+--------
1 | 2
ROLLBACK
id | copies
----+--------
1 | 3Inside the transaction, you saw your own change (2 copies). After ROLLBACK, the row was back to 3. The UPDATE was real while it lasted, and the rollback undid it. This is the "experiment safely" tool: try a risky UPDATE, check the result with SELECT, and roll back if it is wrong.
Commit a transaction
BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 1;
INSERT INTO loans (id, book_id, member_id) VALUES (2, 1, 2);
COMMIT;Both changes become permanent together. Afterward the copies count is 2 and loans has one more row. Once COMMIT returns, the manual describes the change as durable: stored so that a crash will not lose it.
Errors inside a transaction
Suppose the second statement fails, for example the loan names member 999 who does not exist:
BEGIN
UPDATE 1
ERROR: insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL: Key (member_id)=(999) is not present in table "members".
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACKAfter the first error, PostgreSQL marks the transaction as failed. Every later command is ignored with "current transaction is aborted" (the SELECT 1 that followed got that message) until you end the block. Ending it with ROLLBACK (even COMMIT would roll back a failed transaction in PostgreSQL) discards the first UPDATE as well. Checking afterward shows copies still at 3. This is exactly the all-or-nothing guarantee: the book was not "lost" just because the loan insert failed.
You can also roll back deliberately, because you decided the result looks wrong. The tutorial also describes SAVEPOINT for undoing only part of a transaction; you do not need it yet.
Every statement is already a transaction
If you do not type BEGIN, PostgreSQL still treats each statement as its own tiny transaction: the manual says it is wrapped in an implicit BEGIN and, if it succeeds, COMMIT, and a failing statement is rolled back. That is why a failed single INSERT in Lesson 4 left the table unchanged, and why a multi-row INSERT is all or nothing. BEGIN is for when several statements must be one unit.
Some client libraries start and commit transactions for you, so check your tool's documentation. Do not assume a click in a GUI is or is not in a transaction without looking at how that tool is configured.
Be careful with stray commands
If you run COMMIT or ROLLBACK when no transaction is open, PostgreSQL does not fail; it warns:
WARNING: there is no transaction in progressThat warning is useful: it tells you your mental picture ("I'm inside a transaction") was wrong, possibly because an earlier command already ended it.
DDL can roll back too (in PostgreSQL)
In our test, this worked:
BEGIN;
DROP TABLE staging.tmp_books;
ROLLBACK;and the table was still there afterwards. PostgreSQL lets structure changes sit inside a transaction. Not every database does this, and some commands cannot run in a transaction at all: CREATE DATABASE is one, as Lesson 3 noted. Treat it as a PostgreSQL feature you can use, not a rule for every system. It makes "try the migration, look, roll back" a safe habit.
Examples
Example 1: undo an experiment (simple)
BEGIN;
UPDATE books SET copies = 0; -- oops, no WHERE
SELECT id, copies FROM books;
ROLLBACK;The UPDATE hits every book and the SELECT shows every copies value at 0, but inside a transaction you see the damage and undo it. Lesson BEG-050 taught you to guard UPDATE with WHERE; the transaction is a second net, not a replacement.
Example 2: lending a book (practical)
BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 1;
INSERT INTO loans (id, book_id, member_id) VALUES (2, 1, 2);
COMMIT;Afterward: books.copies for id 1 is one lower, and a loan row for member 2 exists. The two changes became visible to other users together at COMMIT, never one without the other. The CHECK (copies >= 0) from Lesson 4 still guards each row inside the transaction: lending the last copy works, lending a copy that does not exist fails.
Example 3: the second step fails (realistic)
BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 1;
INSERT INTO loans (id, book_id, member_id) VALUES (3, 1, 999);The insert fails (member 999 does not exist). The session is now in a failed transaction. You type ROLLBACK;, then check:
SELECT id, copies FROM books WHERE id = 1;The copy count is unchanged. Compare this with running the same two statements without BEGIN: the UPDATE would have committed by itself, the INSERT would fail, and the library would have one copy missing with no loan to explain it. That difference is the whole reason transactions exist.
Backend Insight
Almost every non-trivial backend operation is "several statements, one meaning": place an order and reduce stock, transfer money, register a user and create their profile. Frameworks you will meet later let you mark a Java method as transactional, and they issue the BEGIN, COMMIT, or ROLLBACK for you: an exception inside the method means rollback. The idea you learn at the psql prompt is the same idea.
Two practical rules. Keep transactions short: a transaction left open (for example someone ran BEGIN and went to lunch) can hold up other work, which later modules explain under locks. And never leave a session sitting in an unfinished transaction by accident: check whether you are in one before you walk away.
Common Mistakes
Mistake 1: Forgetting COMMIT
Everything inside the block looks fine, but if you disconnect without COMMIT, the changes are not saved. When a script is supposed to change data, end it with COMMIT explicitly.
Mistake 2: Continuing after an error inside the block
After an error, further commands are ignored until you roll back. Do not try to "fix it" with more statements in the same block; roll back and start again.
Mistake 3: Thinking ROLLBACK can undo a committed change
It only undoes changes since the last BEGIN. After COMMIT, the change is permanent; undoing it takes new statements (or a backup).
Mistake 4: Wrapping unrelated work in one big transaction
A transaction should hold statements that belong together. Putting an hour of unrelated work in one block means one error cancels everything, and long transactions cause problems for other users.
Exercises
Try each exercise yourself before opening the solutions.
Level A: Foundation
A1. What does ROLLBACK do?
Show solution A1
A1. It cancels all changes made since BEGIN, so the data returns to how it was.
A2. What does COMMIT do?
Show solution A2
A2. It makes all changes since BEGIN permanent and visible to other sessions.
A3. Is a single UPDATE without BEGIN already protected from half-finished results? Explain briefly.
Show solution A3
A3. Yes. Each statement is its own implicit transaction; if it fails, it is rolled back, so it cannot be half done. A transaction is needed when several statements must succeed together.
Level B: Application
B1. Write the statements to lend book 2 to member 1 as one transaction (decrease copies, insert a loan with id 5), ending with a commit.
Show solution B1
B1.
BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 2;
INSERT INTO loans (id, book_id, member_id) VALUES (5, 2, 1);
COMMIT;B2. You run BEGIN; UPDATE books SET copies = copies - 1 WHERE id = 2;, then notice a typo and want to cancel. What do you type, and what does SELECT copies FROM books WHERE id = 2; show afterward, compared with before?
Show solution B2
B2. ROLLBACK;. The SELECT then shows the same value as before the BEGIN. (Inside the block it would have shown one less.)
B3. In a block, the third statement errors. You type a correct fourth statement; what happens, and what should you do?
Show solution B3
B3. The fourth statement is ignored with "current transaction is aborted, commands ignored until end of transaction block". Type ROLLBACK;, correct the problem, and start the whole unit again.
Level C: Challenge
C1. A script runs these two statements without BEGIN, and the second fails with a foreign key error: UPDATE books SET copies = copies - 1 WHERE id = 1; then INSERT INTO loans (id, book_id, member_id) VALUES (9, 1, 999);. What state is the data in, and how does a transaction change that?
Show solution C1
C1. The UPDATE was committed on its own, so one copy is missing, but the INSERT failed, so no loan explains it: inconsistent data. In a transaction, the error would lead to ROLLBACK, which would also undo the UPDATE, leaving the copy count as it was.
C2. A colleague says "I always wrap everything, including CREATE DATABASE, in BEGIN ... COMMIT to be safe". Evaluate that, and say what you would test before trusting a claim about DDL in transactions.
Show solution C2
C2. PostgreSQL rejects CREATE DATABASE inside a transaction block, so that habit fails at least there. Many DDL statements can be rolled back in PostgreSQL, but this differs between database products, so test (as the lesson did with DROP TABLE) or read the documentation for the specific statement and product before relying on it.
Mini Challenge
Using the Library tables, do three runs and write down the observed outputs:
- A transaction with an
UPDATEon one book that you roll back; show the value before, inside, and after. - A transaction that lends a book (update plus loan) and commits; show the count of loans before and after.
- A transaction with a failing second step; show the error, the "aborted" message from one more command, the
ROLLBACK, and the unchanged value.
Check your work: in run 3 the copy count after rollback equals the count before BEGIN, and in run 2 both changes appear.
Summary & Next Lesson
Key takeaways
- A transaction groups statements into an all-or-nothing unit;
BEGINstarts it,COMMITsaves it,ROLLBACKundoes everything sinceBEGIN. - After an error inside a block, later commands are ignored until you end the block; the failed work is not kept.
- Without
BEGIN, each statement is already its own transaction. - PostgreSQL lets many structure changes (such as
DROP TABLE) roll back, butCREATE DATABASEcannot be run in a transaction block. - Isolation, locks, and concurrency are for later levels.
Next lesson
The next lesson, "Demo: creating the Library database with constraints", puts everything together: a re-runnable schema.sql, planned mistakes that the constraints reject, and a rollback demonstration, which prepares the module's lab.
Self-check
- What are the three commands that control a transaction?
- What does PostgreSQL do with commands after an error in a transaction block?
- Why is "lend a book" a good example for a transaction?
- Can
ROLLBACKundo something already committed? - What does
WARNING: there is no transaction in progresstell you?
Show answers
BEGIN,COMMIT, andROLLBACK.- It ignores them until the block ends, saying the transaction is aborted.
- It needs two changes (fewer copies, a new loan) that must both happen or neither.
- No. It only undoes changes since
BEGIN. - You ran
COMMITorROLLBACKwhile no transaction was open, so your picture of the session was wrong.
References
- PostgreSQL documentation, Transactions (tutorial). Supports: transactions are all-or-nothing and atomic,
BEGIN/COMMIT/ROLLBACK, durability after commit, savepoints, and that each statement outside a block is wrapped in its own implicit transaction. - PostgreSQL documentation, CREATE DATABASE. Supports:
CREATE DATABASEcannot run inside a transaction block. - The aborted-transaction message, the no-transaction warning, and the
DROP TABLErollback were observed on a local PostgreSQL 18.3 test server. I also ranCOMMITafter a failed statement: PostgreSQL answeredROLLBACK.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.