MySQL Transactions

💳 What are MySQL Transactions?

A transaction is a group of SQL operations that execute as a single unit. All operations succeed together or fail together, ensuring data consistency and integrity in your database.


-- Simple transaction example
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ACID Properties

⚛️

Atomicity

All operations complete or none do

START TRANSACTION;
-- All or nothing
COMMIT;

✅

Consistency

Database remains in valid state

-- Data rules maintained
-- Constraints enforced

🔒

Isolation

Transactions don't interfere

SET TRANSACTION 
ISOLATION LEVEL 
READ COMMITTED;

💾

Durability

Changes persist after commit

COMMIT;
-- Saved permanently

🔹 Starting a Transaction

Begin a transaction using START TRANSACTION or BEGIN. This marks the starting point where you can group multiple SQL statements together. All changes remain temporary until you commit or rollback the transaction.

-- Method 1: START TRANSACTION
START TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (101, 250.00);

-- Method 2: BEGIN
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 5;

🔹 Committing Changes

COMMIT saves all changes made during the transaction permanently to the database. Once committed, the changes cannot be undone. Use COMMIT when you're certain all operations completed successfully and you want to make them permanent.

START TRANSACTION;

INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]');
INSERT INTO orders (customer_id, amount) VALUES (LAST_INSERT_ID(), 99.99);

-- Save all changes permanently
COMMIT;

🔹 Rolling Back Changes

ROLLBACK undoes all changes made during the current transaction, returning the database to its state before the transaction began. Use ROLLBACK when an error occurs or when you need to cancel operations without saving any changes.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Oops! Cancel everything
ROLLBACK;

🔹 Savepoints

Savepoints let you create checkpoints within a transaction. You can rollback to a specific savepoint without canceling the entire transaction. This is useful for complex operations where you want partial rollback capability while keeping other changes.

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (LAST_INSERT_ID(), 5);
SAVEPOINT items_added;

-- Rollback only to items_added
ROLLBACK TO items_added;

COMMIT;

🔹 Autocommit Mode

MySQL runs in autocommit mode by default, meaning each SQL statement is automatically committed immediately. You can disable autocommit to manually control when changes are saved. This gives you more control over transaction boundaries and error handling.

-- Check current autocommit status
SELECT @@autocommit;

-- Disable autocommit
SET autocommit = 0;

UPDATE products SET price = 29.99 WHERE id = 1;
-- Not saved yet, must COMMIT manually

COMMIT;

-- Re-enable autocommit
SET autocommit = 1;

🔹 Transaction Example: Bank Transfer

A classic example showing how transactions ensure money transfers are safe. If any step fails, the entire transfer is cancelled, preventing money from being lost or created incorrectly. This demonstrates the power of transactions in maintaining data integrity.

START TRANSACTION;

-- Deduct from sender
UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 1 AND balance >= 100;

-- Check if deduction succeeded
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    -- Add to receiver
    UPDATE accounts 
    SET balance = balance + 100 
    WHERE account_id = 2;
    
    COMMIT;
END IF;