SQL Transactions

ACID transactions

A transaction groups statements so they succeed or fail together. Moving money must debit one account and credit another — never just one of them.

• Atomicity — all or nothing.
• Consistency — constraints hold before and after.
• Isolation — concurrent transactions don't see each other's half-done work.
• Durability — once committed, it survives a crash.

BEGIN, COMMIT, ROLLBACK

BEGIN;
UPDATE products SET stock = stock - 2 WHERE product_id = 6;
INSERT INTO orders (customer_id, order_date, status) VALUES (5, '2026-10-12', 'pending');
ROLLBACK;  -- changed our mind: undo both statements
SELECT stock FROM products WHERE product_id = 6;
SELECT COUNT(*) AS orders FROM orders;

BEGIN;
UPDATE products SET stock = stock - 2 WHERE product_id = 6;
COMMIT;
SELECT stock FROM products WHERE product_id = 6;

stock
8

orders
12

stock
6

Isolation levels

Databases offer isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) that trade safety for concurrency. Learn more in the databases and transactions interview questions.