How to Use Transactions in MySQL

Key Insights

Transactions ensure data integrity through ACID properties, preventing partial updates that could corrupt your database during multi-step operations like financial transfers or inventory management.
MySQL defaults to autocommit mode where each statement is its own transaction—you must explicitly disable this with START TRANSACTION or set autocommit=0 for manual transaction control.
Choose isolation levels based on your consistency vs. performance needs: REPEATABLE READ (MySQL’s default) prevents most anomalies, while READ COMMITTED offers better concurrency for high-traffic applications.

Introduction to Transactions and ACID Properties

A transaction is a sequence of one or more SQL operations treated as a single unit of work. Either all operations succeed and get permanently saved, or they all fail and the database remains unchanged. This all-or-nothing behavior is critical for maintaining data integrity in production systems.

Transactions follow ACID properties:

Atomicity: All operations complete successfully or none do. No partial updates.
Consistency: Data moves from one valid state to another, respecting all constraints and rules.
Isolation: Concurrent transactions don’t interfere with each other.
Durability: Once committed, changes survive system crashes and power failures.

Consider what happens without transactions:

-- WITHOUT TRANSACTION - DANGEROUS
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- System crashes here!
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

If the system crashes between these statements, $100 disappears from the first account but never arrives at the second. With transactions, this can’t happen:

-- WITH TRANSACTION - SAFE
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

Now both updates happen together or neither happens at all.

Basic Transaction Syntax

MySQL provides three fundamental transaction commands:

-- Start a transaction
START TRANSACTION;

-- Your SQL operations here
UPDATE products SET stock = stock - 1 WHERE product_id = 42;
INSERT INTO orders (product_id, quantity) VALUES (42, 1);

-- Make changes permanent
COMMIT;

If something goes wrong, roll back all changes:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Check if account went negative
SELECT balance INTO @bal FROM accounts WHERE account_id = 1;

IF @bal < 0 THEN
    ROLLBACK;  -- Undo everything
ELSE
    COMMIT;    -- Save changes
END IF;

You can use BEGIN as a synonym for START TRANSACTION:

BEGIN;
DELETE FROM temp_data WHERE created_at < NOW() - INTERVAL 1 DAY;
COMMIT;

Both commands work identically, though START TRANSACTION is more explicit and the SQL standard.

Practical Use Case: Bank Transfer

Here’s a complete bank transfer implementation that demonstrates why transactions are essential:

DELIMITER $$

CREATE PROCEDURE transfer_funds(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE from_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Lock the source account and check balance
    SELECT balance INTO from_balance 
    FROM accounts 
    WHERE account_id = from_account 
    FOR UPDATE;
    
    IF from_balance < amount THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;
    
    -- Debit source account
    UPDATE accounts 
    SET balance = balance - amount,
        updated_at = NOW()
    WHERE account_id = from_account;
    
    -- Credit destination account
    UPDATE accounts 
    SET balance = balance + amount,
        updated_at = NOW()
    WHERE account_id = to_account;
    
    -- Log the transfer
    INSERT INTO transfer_log (from_account, to_account, amount, timestamp)
    VALUES (from_account, to_account, amount, NOW());
    
    COMMIT;
END$$

DELIMITER ;

The EXIT HANDLER FOR SQLEXCEPTION automatically rolls back if any error occurs. The FOR UPDATE clause locks the row, preventing other transactions from modifying it until we commit or rollback.

Transaction Isolation Levels

Isolation levels control how transactions see changes made by other concurrent transactions. MySQL supports four levels:

-- Set isolation level for next transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQL default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set for entire session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Check current level
SELECT @@transaction_isolation;

READ UNCOMMITTED allows dirty reads—you can see uncommitted changes from other transactions:

-- Session 1
START TRANSACTION;
UPDATE products SET price = 99.99 WHERE id = 1;
-- Not committed yet

-- Session 2 (READ UNCOMMITTED)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT price FROM products WHERE id = 1;  -- Sees 99.99 even though not committed!

READ COMMITTED prevents dirty reads but allows non-repeatable reads:

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT price FROM products WHERE id = 1;  -- Returns 49.99

-- Session 2 commits a change here

SELECT price FROM products WHERE id = 1;  -- Now returns 99.99 (different!)

REPEATABLE READ (MySQL’s default) ensures consistent reads within a transaction:

START TRANSACTION;
SELECT price FROM products WHERE id = 1;  -- Returns 49.99
-- Other transactions can update this row
SELECT price FROM products WHERE id = 1;  -- Still returns 49.99
COMMIT;

SERIALIZABLE provides the strictest isolation by locking all read rows, preventing phantom reads but reducing concurrency.

For most applications, stick with REPEATABLE READ. Use READ COMMITTED only if you need higher concurrency and can handle non-repeatable reads.

Savepoints and Nested Transactions

Savepoints let you create rollback points within a transaction:

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (123, 0);
SET @order_id = LAST_INSERT_ID();

SAVEPOINT items_added;

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 1, 2, 29.99);

INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 2, 1, 49.99);

-- Oops, second item is out of stock
ROLLBACK TO SAVEPOINT items_added;

-- Add different item instead
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 3, 1, 39.99);

UPDATE orders SET total = 69.98 WHERE id = @order_id;

COMMIT;

This is invaluable for complex operations where you want to undo part of a transaction without starting over.

Common Pitfalls and Best Practices

Autocommit Mode: MySQL defaults to autocommit=1, making each statement its own transaction:

-- Check autocommit status
SELECT @@autocommit;

-- Disable for manual transaction control
SET autocommit = 0;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- Re-enable
SET autocommit = 1;

Implicit Commits: DDL statements (CREATE, ALTER, DROP) automatically commit the current transaction:

START TRANSACTION;
INSERT INTO users (name) VALUES ('Alice');
CREATE TABLE temp_table (id INT);  -- Implicitly commits the INSERT!
ROLLBACK;  -- Too late, INSERT is already committed

Deadlocks: Occur when transactions wait for each other’s locks:

-- Session 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Waiting to update account 2

-- Session 2
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;  -- DEADLOCK!

MySQL automatically detects deadlocks and rolls back one transaction. Always access resources in the same order across all transactions to prevent deadlocks.

Transaction Size: Keep transactions small and fast:

-- BAD: Long-running transaction
START TRANSACTION;
SELECT SLEEP(30);  -- Holds locks for 30 seconds
UPDATE accounts SET balance = balance + 1 WHERE account_id = 1;
COMMIT;

-- GOOD: Quick transaction
START TRANSACTION;
UPDATE accounts SET balance = balance + 1 WHERE account_id = 1;
COMMIT;

Long transactions increase lock contention and deadlock probability.

Conclusion

Transactions are non-negotiable for data integrity in any serious application. Use them for all multi-step operations where partial completion would corrupt your data. Start with MySQL’s default REPEATABLE READ isolation level and only adjust if you have specific concurrency requirements. Remember to handle errors properly with rollback logic, keep transactions short, and always access resources in consistent order to avoid deadlocks. Master these fundamentals and you’ll prevent an entire class of data corruption bugs that plague poorly designed systems.