MySQL Transactions

Transactions group SQL statements into an all-or-nothing unit.

ACID Properties

• Atomicity: all or nothing
• Consistency: data remains valid
• Isolation: concurrent transactions don't interfere
• Durability: committed data persists

Using Transactions

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Check for errors
COMMIT;    -- save changes
-- or
ROLLBACK;  -- undo everything

SAVEPOINT

START TRANSACTION;
INSERT INTO orders (user_id) VALUES (1);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product_id) VALUES (1, 5);
ROLLBACK TO sp1;  -- undo only the item insert
COMMIT;