MySQL Transactions

Master MySQL transactions — START TRANSACTION, COMMIT, ROLLBACK, ACID properties, isolation levels (READ UNCOMMITTED through SERIALIZABLE), savepoints for partial rollback, the InnoDB-only requirement, the DDL implicit-commit gotcha, and the proper EXIT HANDLER FOR SQLEXCEPTION pattern for transactions inside stored procedures.

A transaction is a group of SQL statements that succeed or fail as a single unit. Either every statement in the transaction is committed to the database, or none of them are — there's no partial state where some changes stick and others don't.

The classic example is a money transfer: debit one account, credit another. If the second statement fails (network error, constraint violation, server crash), the first one must be undone — otherwise money has vanished. Transactions guarantee this.

By default, MySQL runs every statement in autocommit mode — each statement gets wrapped in its own one-statement transaction and committed immediately. To group multiple statements, you either:

• Explicitly START TRANSACTION (autocommit is paused for the duration), or
• Turn autocommit off entirely with SET autocommit = 0;

SET autocommit = 0;     -- or OFF — disable, every change must be COMMITted
SET autocommit = 1;     -- or ON  — re-enable (the default)

SELECT @@autocommit;    -- check the current setting

One of the most surprising things about MySQL transactions: DDL statements force an implicit COMMIT. Running CREATE TABLE, DROP TABLE, ALTER TABLE, TRUNCATE, RENAME, CREATE/DROP INDEX, or any user-management statement (CREATE USER, GRANT, etc.) inside a transaction will:

1. Commit your in-flight transaction immediately — even if you weren't done
2. Run the DDL outside any transaction
3. Cannot be rolled back

CREATE DATABASE IF NOT EXISTS banks;
USE banks;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id        INT PRIMARY KEY,
    username  VARCHAR(255) NOT NULL,
    email     VARCHAR(255)
) ENGINE = InnoDB;

START TRANSACTION;

INSERT INTO users (id, username) VALUES (1, 'karthick');

UPDATE users
SET    email = 'karthick123@example.com'
WHERE  id = 1;

-- Visible only to this session until commit
SELECT * FROM users;

COMMIT;     -- now visible to everyone

Same setup, but ROLLBACK instead of COMMIT — the changes vanish:

START TRANSACTION;

INSERT INTO users (id, username) VALUES (2, 'Dinesh');
UPDATE users SET email = 'dinesh@example.com' WHERE id = 2;

ROLLBACK;     -- discard everything done since START TRANSACTION

SELECT * FROM users;

The Dinesh row was never persisted — the ROLLBACK undid the INSERT and UPDATE. Only the previously-committed Karthick row remains.

Within a transaction, you can mark named savepoints and roll back to any of them — undoing later changes while keeping earlier ones:

START TRANSACTION;

INSERT INTO users (id, username) VALUES (10, 'Alice');

SAVEPOINT after_alice;

INSERT INTO users (id, username) VALUES (11, 'Bob');
INSERT INTO users (id, username) VALUES (12, 'Charlie');

-- Decide we don't want Bob and Charlie after all
ROLLBACK TO SAVEPOINT after_alice;

-- Alice is still there; Bob and Charlie are gone
COMMIT;

Savepoints are useful for nested logic — for instance, "try this risky operation; if it fails, undo just it without losing the rest of the transaction."

Isolation controls how much one transaction can see of others' uncommitted work. MySQL supports the four standard levels:

-- For the next transaction in this session only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- For all transactions in this session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Server-wide default
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

Inside stored procedures, the right pattern uses an EXIT HANDLER to roll back automatically when any error occurs:

DROP TABLE IF EXISTS transactions;
DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    account_id      INT AUTO_INCREMENT PRIMARY KEY,
    account_holder  VARCHAR(255)    NOT NULL,
    balance         DECIMAL(10, 2)  NOT NULL,
    CHECK (balance >= 0)                          -- guard against negatives
) ENGINE = InnoDB;

CREATE TABLE transactions (
    transaction_id    INT AUTO_INCREMENT PRIMARY KEY,
    account_id        INT             NOT NULL,
    amount            DECIMAL(10, 2)  NOT NULL,
    transaction_type  ENUM('DEPOSIT','WITHDRAWAL') NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
) ENGINE = InnoDB;

INSERT INTO accounts (account_holder, balance)
VALUES ('karthick', 10000.00),
       ('Dinesh',    2000.00);

DROP PROCEDURE IF EXISTS transfer;

DELIMITER $$

CREATE PROCEDURE transfer(IN sender_id   INT,
                          IN receiver_id INT,
                          IN amount      DECIMAL(10, 2))
BEGIN
    -- Auto-rollback on any SQL exception, then re-raise
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Check funds first — fail fast before any UPDATE
    IF (SELECT balance FROM accounts WHERE account_id = sender_id) < amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance - amount WHERE account_id = sender_id;
    UPDATE accounts SET balance = balance + amount WHERE account_id = receiver_id;

    INSERT INTO transactions (account_id, amount, transaction_type)
    VALUES (sender_id,   amount, 'WITHDRAWAL'),
           (receiver_id, amount, 'DEPOSIT');

    COMMIT;
END$$

DELIMITER ;

Successful transfer:

CALL transfer(1, 2, 2000);
SELECT * FROM accounts;

Now try a transfer larger than the sender's balance — the SIGNAL fires, the EXIT HANDLER catches it, ROLLBACK runs, and RESIGNAL bubbles the error up to the caller:

CALL transfer(1, 2, 10000);     -- karthick only has 8000 now

Balances unchanged — the rollback worked. The caller gets a clear error message, and the database is exactly as it was before the call.

1. Use InnoDB. MyISAM ignores transactions silently — silent data-loss risk.
2. Don't run DDL inside an explicit transaction — it auto-commits and can't be rolled back.
3. Keep transactions short. Long-running transactions hold locks, block other writers, and bloat the InnoDB undo log. Begin → modify → commit, fast.
4. In stored procedures, use DECLARE EXIT HANDLER FOR SQLEXCEPTION to auto-rollback on any error. RESIGNAL the error so the caller knows.
5. Validate before changing. Check business rules (sufficient funds, valid status, etc.) before the UPDATEs — fail fast before any work is done.
6. Default isolation (REPEATABLE READ) is usually right. Change it only with a specific reason.
7. Use savepoints sparingly. They're useful for nested logic but add complexity; restructuring code to avoid them is often cleaner.

• A transaction is an all-or-nothing group of SQL statements with the four ACID guarantees: Atomicity, Consistency, Isolation, Durability.
• Three statements run them: START TRANSACTION → ... → COMMIT (apply) or ROLLBACK (undo).
• Only InnoDB supports transactions — MyISAM tables silently ignore them.
• DDL auto-commits — CREATE, ALTER, DROP statements implicitly commit any in-flight transaction and can't be rolled back.
• SAVEPOINT / ROLLBACK TO SAVEPOINT let you undo part of a transaction while keeping earlier work.
• Default isolation is REPEATABLE READ; change with SET TRANSACTION ISOLATION LEVEL.
• In stored procedures, the right pattern is DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; — automatic rollback on any error.