How to Use MySQL Transactions (COMMIT & ROLLBACK)

Picture a money transfer scenario between two accounts: the amount has already been deducted from the sender's account, but suddenly an error occurs so the addition to the recipient's account fails. As a result, money "disappears" from the system. This is exactly the problem that Transactions in MySQL solve. A transaction ensures that a group of SQL operations either succeeds completely or fails completely — there is no half-finished state.

The ACID Concept

A transaction in a database must satisfy four ACID properties:

• Atomicity: All operations within a single transaction either succeed or are all rolled back.
• Consistency: The database always moves from one valid state to another valid state.
• Isolation: Transactions running concurrently do not interfere with each other.
• Durability: Once a COMMIT happens, the changes are stored permanently even if a crash occurs.

Basic Transaction Commands

There are three main commands in a MySQL transaction:

• START TRANSACTION or BEGIN: Starts a new transaction.
• COMMIT: Saves all changes permanently.
• ROLLBACK: Cancels all changes made since the transaction began.

Preparing an Example Table

CREATE TABLE rekening (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_pemilik VARCHAR(100) NOT NULL,
    saldo DECIMAL(15, 2) NOT NULL DEFAULT 0.00,
    CHECK (saldo >= 0)
) ENGINE=InnoDB;

INSERT INTO rekening (nama_pemilik, saldo) VALUES
('Andi Saputra', 5000000.00),
('Budi Santoso', 3000000.00);

Basic Transaction Example: Transferring a Balance

-- Start the transaction
START TRANSACTION;

-- Deduct the sender's balance
UPDATE rekening
SET saldo = saldo - 1000000
WHERE id = 1 AND saldo >= 1000000;

-- Check whether the update succeeded
-- If ROW_COUNT() = 0 it means the balance is insufficient
-- In that case we ROLLBACK

-- Add to the recipient's balance
UPDATE rekening
SET saldo = saldo + 1000000
WHERE id = 2;

-- If everything succeeds, save permanently
COMMIT;

-- Check the balances after the transfer
SELECT * FROM rekening;

ROLLBACK – Cancelling a Transaction

If an error occurs in the middle of a transaction, use ROLLBACK to cancel all changes:

START TRANSACTION;

UPDATE rekening SET saldo = saldo - 2000000 WHERE id = 1;
UPDATE rekening SET saldo = saldo + 2000000 WHERE id = 2;

-- Simulation: an error occurred, cancel everything
ROLLBACK;

-- The balances return to their original values
SELECT * FROM rekening;

SAVEPOINT – A Checkpoint Within a Transaction

A SAVEPOINT lets you create checkpoints inside a transaction, so you can ROLLBACK to a specific point without cancelling the entire transaction:

START TRANSACTION;

-- First operation
UPDATE rekening SET saldo = saldo - 500000 WHERE id = 1;

-- Create a savepoint after the first operation
SAVEPOINT setelah_debit;

-- Second operation
UPDATE rekening SET saldo = saldo + 500000 WHERE id = 2;

-- Create a second savepoint
SAVEPOINT setelah_kredit;

-- Suppose a validation fails here
-- Roll back only to a specific savepoint
ROLLBACK TO SAVEPOINT setelah_debit;

-- Account 2's balance returns to its original value, but account 1 has already been deducted
-- Continue, or cancel entirely
ROLLBACK;  -- cancel everything

-- Remove savepoints that are no longer needed
-- RELEASE SAVEPOINT setelah_debit;

Autocommit in MySQL

By default, MySQL runs in autocommit mode, meaning every SQL statement is committed immediately. You can disable it:

-- Check the autocommit status
SELECT @@autocommit;

-- Disable autocommit for this session
SET autocommit = 0;

-- Now every change must be committed manually
UPDATE rekening SET saldo = saldo + 100000 WHERE id = 1;
COMMIT;  -- required to save the change

-- Re-enable autocommit
SET autocommit = 1;

Handling Transactions Inside Code (Simulation)

-- Simulating a transaction with error handling using a stored procedure
DELIMITER $$

CREATE PROCEDURE transfer_saldo(
    IN p_dari INT,
    IN p_ke INT,
    IN p_jumlah DECIMAL(15,2),
    OUT p_status VARCHAR(50)
)
BEGIN
    DECLARE v_saldo_pengirim DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'FAILED: A database error occurred';
    END;

    START TRANSACTION;

    -- Fetch and lock the sender's balance
    SELECT saldo INTO v_saldo_pengirim
    FROM rekening
    WHERE id = p_dari
    FOR UPDATE;

    -- Validate that the balance is sufficient
    IF v_saldo_pengirim < p_jumlah THEN
        ROLLBACK;
        SET p_status = 'FAILED: Insufficient balance';
    ELSE
        UPDATE rekening SET saldo = saldo - p_jumlah WHERE id = p_dari;
        UPDATE rekening SET saldo = saldo + p_jumlah WHERE id = p_ke;
        COMMIT;
        SET p_status = 'SUCCESS';
    END IF;
END$$

DELIMITER ;

-- Run the transfer
CALL transfer_saldo(1, 2, 750000, @hasil);
SELECT @hasil AS status_transfer;
SELECT * FROM rekening;

Important Transaction Tips

• Keep transactions as short as possible to avoid holding locks for too long.
• Use FOR UPDATE when reading data you are about to modify to prevent race conditions.
• Transactions only work on storage engines that support ACID, such as InnoDB. MyISAM does not support transactions.
• Always include error handling (ROLLBACK) in your application code.

Conclusion

Transactions are a crucial MySQL feature for guaranteeing data integrity in operations that involve multiple steps. With START TRANSACTION, COMMIT, and ROLLBACK, you can ensure that a group of SQL operations either succeeds completely or not at all. Use SAVEPOINT for more granular control within complex transactions. Always make sure the storage engine you use is InnoDB so the transaction feature is available and works according to the ACID properties.