Transactions · LearningSQL.org

Imagine transferring money between two bank accounts. You debit one account and credit another. What if the system fails between those two operations? One account lost money, but the other never received it. Transactions give you a way to group operations so that a failure halfway through does not have to leave the books like that.

What You'll Learn

By the end of this lesson you will write a multi-statement change that can be undone as a unit, and — more importantly — you will be able to say which statement in a script decides whether the undo actually happens. That second half is the real skill here, because the database does not decide it for you: when one statement inside a transaction fails, this engine aborts only that statement and a later COMMIT persists everything else. All-or-nothing is something your script asks for with an explicit ROLLBACK, not something the engine hands you.

This is the milestone the warehouse's safe-mutation stage stands on — the capstone has to move stock and record an order together, and a half-applied version of that pair is a warehouse whose numbers no longer add up. You arrive here able to change rows with INSERT, UPDATE and DELETE (Inserting and Updating Data); what is new is bracketing several of those changes so you can still take them back.

What is a Transaction?

A transaction is a sequence of SQL operations bracketed by BEGIN and either COMMIT or ROLLBACK. Everything after BEGIN is provisional: COMMIT makes the whole group permanent, ROLLBACK discards the whole group.

BEGIN, COMMIT, and ROLLBACK

-- Create an accounts table
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL
);

INSERT INTO accounts (id, owner, balance)
VALUES
    (1, 'Alice', 1000.00),
    (2, 'Bob', 500.00),
    (3, 'Carol', 750.00);

-- Show initial balances
SELECT * FROM accounts;

-- Begin a transaction: transfer $200 from Alice to Bob
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE owner = 'Bob';

-- Commit the transaction (make changes permanent)
COMMIT;

-- Verify the transfer
SELECT * FROM accounts;

BEGIN TRANSACTION (or just BEGIN) starts a new transaction
COMMIT saves all changes made during the transaction
ROLLBACK undoes all changes made during the transaction

Notice what the run prints and what it does not. The two UPDATEs each report 1 row(s) affected, but BEGIN and COMMIT print nothing at all — they are bookkeeping, not results. If you run it and see only the inserts and the two SELECTs, nothing has gone wrong.

Two variations to try on the fence above — open it with its Open in Playground pill, then edit and Run Code. Predict each one before you run it.

Change COMMIT; to ROLLBACK;. Both UPDATEs still report 1 row(s) affected — the rows really did change while the transaction was open — but the final SELECT prints the starting balances again, 1000.0 and 500.0. The affected-row counts describe what the statement did, not what survived.
Delete the BEGIN TRANSACTION; line, keeping the COMMIT. The transfer still happens, because a statement outside a transaction commits on its own; but the script now ends with Error: cannot commit - no transaction is active and exits 1, because there is no open transaction for COMMIT to close. That error is the cheapest way to find out whether a BEGIN really ran.

Rolling Back Changes

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO products (id, name, price, stock)
VALUES
    (1, 'Laptop', 999.99, 50),
    (2, 'Mouse', 24.99, 200),
    (3, 'Keyboard', 79.99, 150);

-- Show initial state
SELECT * FROM products;

-- Start a transaction
BEGIN TRANSACTION;

-- Make some changes
UPDATE products SET price = 0 WHERE name = 'Laptop';
UPDATE products SET stock = -999 WHERE name = 'Mouse';

-- Oops! Those changes are wrong. Roll back.
ROLLBACK;

-- Verify nothing changed
SELECT * FROM products;

ROLLBACK is your undo button. Any changes made after BEGIN are discarded when you ROLLBACK. The database returns to exactly the state it was in before the transaction started.

What a Failing Statement Does Inside a Transaction

Here is the part most people get wrong, and it is worth slowing down for. A transaction is a bracket you can discard — it is not a watchdog. If one statement inside the bracket fails, the engine rejects that statement and keeps going; the statements around it are still sitting in the open transaction, and a COMMIT at the bottom of the script makes them permanent.

So the model to carry is this: a failing statement rolls back itself, and nothing else. What rolls back the group is the word ROLLBACK, and something has to decide to say it. In an application that decision lives in the error handler; in a script you run by hand it lives in your own reading of the output. Either way it is a decision, not a guarantee.

The fence below is deliberately wrong so you can watch it happen. Alice has 500 and a CHECK constraint forbids a negative balance, so the 600 withdrawal cannot stand — but look at what Bob ends up with.

CREATE TABLE bank_accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL,
    balance REAL NOT NULL CHECK(balance >= 0)
);

INSERT INTO bank_accounts (id, owner, balance)
VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 300.00);

BEGIN TRANSACTION;
UPDATE bank_accounts SET balance = balance + 600 WHERE owner = 'Bob';
UPDATE bank_accounts SET balance = balance - 600 WHERE owner = 'Alice';
COMMIT;

SELECT id, owner, balance FROM bank_accounts ORDER BY id;

The withdrawal from Alice is refused, and the refusal is reported on the error channel:

Error: CHECK constraint failed: balance >= 0

But the script does not stop there. The final SELECT still runs, and it prints 1 | Alice | 500.0 beside 2 | Bob | 900.0: Bob was credited 600 that Alice never paid. The COMMIT persisted the half of the transfer that succeeded. Nothing about the failure was undone except the one statement that caused it.

The fix is one word. Replace COMMIT; with ROLLBACK; and both balances return to 500.0 and 300.0 — the credit to Bob disappears along with the rejected withdrawal, because ROLLBACK discards everything after BEGIN whether it succeeded or not.

ACID Properties

Transactions are usually described by four properties, known by the acronym ACID. Read them as what a transaction offers, not as what happens automatically:

Atomicity — the changes between BEGIN and the end of the transaction are a single unit you can accept or discard. Discarding them is what ROLLBACK does; as the fence above shows, a failed statement does not trigger it for you.

Consistency — constraints are checked on every statement, so a transaction cannot leave behind a row that violates one.

Isolation — other connections do not see your uncommitted changes.

Durability — once committed, changes survive a restart of the database.

Consistency and durability you get whether you ask or not. Atomicity is the one with a catch, and the catch is the whole reason this lesson exists.

Predict

A different script, same shape. Two rows start at 100 each and a CHECK forbids a negative balance. The second UPDATE asks for 999 from a row that has 100, so it will be refused. The script ends with COMMIT, and then reads both rows back. What does the final SELECT print?

CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER NOT NULL CHECK(balance >= 0));
INSERT INTO accounts VALUES ('ada', 100), ('bo', 100);
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'bo';
UPDATE accounts SET balance = balance - 999 WHERE name = 'ada';
COMMIT;
SELECT name, balance FROM accounts ORDER BY name;

Both rows at 100. The failed statement aborts the whole transaction, so neither change survives.
ada at 100 and bo at 70. Only the refused statement was undone, and COMMIT kept the rest.
Nothing at all. The error stops the script before the SELECT can run.

SAVEPOINT

SAVEPOINTs let you create checkpoints within a transaction. You can roll back to a specific savepoint without discarding the entire transaction.

-- Create an orders system
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    status TEXT DEFAULT 'pending'
);

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO inventory (id, product, stock)
VALUES
    (1, 'Widget', 100),
    (2, 'Gadget', 50),
    (3, 'Gizmo', 25);

-- Start a transaction for processing multiple orders
BEGIN TRANSACTION;

-- Process first order successfully
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (1, 'Alice', 'Widget', 10, 'confirmed');
UPDATE inventory SET stock = stock - 10 WHERE product = 'Widget';

-- Create a savepoint before the risky second order
SAVEPOINT before_second_order;

-- Process second order
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (2, 'Bob', 'Gizmo', 30, 'confirmed');
UPDATE inventory SET stock = stock - 30 WHERE product = 'Gizmo';

-- Bob ordered 30 Gizmos against a stock of 25. Nothing refuses it:
-- this table has no CHECK, so the stock silently went to -5.
-- Roll back only the second order.
ROLLBACK TO before_second_order;

-- Process a corrected second order
INSERT INTO orders (id, customer, product, quantity, status)
VALUES (2, 'Bob', 'Gizmo', 20, 'confirmed');
UPDATE inventory SET stock = stock - 20 WHERE product = 'Gizmo';

-- Commit everything
COMMIT;

-- Verify results
SELECT id, customer, product, quantity, status FROM orders ORDER BY id;
SELECT id, product, stock FROM inventory ORDER BY id;

Read the Gizmo row twice. During the transaction it really did go to -5, and no error was raised — the table declares no CHECK, so the database had nothing to refuse. What put it back to 25 was ROLLBACK TO before_second_order, and what left it at 5 afterwards was the corrected order. A savepoint is an undo point you place yourself; it detects nothing on your behalf.

SAVEPOINTs are especially useful in long transactions where you want to undo part of the work without losing everything. You can create multiple savepoints and roll back to any of them, and ROLLBACK TO leaves the outer transaction open, so a COMMIT after it still commits the work you kept.

Here is the same idea stripped to its shortest runnable form. The two rows are stocked and a transaction is already open:

CREATE TABLE inventory (product TEXT PRIMARY KEY, stock INTEGER NOT NULL);
INSERT INTO inventory VALUES ('widget', 100), ('gizmo', 25);
BEGIN;

Arrange the code

A transaction is already open on the stock above. These five lines take 30 gizmos off a shelf that holds 25, notice the problem, and put them back; the third line is a check that only prints a row while some product is oversold. Put them in the order that leaves the shelf correct — then answer what the order is really testing: which two lines does the check have to sit between for it to prove anything, and what would the last line print if the undo came after it instead of before?

ROLLBACK TO risky;
SAVEPOINT risky;
SELECT product, stock FROM inventory ORDER BY product;
UPDATE inventory SET stock = stock - 30 WHERE product = 'gizmo';
SELECT product, stock FROM inventory WHERE stock < 0;

Transaction Patterns for Real Applications

The Try-and-Verify Pattern

A common pattern is to perform operations and verify the results before committing.

-- Create employee and payroll tables
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary INTEGER NOT NULL
);

CREATE TABLE payroll_log (
    id INTEGER PRIMARY KEY,
    employee_id INTEGER,
    amount INTEGER,
    pay_date TEXT,
    type TEXT
);

INSERT INTO employees (id, name, salary)
VALUES
    (1, 'Alice', 80000),
    (2, 'Bob', 75000),
    (3, 'Carol', 90000);

-- Give everyone a 10% raise and log it
BEGIN TRANSACTION;

-- Apply the raises
UPDATE employees SET salary = ROUND(salary * 1.10);

-- Log each raise
INSERT INTO payroll_log (id, employee_id, amount, pay_date, type)
SELECT
    id,
    id,
    ROUND(salary * 0.10 / 1.10),
    '2025-02-01',
    'raise'
FROM employees;

-- Verify: check the new total salary budget
SELECT
    SUM(salary) AS new_total_budget,
    COUNT(*) AS employees_affected
FROM employees;

-- If everything looks correct, commit
COMMIT;

-- Final state
SELECT e.name, e.salary, p.amount AS raise_amount
FROM employees e
LEFT JOIN payroll_log p ON e.id = p.employee_id
ORDER BY e.id;

The verify SELECT is the point of the pattern: it runs while the transaction is still open, so its number is what you would be committing. Reading before committing is what turns the last line of a transaction into a decision rather than a formality — once COMMIT has run there is no undo left to reach for. The block below is a transaction where nobody made that decision.

Debug

This script moves 3000 cents from ada to a second account, inside a transaction, and then reads both balances back. It runs, exits cleanly, and prints ada at 2000 and bo at 2000 — so 3000 cents have left ada and arrived nowhere. Commit a hypothesis about why the transaction did not protect ada, then fix the script so both balances are what they started as.

CREATE TABLE accounts (name TEXT PRIMARY KEY, cents INTEGER NOT NULL CHECK(cents >= 0));
INSERT INTO accounts VALUES ('ada', 5000), ('bo', 2000);
BEGIN;
UPDATE accounts SET cents = cents - 3000 WHERE name = 'ada';
UPDATE accounts SET cents = cents + 3000 WHERE name = 'zoe';
COMMIT;
SELECT name, cents FROM accounts ORDER BY name;

Expected output:

2 row(s) affected
1 row(s) affected
0 row(s) affected
name | cents
------------
ada | 5000
bo | 2000

Before you change anything — why do you think this fails?

Batch Operations

Wrapping batch inserts in a transaction means the database commits once at the end rather than once per row. On a database that writes to disk, that difference is the reason batch loads are wrapped; on the in-memory database behind the Run button there is no disk to flush, so you can see the shape here but not the speedup. Do not take a timing lesson from this fence — take the shape.

-- Create a logs table
CREATE TABLE event_log (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    message TEXT,
    created_at TEXT
);

-- Without a transaction, each INSERT would auto-commit (slow)
-- With a transaction, all inserts commit once (fast)
BEGIN TRANSACTION;

INSERT INTO event_log (id, event_type, message, created_at)
VALUES (1, 'LOGIN', 'User alice logged in', '2025-01-15 08:00:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (2, 'PAGE_VIEW', 'alice viewed dashboard', '2025-01-15 08:01:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (3, 'ACTION', 'alice created report', '2025-01-15 08:05:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (4, 'LOGIN', 'User bob logged in', '2025-01-15 08:10:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (5, 'PAGE_VIEW', 'bob viewed settings', '2025-01-15 08:11:00');
INSERT INTO event_log (id, event_type, message, created_at)
VALUES (6, 'LOGOUT', 'User alice logged out', '2025-01-15 08:30:00');

COMMIT;

-- Verify all inserts succeeded
SELECT event_type, COUNT(*) AS count
FROM event_log
GROUP BY event_type
ORDER BY count DESC;

The COMMIT at the bottom is also what makes the six inserts arrive together. A reader on another connection sees none of them until it runs, and then sees all six.

Isolation

Isolation is the property that decides what one connection can see of another connection's unfinished work. SQLite's answer is short, and it is worth knowing precisely because it is not the answer most database documentation gives.

SQLite has no isolation levels to choose from. Its documentation states that "all transactions in SQLite show serializable isolation", with a single narrow exception for shared-cache connections that have explicitly turned on a read-uncommitted pragma — see sqlite.org/isolation.html. There is no statement you issue to select a weaker level, because there is no weaker level to select.

Two consequences for what you write here:

Between connections, you get the strongest guarantee by default. A reader sees complete committed transactions from a writer, never a half-finished one.
Within a single connection, there is no isolation at all. SQLite's documentation is explicit that "there is no isolation between operations that occur within the same database connection" — a SELECT sees every change made earlier on that same connection, committed or not. That is exactly what you have been watching all lesson: the verify SELECT inside the transaction reads the uncommitted numbers, which is the only reason the try-and-verify pattern works.

Other engines do offer selectable levels — PostgreSQL, for instance, documents READ COMMITTED as its default with REPEATABLE READ and SERIALIZABLE available (postgresql.org/docs/16/transaction-iso.html). If you carry a transaction pattern from here to a server database, the level is a thing you will have to think about; here it is not.

Recall

No notes, and this reaches back before transactions. In 04-inserting-and-updating you wrote UPDATE statements with a WHERE clause. If an UPDATE's WHERE clause matches no rows at all, what does the database do?

It raises an error, because the statement did not do what it was asked to do.
It succeeds and reports that 0 rows were affected.
It updates every row in the table, because an unmatched WHERE is ignored.

Try It Yourself

Build the try-and-verify pattern for real. The starter below records a shipment of 9 units of B-2 and takes those units off the shelf — but B-2 only holds 4, and nothing in this schema refuses a negative shelf. The starter commits anyway, so it ships stock that does not exist and leaves the shipment on the books. Your job is to add the check that catches it and the word that undoes it.

Build

The starter opens a transaction, records a shipment, decrements the shelf, and commits. Work TODO 1 and TODO 2 so the oversold shipment is undone instead: the shelf must end at its starting figures, A-1 at 12 and B-2 at 4, and the shipments table must end empty.

CREATE TABLE stock (sku TEXT PRIMARY KEY, units INTEGER NOT NULL);
CREATE TABLE shipments (id INTEGER PRIMARY KEY, sku TEXT NOT NULL, units INTEGER NOT NULL);
INSERT INTO stock VALUES ('A-1', 12), ('B-2', 4);
BEGIN;
INSERT INTO shipments VALUES (1, 'B-2', 9);
UPDATE stock SET units = units - 9 WHERE sku = 'B-2';
-- TODO 1: before deciding anything, add a verify SELECT of sku and units
--         from stock for every row whose units have gone below zero.
--         With this data it must report B-2 at -5. Put it here, while the
--         transaction is still open, so it reads the pending numbers.
-- TODO 2: the verify found an oversold shelf, so this transaction must not
--         be kept. Replace COMMIT with the statement that discards
--         everything after BEGIN.
COMMIT;
SELECT sku, units FROM stock ORDER BY sku;
SELECT COUNT(*) AS shipments_recorded FROM shipments;

Expected output:

2 row(s) affected
1 row(s) affected
1 row(s) affected
sku | units
-----------
B-2 | -5
sku | units
-----------
A-1 | 12
B-2 | 4
shipments_recorded
------------------
0

Before you run — what's your plan for the first TODO?

Once it passes, try two variations and predict each before you run it.

Change the shipment to 3 units instead of 9, keeping your ROLLBACK. The verify SELECT now prints an empty result — just the header and its dash rule, because no row is below zero — and the final listing still shows B-2 at 4 with zero shipments recorded. The rollback does not care that there was nothing wrong; it discards a good transaction just as thoroughly as a bad one, which is why the decision has to be conditional in a real application.
Change the shipment to 3 units and put COMMIT back. Now the shelf ends at A-1 12 and B-2 1, and shipments_recorded is 1. Comparing this run with the previous one is the clearest statement of the lesson: identical statements, two different last words, two different databases.

Capstone milestone

Milestone — safe mutation. The warehouse capstone has to move stock and record an order as one change, and its whole credibility rests on never leaving one of those two halves behind. The build task above is that mutation in miniature. Confirm you can write a change you are able to take back.

Hint: You do not need the full warehouse yet — this confirms the mutation shape the capstone's stock movements are built on. There, the same verify-then-decide pair is what keeps an order from being recorded against stock the warehouse does not have.

Bracketed the related writes with BEGIN and a single deliberate ending
Read the pending state with a verify SELECT while the transaction was still open
Ended with ROLLBACK on the failure path rather than assuming the engine would undo it
Checked that a statement affecting 0 rows is a success, not an error
Confirmed the rollback undid every statement after BEGIN, not only the one that looked wrong

Transfer

Two ways to keep a good write and discard a bad one that came after it. The first keeps one transaction open and undoes back to a savepoint; the second commits the good write and puts the bad one in a transaction of its own. Each runs against its own table, both starting at 5000, and each one ends with its ada at 4000. Which statement describes the difference that matters?

CREATE TABLE savepoint_demo (name TEXT PRIMARY KEY, cents INTEGER NOT NULL);
INSERT INTO savepoint_demo VALUES ('ada',5000);
BEGIN;
UPDATE savepoint_demo SET cents = cents - 1000 WHERE name = 'ada';
SAVEPOINT s;
UPDATE savepoint_demo SET cents = cents - 9999 WHERE name = 'ada';
ROLLBACK TO s;
COMMIT;
SELECT name, cents FROM savepoint_demo;

CREATE TABLE two_txn_demo (name TEXT PRIMARY KEY, cents INTEGER NOT NULL);
INSERT INTO two_txn_demo VALUES ('ada',5000);
BEGIN;
UPDATE two_txn_demo SET cents = cents - 1000 WHERE name = 'ada';
COMMIT;
BEGIN;
UPDATE two_txn_demo SET cents = cents - 9999 WHERE name = 'ada';
ROLLBACK;
SELECT name, cents FROM two_txn_demo;

Nothing meaningful differs; a savepoint is just a shorter way of writing two transactions.
The savepoint keeps the earlier write provisional, so a later ROLLBACK of the outer transaction discards it too; committing early makes it permanent and unreachable by any later rollback.
The two-transaction version is safer, because a savepoint does not really undo anything until the transaction commits.

Key Takeaways

A transaction is a bracket you can discard: BEGIN opens it, COMMIT keeps the work, ROLLBACK throws it away
A failing statement inside a transaction rolls back only itself — the rest stays pending and a later COMMIT keeps it
All-or-nothing is something your script asks for with an explicit ROLLBACK, not something the engine does for you
A statement that affects 0 rows has succeeded, so row counts are evidence you have to read yourself
SAVEPOINT creates an undo point inside a transaction; ROLLBACK TO returns to it and leaves the transaction open
Within one connection a SELECT sees uncommitted changes, which is what makes verify-then-decide possible

Pro Tip: In production applications, wrap related write operations in transactions — and put an error check between the writes and the COMMIT. A transaction with no check is a bracket around a wish: it gives you the ability to undo, and then never uses it.

Next Steps

Transactions keep your data consistent when you change it. The next lesson turns back to reading: window functions. Where GROUP BY collapses rows into summaries, window functions perform calculations across related rows while keeping every individual row visible. You will learn ROW_NUMBER, RANK, LAG, LEAD, and running totals — the tools behind leaderboards, time-series comparisons, and percentile rankings.

If you landed here from a search, the rest of the SQL curriculum builds on these foundations. Explore Joins and Relationships for multi-table queries, Window Functions for advanced analytics, or continue below.