MySQL Transactions

A transaction in MySQL is a group of one or more SQL statements that are executed together as a single unit of work. Either all statements in the transaction succeed and are permanently saved, or none of them take effect. This guarantees data consistency even when errors occur during execution.

Why Transactions Matter

Consider a bank transfer: money is deducted from Account A and added to Account B. If the deduction succeeds but the addition fails due to an error, the money simply disappears. A transaction ensures both operations succeed together — or neither takes effect at all.

ACID Properties

Transactions follow four core principles known as ACID:

Transaction Control Statements

MySQL Storage Engine Requirement

Transactions work only with storage engines that support them. InnoDB (the default MySQL engine) fully supports transactions. MyISAM does not support transactions.

AUTO-COMMIT Mode

By default, MySQL runs in auto-commit mode — every individual SQL statement is treated as its own transaction and committed immediately. To group multiple statements into one transaction, auto-commit must be disabled or START TRANSACTION must be used.

-- Check auto-commit status
SELECT @@autocommit;

-- Disable auto-commit
SET autocommit = 0;

-- Re-enable auto-commit
SET autocommit = 1;

Sample Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100),
    balance DECIMAL(10,2)
);

INSERT INTO accounts VALUES (1, 'Ramesh Gupta', 50000.00);
INSERT INTO accounts VALUES (2, 'Sunita Rao', 30000.00);

Example: Successful Transaction

START TRANSACTION;

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

COMMIT;

Both updates succeed. COMMIT saves them permanently. Ramesh now has 40000 and Sunita has 40000.

Example: Failed Transaction with ROLLBACK

START TRANSACTION;

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

-- Suppose an error occurs here (e.g., constraint violation)
-- Issue a ROLLBACK to undo everything

ROLLBACK;

The deduction is reversed. Ramesh's balance returns to 40000 as if the deduction never happened.

Transaction with Error Handling in a Procedure

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN from_acc INT,
    IN to_acc INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_acc;
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_acc;
    COMMIT;
END //

DELIMITER ;

CALL transfer_funds(1, 2, 5000);

If any SQL error occurs during the updates, the EXIT HANDLER triggers a ROLLBACK automatically.

Key Points

• A transaction groups multiple SQL statements into a single all-or-nothing unit.
• Transactions follow ACID properties to ensure data integrity.
• Use START TRANSACTION to begin, COMMIT to save, and ROLLBACK to undo.
• MySQL's default auto-commit mode commits each statement immediately — use START TRANSACTION to override this.
• Transactions work only with InnoDB tables, not MyISAM.