ACID: all or nothing

In short: A transaction makes several queries one unit that either fully happens or not at all, and ACID names its four promises: atomicity, consistency, isolation and durability.

Problem

The order that stopped halfway

Teodora orders two loaves of bread and something the program took from an old basket: product 99, which no longer exists. The program makes the order in two queries: first the order itself, then its lines. Run them:

SQLdatabase: shop

INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (61, 1, 2, 1.2),
  (61, 99, 1, 5.0);

INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 1, 2, 1.2), (61, 99, 1, 5.0);

▶ RunRestore the queryFresh database

The second query stops with an error: product 99 does not exist. But the first one has already gone through. The database is left with order 61 and not a single line:

SQLdatabase: shop

SELECT o.id, o.status, COUNT(i.product_id) AS items
FROM orders o LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.id = 61
GROUP BY o.id;

SELECT o.id, o.status, COUNT(i.product_id) AS items FROM orders o LEFT JOIN order_items i ON i.order_id = o.id WHERE o.id = 61 GROUP BY o.id;

▶ RunRestore the queryFresh database

An order for 0 products. The courier will get an empty bag, and the reports will count one order too many. The same happens if the server stops between the two queries.

The idea

A transaction

The action “place an order” is several queries that make sense only together. A transaction makes them one whole:

• BEGIN; starts it.
• COMMIT; confirms it: everything goes in at once.
• ROLLBACK; cancels it: as if nothing had happened.

Here is the whole order: the order, its two lines and the stock, in one transaction:

SQLdatabase: shop

BEGIN;
INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (61, 1, 2, 1.2),
  (61, 19, 1, 2.9);
UPDATE products SET stock = stock - 2 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 19;
COMMIT;
SELECT id, name, stock FROM products WHERE id IN (1, 19);

BEGIN; INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new'); INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (61, 1, 2, 1.2), (61, 19, 1, 2.9); UPDATE products SET stock = stock - 2 WHERE id = 1; UPDATE products SET stock = stock - 1 WHERE id = 19; COMMIT; SELECT id, name, stock FROM products WHERE id IN (1, 19);

▶ RunRestore the queryFresh database

If any query between BEGIN and COMMIT fails, the program says ROLLBACK and the database goes back to how it was. Here is the cancel:

SQLdatabase: shop

BEGIN;
INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new');
ROLLBACK;
SELECT COUNT(*) AS orders FROM orders;

BEGIN; INSERT INTO orders (id, customer_id, ordered_at, status) VALUES (61, 19, '2026-10-25', 'new'); ROLLBACK; SELECT COUNT(*) AS orders FROM orders;

▶ RunRestore the queryFresh database

There are still 60 orders. Order 61 existed only inside the transaction.

Try it

Undo it

A colleague started a transaction and deleted all reviews. Add a line that undoes the delete, before the count.

SQLdatabase: shop

BEGIN;
DELETE FROM reviews;
SELECT COUNT(*) AS reviews FROM reviews;

BEGIN; DELETE FROM reviews; SELECT COUNT(*) AS reviews FROM reviews;

Check▶ Run

The idea

ACID

Databases promise four things about transactions. From the first letters this is called ACID:

• Atomicity. All or nothing. An order without lines cannot be left behind.
• Consistency. After the transaction, all the database's rules hold: keys, CHECK, UNIQUE.
• Isolation. Two transactions running at the same time do not get in each other's way, as if they ran one after the other. How far that goes, we will see in the lesson on concurrent work.
• Durability. After COMMIT the change is on disk. A power cut does not erase it.

More precisely, if you are curious

Every single query is already a small transaction: an INSERT with three rows, one of which breaks a rule, puts in none. That is why in the first field the bread line did not get in either. The trouble starts when one action takes several queries. Then the program writes BEGIN and COMMIT.

Exercise

Yavor's order

Yavor (customer 20) orders 1 pack of coffee beans (product 17, 14.90 €). Create order 61 dated today (2026-10-25) with status new, its line, and reduce the stock, all in one transaction.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. A transaction stopped with an error on the third of five queries and was rolled back. What is left in the database?

The first threeNothing from the fiveThe first two queries

2. Which letter of ACID promises that after COMMIT the order will not vanish in a power cut?

I (isolation)A (atomicity)D (durability)

3. One INSERT with three rows, the third of which breaks a rule. How many rows get in?

ThreeNoneTwo

Check

Start the lesson over

Related lessons

• Builds on: LEFT JOIN: rows without a match too · INSERT: new rows · UPDATE: changing rows · DELETE: deleting rows
• Continues in: BEGIN, COMMIT, ROLLBACK · Concurrent transactions · Triggers · Schema migrations
• Tasks on this topic: Transactions