MySQL — Transactions

Basic Transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ROLLBACK

START TRANSACTION;
DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK; -- Undo the delete

SAVEPOINT

START TRANSACTION;
INSERT INTO orders (id, total) VALUES (1, 100);
SAVEPOINT sp1;
INSERT INTO order_items (order_id, product) VALUES (1, 'Widget');
ROLLBACK TO sp1; -- Only undo the order_items insert
COMMIT; -- Only the orders insert is saved

Transaction Properties (ACID)

Property	Description
Atomicity	All operations succeed or none do
Consistency	Data remains valid after transaction
Isolation	Concurrent transactions don't interfere
Durability	Committed data survives crashes

Auto-Commit

MySQL auto-commits each statement by default. Disable it:

SET autocommit = 0;
-- Statements are not committed until COMMIT
COMMIT;
SET autocommit = 1;

Transaction with Error Handling

DELIMITER //
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred, rolled back' AS result;
    END;

    START TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    COMMIT;
    SELECT 'Transfer successful' AS result;
END //
DELIMITER ;

Mini Practice

Write SQL code that:

1. Starts a transaction and commits it
2. Starts a transaction and rolls it back
3. Uses SAVEPOINT and ROLLBACK TO
4. Disables auto-commit

Up Next

Continue with Prepared Statements — efficient parameterized queries.