Transactions

A transaction groups several statements into one unit of work: either all of them take effect, or none do. When a customer places an order, the order row, its items and the stock changes must all be saved together — never half of them. Transactions are what make databases trustworthy.

BEGIN, COMMIT and ROLLBACK

BEGIN;

INSERT INTO orders (id, customer_id, order_date, status) VALUES (16, 5, '2026-03-12', 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (16, 8, 1, 34.00);
UPDATE products SET stock = stock - 1 WHERE id = 8;

COMMIT;

SELECT o.id, oi.product_id, p.stock
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.id = 16;

BEGIN (or START TRANSACTION) starts a transaction; COMMIT makes all its changes permanent and visible to others.

Rolling back

BEGIN;
DELETE FROM order_items;
SELECT COUNT(*) AS during_transaction FROM order_items;
ROLLBACK;

SELECT COUNT(*) AS after_rollback FROM order_items;

ROLLBACK undoes everything since BEGIN. Inside the transaction the table looked empty, but after rolling back all 23 rows are back. Wrapping risky changes in a transaction gives you an undo button.

ACID

Autocommit

Outside an explicit transaction, most databases run in autocommit mode: each statement is its own transaction, committed immediately. That's why a mistaken DELETE can't be undone unless you started a transaction first. (Oracle is an exception: changes wait for an explicit COMMIT by default.)

Errors inside a transaction

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 3;
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (999, 3, 1, 45.00);
COMMIT;

Error: FOREIGN KEY constraint failed

Order 999 doesn't exist, so the insert fails a foreign-key check. Your application should then issue ROLLBACK, undoing the stock change too, so stock and orders stay consistent. (PostgreSQL goes further: after an error, it refuses all further statements in that transaction until you roll back.)

Savepoints

BEGIN;
UPDATE products SET price = 10 WHERE id = 7;
SAVEPOINT before_risky_part;
UPDATE products SET price = 0 WHERE id = 8;
ROLLBACK TO before_risky_part;
COMMIT;

SELECT id, name, price FROM products WHERE id IN (7, 8);

A savepoint marks a point inside a transaction that you can roll back to without abandoning the whole transaction.

Isolation levels

When transactions run at the same time, the database must decide how much they can see of each other. The SQL standard defines four isolation levels, from fastest and least protected to slowest and safest:

Higher isolation means more locking or more transactions forced to retry. The defaults are a sensible balance; raise the level for critical logic such as moving money between accounts. SQLite is serializable, because it allows only one writer at a time.

Keep transactions short

While a transaction is open, it may hold locks that block other users. Never keep one open while waiting for a person to click something. Do the work, commit, and move on.

Try it yourself

Transfer 5 units of stock from product 5 to product 12 in a single transaction, then try it with a rollback.