🔒 Lesson 18: Transactions

Why Transactions?

Many real-world database operations involve multiple steps that must succeed or fail as a unit:

• Bank transfer: Debit Account A and credit Account B — both must happen, or neither
• Order placement: Create the order, add line items, update inventory — all or nothing
• User registration: Create the user account, create their profile, send a welcome email record — if any step fails, clean up

Without transactions, a failure midway through leaves your data in an inconsistent state — some steps completed, others not. Transactions prevent this.

The ACID Properties

Transactions guarantee four properties, known by the acronym ACID:

Basic Transaction Syntax

-- Start a transaction
START TRANSACTION;

-- Do your work (INSERT, UPDATE, DELETE)
INSERT INTO orders (customer_id, order_date) VALUES (1, CURRENT_DATE);
INSERT INTO order_items (order_id, book_id, quantity) VALUES (LAST_INSERT_ID(), 1, 2);

-- If everything worked:
COMMIT;

-- OR if something went wrong:
ROLLBACK;
                

Between START TRANSACTION and COMMIT (or ROLLBACK), your changes are temporary. Other database connections can't see them yet. Only after COMMIT do they become permanent and visible.

START TRANSACTION;   -- Standard SQL syntax
BEGIN;               -- MySQL alias (same thing)
BEGIN WORK;          -- Another alias (same thing)
                    

COMMIT — Make It Permanent

COMMIT tells MySQL: "Everything since START TRANSACTION looks good — save it permanently."

START TRANSACTION;

-- Create a new order for Alice (customer_id = 1)
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2026-04-16');

-- Get the new order's ID
SET @new_order_id = LAST_INSERT_ID();

-- Add items to the order
INSERT INTO order_items (order_id, book_id, quantity)
VALUES (@new_order_id, 1, 1);    -- 1 copy of Dune

INSERT INTO order_items (order_id, book_id, quantity)
VALUES (@new_order_id, 2, 3);    -- 3 copies of 1984

-- Everything succeeded — commit!
COMMIT;
                

After COMMIT, the order and its items are permanently stored. Even if the server crashes immediately after, the data survives (that's durability).

What Happens After COMMIT

• All changes become permanent on disk
• Other connections can now see the new data
• The transaction ends — you're back to normal mode
• You cannot ROLLBACK after COMMIT

ROLLBACK — Undo Everything

ROLLBACK tells MySQL: "Something went wrong — undo all changes since START TRANSACTION."

START TRANSACTION;

-- Accidentally delete all books!
DELETE FROM order_items;
DELETE FROM books;

-- Oh no! Undo everything:
ROLLBACK;

-- Phew — books and order_items are still intact
SELECT COUNT(*) FROM books;  -- Still has all rows
                

ROLLBACK is your safety net. The data is restored to exactly the state it was in before START TRANSACTION.

Practical Example: Transfer with Validation

-- Simulate a "balance transfer" between two accounts
-- accounts table: id, name, balance

START TRANSACTION;

-- Debit the source account
UPDATE accounts SET balance = balance - 500 WHERE id = 1;

-- Check if the balance went negative
SELECT balance INTO @source_balance FROM accounts WHERE id = 1;

-- If negative, abort the whole thing
-- (In the CLI you'd check manually; in a procedure, use IF)
-- For now, let's assume it's fine and credit the destination:
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Verify both accounts look correct, then commit:
COMMIT;
                

Autocommit Mode

By default, MySQL runs in autocommit mode — every single statement is automatically wrapped in its own transaction and committed immediately:

-- With autocommit ON (default):
INSERT INTO books (title, price, author_id) VALUES ('Foundation', 11.99, 4);
-- This is immediately committed — you can't ROLLBACK it

-- Check autocommit status:
SELECT @@autocommit;  -- 1 = ON, 0 = OFF
                

When you write START TRANSACTION, autocommit is temporarily suspended for that transaction. After COMMIT or ROLLBACK, autocommit resumes.

Disabling Autocommit

-- Turn off autocommit for the current session
SET autocommit = 0;

-- Now every statement is part of an implicit transaction
INSERT INTO books (title, price, author_id) VALUES ('Foundation', 11.99, 4);
UPDATE books SET price = 12.99 WHERE title = 'Foundation';

-- Nothing is saved until you explicitly COMMIT:
COMMIT;

-- Or undo everything:
ROLLBACK;

-- Turn autocommit back on:
SET autocommit = 1;
                

Implicit Commits

Certain statements cause an implicit commit — they automatically commit any pending transaction, even if you didn't write COMMIT:

• DDL statements: CREATE TABLE, ALTER TABLE, DROP TABLE
• TRUNCATE TABLE
• CREATE INDEX, DROP INDEX
• CREATE USER, GRANT, REVOKE
• Starting a new START TRANSACTION (commits the previous one)

Savepoints — Partial Rollback

Sometimes you want to undo part of a transaction without losing everything. A savepoint is a named checkpoint you can roll back to.

START TRANSACTION;

-- Step 1: Create the order
INSERT INTO orders (customer_id, order_date) VALUES (1, CURRENT_DATE);
SET @order_id = LAST_INSERT_ID();

SAVEPOINT after_order;

-- Step 2: Add items
INSERT INTO order_items (order_id, book_id, quantity)
VALUES (@order_id, 1, 1);

SAVEPOINT after_items;

-- Step 3: Try something risky
INSERT INTO order_items (order_id, book_id, quantity)
VALUES (@order_id, 999, 1);  -- book_id 999 doesn't exist!

-- Step 3 failed — roll back to after_items, keeping Steps 1 & 2
ROLLBACK TO SAVEPOINT after_items;

-- Steps 1 and 2 are still intact — commit them:
COMMIT;
                

Savepoint Commands

-- Create a savepoint
SAVEPOINT savepoint_name;

-- Roll back to a savepoint (undo changes AFTER the savepoint)
ROLLBACK TO SAVEPOINT savepoint_name;

-- Release (delete) a savepoint without rolling back
RELEASE SAVEPOINT savepoint_name;
                

Transactions in Stored Procedures

Transactions inside stored procedures are where the real power emerges — you can combine multi-step logic with error handling.

Basic Pattern

DELIMITER //

CREATE PROCEDURE place_order(
    IN cust_id INT,
    IN book_id_param INT,
    IN qty INT,
    OUT result_message VARCHAR(100)
)
BEGIN
    DECLARE order_id INT;

    -- Use a handler for errors
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result_message = 'Error: Order failed — rolled back';
    END;

    START TRANSACTION;

    -- Create the order
    INSERT INTO orders (customer_id, order_date)
    VALUES (cust_id, CURRENT_DATE);

    SET order_id = LAST_INSERT_ID();

    -- Add the item
    INSERT INTO order_items (order_id, book_id, quantity)
    VALUES (order_id, book_id_param, qty);

    -- If we got here, everything worked
    COMMIT;
    SET result_message = CONCAT('Success: Order #', order_id, ' created');

END //

DELIMITER ;

-- Test it:
CALL place_order(1, 1, 2, @msg);
SELECT @msg;
-- 'Success: Order #4 created'
                

Transfer Between Accounts

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE source_balance DECIMAL(10,2);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = 'Error: Transfer failed';
    END;

    START TRANSACTION;

    -- Check source balance
    SELECT balance INTO source_balance
    FROM accounts
    WHERE id = from_account
    FOR UPDATE;  -- Lock the row to prevent concurrent modification

    IF source_balance < amount THEN
        ROLLBACK;
        SET result = 'Error: Insufficient funds';
    ELSE
        -- Debit source
        UPDATE accounts SET balance = balance - amount
        WHERE id = from_account;

        -- Credit destination
        UPDATE accounts SET balance = balance + amount
        WHERE id = to_account;

        COMMIT;
        SET result = CONCAT('Success: Transferred $', amount);
    END IF;

END //

DELIMITER ;
                

Common Transaction Patterns

1. The Try-Commit-Catch-Rollback Pattern

The most common pattern — attempt the work, commit on success, rollback on failure:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    -- Log the error, set a message, etc.
END;

START TRANSACTION;
    -- Multiple operations...
COMMIT;
                

2. Batch Insert with Transaction

Wrap bulk inserts in a transaction — much faster than individual autocommitted inserts:

START TRANSACTION;

INSERT INTO books (title, price, author_id) VALUES ('Book 1', 9.99, 1);
INSERT INTO books (title, price, author_id) VALUES ('Book 2', 12.99, 2);
INSERT INTO books (title, price, author_id) VALUES ('Book 3', 7.99, 3);
-- ... hundreds more ...

COMMIT;
                

3. Conditional Commit

START TRANSACTION;

DELETE FROM order_items WHERE order_id = 5;
DELETE FROM orders WHERE id = 5;

-- Check if we actually deleted anything meaningful:
-- (In a procedure, use ROW_COUNT() or a variable)
-- If something looks wrong:
--   ROLLBACK;
-- If everything is fine:
COMMIT;
                

4. Read-Only Transaction

You can start a transaction in read-only mode — MySQL can optimize it since it knows no writes will happen:

START TRANSACTION READ ONLY;

SELECT * FROM customers;
SELECT * FROM orders WHERE customer_id = 1;

COMMIT;  -- Nothing changed, but ends the transaction
                

Pitfalls & Best Practices

1. Keep Transactions Short

Long transactions hold locks on rows, blocking other users. Do your prep work (validation, calculations) before starting the transaction, then keep the transaction itself as brief as possible.

-- ❌ BAD: Long transaction with unnecessary work inside
START TRANSACTION;
    -- 5 seconds of complex calculations...
    -- Other users are waiting for locked rows
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- ✅ GOOD: Do prep work first, then a quick transaction
-- Calculate everything first...
START TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
                

2. Always End Your Transactions

A transaction that's never committed or rolled back holds locks indefinitely. Always ensure every START TRANSACTION has a matching COMMIT or ROLLBACK — even in error cases.

3. Don't Mix DDL and DML in a Transaction

-- ❌ BAD: DDL implicitly commits everything before it
START TRANSACTION;
    INSERT INTO orders (customer_id, order_date) VALUES (1, CURRENT_DATE);
    CREATE TABLE temp_log (msg TEXT);  -- IMPLICIT COMMIT happens here!
    -- The INSERT is now committed — you can't roll it back
    ROLLBACK;  -- Only rolls back changes AFTER the implicit commit
                

4. Handle Deadlocks

When two transactions each hold a lock that the other needs, MySQL detects a deadlock and automatically rolls back one of them. Your application should catch this error and retry:

-- In application code (pseudocode):
-- retry_count = 0
-- while retry_count < 3:
--     try:
--         START TRANSACTION
--         ... do work ...
--         COMMIT
--         break
--     catch deadlock_error:
--         retry_count += 1
--         wait briefly, then retry
                

5. Use Consistent Lock Order

To minimize deadlocks, always access tables (and rows) in the same order across all transactions. If Transaction A locks customers then orders, and Transaction B locks orders then customers, they can deadlock. Both locking customers first prevents this.

Exercises

-- 1
START TRANSACTION;
INSERT INTO authors (name) VALUES ('Ursula K. Le Guin');
SET @author_id = LAST_INSERT_ID();
INSERT INTO books (title, price, author_id)
VALUES ('The Left Hand of Darkness', 10.99, @author_id);
COMMIT;

-- 2
START TRANSACTION;
DELETE FROM order_items;
SELECT COUNT(*) FROM order_items;  -- 0 rows (temporarily)
ROLLBACK;
SELECT COUNT(*) FROM order_items;  -- All rows restored!

-- 3
START TRANSACTION;
INSERT INTO customers (first_name, last_name)
VALUES ('Dave', 'Wilson');
INSERT INTO customers (first_name, last_name)
VALUES ('Eve', 'Brown');
COMMIT;
SELECT * FROM customers WHERE first_name IN ('Dave', 'Eve');
                        

START TRANSACTION;

-- 1. Insert order
INSERT INTO orders (customer_id, order_date) VALUES (2, CURRENT_DATE);
SET @oid = LAST_INSERT_ID();

-- 2. Savepoint
SAVEPOINT order_created;

-- 3. Insert valid item
INSERT INTO order_items (order_id, book_id, quantity) VALUES (@oid, 1, 1);

-- 4. Savepoint
SAVEPOINT item_added;

-- 5. Try invalid item (might cause FK error)
-- INSERT INTO order_items (order_id, book_id, quantity) VALUES (@oid, 999, 1);

-- 6. Roll back to item_added
ROLLBACK TO SAVEPOINT item_added;

-- 7. Commit (order + first item are saved)
COMMIT;
                        

DELIMITER //

CREATE PROCEDURE safe_delete_order(
    IN order_id_param INT,
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = 'Error: Delete failed — rolled back';
    END;

    START TRANSACTION;

    -- Delete items first (child rows)
    DELETE FROM order_items WHERE order_id = order_id_param;

    -- Then delete the order (parent row)
    DELETE FROM orders WHERE id = order_id_param;

    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SET result = CONCAT('Error: Order #', order_id_param, ' not found');
    ELSE
        COMMIT;
        SET result = CONCAT('Success: Order #', order_id_param, ' deleted');
    END IF;
END //

DELIMITER ;

-- Test:
CALL safe_delete_order(3, @msg);
SELECT @msg;
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — COMMIT, ROLLBACK, and SAVEPOINT
• MySQL Docs — InnoDB Transaction Model
• MySQL Docs — Autocommit, Commit, and Rollback
• MySQL Docs — Deadlocks in InnoDB

🚀 What's Next?

You can now protect data integrity with transactions. But who gets to run those transactions? In Lesson 19: User Management & Permissions, you'll learn to create database users, control what they can access with GRANT and REVOKE, and apply the principle of least privilege to keep your database secure.