Transactions & ACID Properties
Group multiple operations into one safe unit — so either everything succeeds or nothing changes at all.
A transaction is all-or-nothing. properties guarantee your data stays consistent even when things go wrong.
EXERCISE
1Transactions — All or Nothing Operations
A transaction groups multiple SQL statements into a single unit. Either all statements succeed and the changes are saved, or any failure rolls everything back to the starting state.
The classic example — bank transfer:
-- Without a transaction: if step 2 fails, money disappears!
UPDATE accounts SET balance = balance - 5000 WHERE id = 1; -- debit Ada
UPDATE accounts SET balance = balance + 5000 WHERE id = 2; -- credit Grace
-- If the server crashes between these two lines → Ada lost 5000, Grace got nothing
-- With a transaction — both succeed or neither does:
START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
-- Check if everything looks correct:
SELECT id, balance FROM accounts WHERE id IN (1, 2);
COMMIT; -- save both changes permanently
-- OR:
-- ROLLBACK; -- undo both changes if something went wrong
Transaction commands:
START TRANSACTION; -- begin a transaction (also: BEGIN)
COMMIT; -- save all changes permanently
ROLLBACK; -- undo all changes back to START TRANSACTION
SAVEPOINT sp1; -- create a named save point within the transaction
ROLLBACK TO sp1; -- undo back to the savepoint (not all the way back)
RELEASE SAVEPOINT sp1; -- remove the savepoint
Savepoints — partial rollback:
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT after_order;
INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 99);
-- Oops — product 99 does not exist, foreign key error
ROLLBACK TO after_order; -- undo the order_item insert only
-- the order itself is still in the transaction
COMMIT; -- save the order (without the bad item)
Pro Tip: Any operation that modifies data across multiple tables should be wrapped in a transaction. Without one, a partial failure leaves your database in an inconsistent state that can be very hard to repair.
EXERCISE
2ACID Properties — The Four Guarantees of a Reliable Database
ACID is the contract that a database makes with you: your transactions will be Atomic, Consistent, Isolated, and Durable — no matter what.
A — Atomicity: all or nothing
START TRANSACTION;
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 10;
INSERT INTO order_items (order_id, product_id, qty) VALUES (55, 10, 1);
COMMIT;
-- Either BOTH changes happen, or NEITHER does.
-- Atomicity: the transaction is indivisible — an atom.
C — Consistency: rules are always enforced
-- Foreign key constraint = consistency rule:
INSERT INTO order_items (order_id, product_id)
VALUES (999, 1); -- order_id 999 does not exist
-- ❌ Error: foreign key violation
-- The database refuses the change to stay in a consistent state
I — Isolation: transactions do not interfere with each other
-- Session A:
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 5;
-- Not yet committed — other sessions cannot see this change yet
-- Session B (simultaneously):
SELECT stock FROM products WHERE id = 5;
-- Sees the original stock value (not Session A's uncommitted change)
-- This is isolation: transactions are invisible until committed
D — Durability: committed changes survive crashes
COMMIT;
-- After this line, the changes are written to disk (the transaction log)
-- Even if the server crashes one millisecond later, the data is safe
-- The database will recover and the committed data will be there
ACID summary table:
| Property | Guarantee |
|---|---|
| Atomicity | Transaction is all-or-nothing |
| Consistency | Data rules (constraints) are always enforced |
| Isolation | Concurrent transactions do not interfere |
| Durability | Committed data survives system failures |
Pro Tip: ACID is not optional — it is the minimum expectation for any database handling real money, medical records, or user data. If you are building anything important, understand which of these guarantees your database provides.
EXERCISE
3Putting It All Together — A Complete Real-World Example
Every concept in this course — queries, joins, aggregation, window functions, transactions — comes together in a single realistic scenario.
Scenario: Process an e-commerce order. Update stock, record the sale, apply a discount, and log the transaction — all safely.
START TRANSACTION;
-- Step 1: Check stock is available
SELECT stock INTO @current_stock
FROM products WHERE id = 42 FOR UPDATE;
-- FOR UPDATE locks the row so no other transaction can change stock simultaneously
IF @current_stock < 1 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Out of stock';
END IF;
-- Step 2: Deduct stock
UPDATE products
SET stock = stock - 1,
updated_at = NOW()
WHERE id = 42;
-- Step 3: Record the order
INSERT INTO orders (customer_id, status, created_at)
VALUES (101, 'confirmed', NOW());
SET @new_order_id = LAST_INSERT_ID();
-- Step 4: Record the order item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT @new_order_id, 42, 1, price FROM products WHERE id = 42;
-- Step 5: Apply loyalty discount if customer has 10+ orders
UPDATE orders
SET discount_pct = 10
WHERE id = @new_order_id
AND (SELECT COUNT(*) FROM orders WHERE customer_id = 101) >= 10;
COMMIT;
-- All 4 changes saved atomically — or none if any step failed
Query the result with a CTE and window function:
WITH order_summary AS (
SELECT
c.name AS customer,
COUNT(o.id) AS total_orders,
SUM(oi.unit_price * oi.quantity) AS lifetime_value,
RANK() OVER (ORDER BY SUM(oi.unit_price * oi.quantity) DESC) AS value_rank
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY c.id, c.name
)
SELECT * FROM order_summary WHERE value_rank <= 10;
Final Key Insight: Real SQL is not about knowing individual commands in isolation — it is about combining SELECT, JOIN, GROUP BY, window functions, CTEs, and transactions to solve complete business problems. You now have every tool you need.