SQL Transactions

Group changes into all-or-nothing transactions with BEGIN, COMMIT and ROLLBACK, and understand ACID.

Lesson 22 of 102Advanced17 min read

Some changes only make sense together. When a customer places an order, the shop must add the order and reduce the product's stock. When money moves between bank accounts, one balance goes down and another goes up. If the database crashed halfway, or one of the steps failed, you'd be left with stock that doesn't match the orders, or money that vanished.

A transaction groups several statements into a single unit of work: either all of them take effect, or none of them do. If anything goes wrong before the end, the database rolls everything back to how it was at the start, as if nothing had happened.

Transactions are what make databases trustworthy enough for banks, airlines and online shops. In this lesson you'll start, finish and cancel transactions, use savepoints, learn the four ACID guarantees, and see what happens when two people change the same data at the same moment.

BEGIN, COMMIT and ROLLBACK

SQL
BEGIN;
UPDATE products SET stock = stock - 2 WHERE id = 4;
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 4, 2, '2024-07-15');
COMMIT;
  • BEGIN (or START TRANSACTION in MySQL) starts a transaction.
  • The statements inside run as normal, but their changes are provisional.
  • COMMIT makes every change permanent and visible to everyone.
  • ROLLBACK cancels every change since BEGIN.

If the connection is lost or the server crashes before COMMIT, the database rolls the transaction back automatically when it restarts.

Autocommit

Outside an explicit transaction, most databases are in autocommit mode: each statement is its own tiny transaction and is committed as soon as it succeeds. That's why the UPDATE and DELETE statements in earlier lessons took effect immediately.

As soon as you write BEGIN, autocommit pauses until you COMMIT or ROLLBACK. This makes transactions a useful safety net when you're changing data by hand: start with BEGIN, run your UPDATE, check the result with a SELECT, and only then decide whether to commit or roll back.

ACID

Transactions in relational databases promise four properties, known as ACID:

PropertyMeaning
AtomicityAll of the transaction happens, or none of it does
ConsistencyThe data moves from one valid state to another; constraints are never left broken
IsolationTransactions running at the same time don't see each other's unfinished changes
DurabilityOnce committed, changes survive crashes and power cuts

Together they mean you can reason about each transaction as if it ran alone, from start to finish, on a computer that never fails.

Savepoints and concurrency

A savepoint marks a point inside a transaction that you can roll back to without abandoning everything:

SQL
SAVEPOINT before_discount;
UPDATE products SET price = price * 0.5;
ROLLBACK TO before_discount;   -- undo just the discount

When two transactions try to change the same row at once, the database makes one wait until the other finishes. Databases offer different isolation levels, from READ COMMITTED (the default in PostgreSQL) to SERIALIZABLE, which trade a little speed for stronger guarantees. For most applications the default is right; what matters is putting related changes in one transaction.

SQL Transactions Syntax

Transaction syntax

BEGIN;                         -- START TRANSACTION in MySQL
  statement1;
  statement2;
COMMIT;                        -- or ROLLBACK;

SAVEPOINT name;
ROLLBACK TO name;
RELEASE SAVEPOINT name;

SQL Transactions Summary

  • A transaction is an all-or-nothing group of statements.
  • BEGIN starts it, COMMIT saves it and ROLLBACK cancels it.
  • Without BEGIN, autocommit saves each statement immediately.
  • ACID: atomicity, consistency, isolation and durability.
  • Savepoints allow partial rollbacks; keep transactions short to avoid blocking others.

SQL Transactions Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Placing an order safely

SQL
BEGIN;

UPDATE products SET stock = stock - 2 WHERE id = 4 AND stock >= 2;

INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 4, 2, '2024-07-15');

COMMIT;

SELECT name, stock FROM products WHERE id = 4;
Output
name         | stock
-------------+------
Water bottle | 38
Line-by-line explanation
  1. Line 3Only reduce the stock if there's enough. The extra condition prevents negative stock.
  2. Lines 5 to 6The order is recorded in the same transaction.
  3. Line 8Both changes become permanent together.
Example 2

Changing your mind with ROLLBACK

SQL
BEGIN;
DELETE FROM orders WHERE customer_id = 2;
SELECT COUNT(*) AS orders_left FROM orders;
ROLLBACK;
SELECT COUNT(*) AS orders_after_rollback FROM orders;
Output
orders_left
-----------
4

orders_after_rollback
---------------------
6
Line-by-line explanation
  1. Line 2Ben's two orders are deleted inside the transaction.
  2. Line 3Inside the transaction, they appear gone.
  3. Line 4ROLLBACK cancels the delete.
  4. Line 5All six orders are back, as if nothing happened.
Example 3

A bank transfer

SQL
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT,
    balance DECIMAL(10, 2) CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Sam', 100), (2, 'Lea', 20);

BEGIN;
UPDATE accounts SET balance = balance - 150 WHERE id = 1;
-- Error: CHECK constraint failed: balance >= 0
ROLLBACK;

SELECT owner, balance FROM accounts;
Output
owner | balance
------+--------
Sam   | 100
Lea   | 20
Line-by-line explanation
  1. Line 4The constraint forbids negative balances.
  2. Line 9Sam doesn't have 150, so the first step fails.
  3. Line 11The application rolls back, so Lea isn't credited money that never left Sam's account.

Common SQL Transactions Mistakes

Forgetting to COMMIT

Don't
BEGIN;
UPDATE products SET price = price * 1.1;
-- connection closed: the change is lost
Do
BEGIN;
UPDATE products SET price = price * 1.1;
COMMIT;

Related changes in separate statements

Don't
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- crash here: money has left account 1 but never arrives
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
Do
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

Long-running transactions

Don't
BEGIN;
SELECT * FROM products WHERE id = 3;
-- wait for the user to fill in a form for ten minutes...
UPDATE products SET stock = stock - 1 WHERE id = 3;
COMMIT;
Do
-- collect the form first, then:
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 3 AND stock > 0;
COMMIT;

SQL Transactions in Real Projects

Transactions behind the scenes

Every card payment, airline booking and online checkout runs inside a transaction. Web frameworks wrap each request's database work in one, and if any step throws an error, everything is rolled back automatically. Database administrators also wrap risky manual fixes in BEGIN ... ROLLBACK to preview the effect before committing for real.

SQL Transactions FAQ

What isolation level do databases use by default?

PostgreSQL and SQL Server default to Read Committed, MySQL's InnoDB to Repeatable Read, and SQLite runs transactions as Serializable. The level decides which changes from other transactions you can see while yours is running, as the ACID and isolation lesson explains.

Can CREATE TABLE and ALTER TABLE be rolled back?

In PostgreSQL and SQLite, yes: schema changes take part in transactions. In MySQL, most DDL statements commit the current transaction automatically, so they can't be rolled back. Plan schema changes as separate, carefully tested steps on MySQL.

What is a deadlock?

Two transactions each hold a lock the other needs, so neither can continue. The database detects this, cancels one of them with an error and lets the other finish. Keep transactions short, update rows in a consistent order, and be ready to retry a transaction that was chosen as the deadlock victim.

Do SELECT statements need a transaction?

A single SELECT runs in its own automatic transaction. Group several reads in one transaction, with a suitable isolation level, when they must all see the same consistent snapshot, for example a report that reads totals from several tables at the same moment.

What happens to an open transaction if the connection drops?

The database rolls it back. Changes are only permanent once COMMIT succeeds, so a crash or lost network connection in the middle of a transaction leaves the data as it was before it started. That's the atomicity part of ACID.

SQL Transactions Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the transaction so both changes are saved.

;
UPDATE products SET stock = stock - 1 WHERE id = 2;
INSERT INTO orders (customer_id, product_id, order_date) VALUES (3, 2, '2024-07-20');
;
Multiple choiceeasy

What does ROLLBACK do?

Predict the outputmedium

What is Sam's balance after this?

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE owner = 'Sam';  -- was 100
ROLLBACK;
Multiple choicemedium

Which ACID property means committed data survives a power cut?

Multiple choicehard

Why shouldn't a transaction stay open while waiting for user input?

SQL Transactions Quiz

5 questions to check what you learned.

  1. What is a transaction?

    • A single SELECT statement
    • A backup of the database
    • A group of statements that succeed or fail together
    • A type of index

  2. What does autocommit mean?

    • Changes are never saved
    • Transactions commit every hour
    • COMMIT is written for you at the end of a script
    • Each statement is committed automatically as soon as it succeeds

  3. What does the A in ACID stand for?

    • Accuracy
    • Atomicity
    • Availability
    • Access

  4. What does ROLLBACK TO before_change do?

    • Undoes changes made after the savepoint named before_change
    • Cancels the whole transaction
    • Creates a savepoint
    • Commits up to the savepoint

  5. What happens to an uncommitted transaction if the database server crashes?

    • It's committed when the server restarts
    • Half of it is saved
    • The database is corrupted
    • It's rolled back automatically

Related SQL topics

08INSERT, UPDATE, DELETEAdd new rows, change existing data and remove rows safely, without losing anything by accident.11Constraints & KeysProtect your data with NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY and FOREIGN KEY constraints.21Indexes & PerformanceSpeed up searches, joins and sorting with indexes, and check how a query runs with EXPLAIN.
Esc
↑↓ to navigateEnter to open