ACID and Transaction Control

Learning Focus

Use this lesson to understand ACID properties and transaction control — the mechanism that ensures your database stays consistent even when things go wrong.

Concept Overview

What Is a Transaction?

A transaction is a group of SQL operations that succeed or fail together. If any operation in the group fails, all of them are undone — as if nothing happened.

The classic example: Transferring money from Account A to Account B. Two operations must happen together:

1. Subtract $100 from Account A
2. Add $100 to Account B

If step 1 succeeds but step 2 fails (crash, error, network issue), the money vanishes. Transactions prevent this by guaranteeing both happen or neither happens.

ACID Properties

Every MySQL transaction is governed by four properties known as ACID:

Property	Meaning	Real-World Analogy
Atomicity	All operations complete, or none do	Bank transfer: debit + credit are one unit
Consistency	The database moves from one valid state to another	Constraints and rules are always satisfied
Isolation	Concurrent transactions don't interfere with each other	Two users editing the same order don't see half-written data
Durability	Once committed, data survives crashes	A confirmed payment stays confirmed even after a server restart

InnoDB and Transactions

MySQL's InnoDB storage engine supports full ACID transactions. MyISAM does not. Always use InnoDB for tables that need transactional safety.

-- Check your table's storage engine
SHOW TABLE STATUS LIKE 'orders'\G
-- Engine: InnoDB ← good

---

Basic Syntax & Rules

Transaction Control Statements

Statement	Purpose
START TRANSACTION	Begin a new transaction
COMMIT	Save all changes permanently
ROLLBACK	Undo all changes since START TRANSACTION
SAVEPOINT name	Create a named checkpoint within a transaction
ROLLBACK TO name	Undo changes back to a savepoint (not the whole transaction)
RELEASE SAVEPOINT name	Remove a savepoint

Auto-Commit Mode

By default, MySQL runs in auto-commit mode — every single SQL statement is its own transaction:

-- Each statement auto-commits immediately
INSERT INTO orders (customer_id, total_amount) VALUES (1, 100);
-- This is already committed! There's no ROLLBACK possible.

To group multiple statements, explicitly start a transaction:

START TRANSACTION;

INSERT INTO orders (customer_id, total_amount) VALUES (1, 100);
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Both operations are now pending. Choose one:
COMMIT;     -- Save both permanently
-- or
ROLLBACK;   -- Undo both

---

Step-by-Step Examples

Example 1: Basic Money Transfer

Step 1: Check initial balances

SELECT account_id, balance FROM accounts WHERE account_id IN (1001, 1002);

account_id	balance
1001	5000.00
1002	3000.00

Step 2: Perform the transfer

START TRANSACTION;

-- Debit sender
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1001;

-- Credit receiver
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1002;

-- Verify before committing
SELECT account_id, balance FROM accounts WHERE account_id IN (1001, 1002);

account_id	balance
1001	4500.00
1002	3500.00

Step 3: Commit (or rollback)

COMMIT;  -- Makes the changes permanent

If something looked wrong in the verification step:

ROLLBACK;  -- Undoes everything — balances return to original

---

Example 2: ROLLBACK on Error

Scenario: Insert an order and its items. If any item fails, undo everything.

START TRANSACTION;

INSERT INTO orders (customer_id, total_amount, status)
VALUES (1024, 750.00, 'pending');

SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 5, 2, 250.00);

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 8, 1, 250.00);

-- If all succeeded:
COMMIT;

If any INSERT fails (e.g., constraint violation), call ROLLBACK to undo everything.

---

Example 3: Savepoints for Partial Rollback

START TRANSACTION;

INSERT INTO orders (customer_id, total_amount) VALUES (1, 100);
SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 5, 2, 50.00);

-- Oops, wrong product! Roll back only the item, keep the order
ROLLBACK TO after_order;

-- Insert the correct item
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 7, 2, 50.00);

COMMIT;  -- Order + correct item are saved

---

Example 4: Transaction in a Stored Procedure

DELIMITER //

CREATE PROCEDURE transfer_funds(
    IN p_from BIGINT,
    IN p_to   BIGINT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Transfer failed — rolled back';
    END;

    START TRANSACTION;

    UPDATE accounts SET balance = balance - p_amount
    WHERE account_id = p_from;

    -- Check for negative balance
    IF (SELECT balance FROM accounts WHERE account_id = p_from) < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Insufficient funds';
    END IF;

    UPDATE accounts SET balance = balance + p_amount
    WHERE account_id = p_to;

    COMMIT;
END //

DELIMITER ;

---

Practical Use Cases

1. Financial Transactions

Money transfers, payment processing, refunds — must be atomic to prevent money loss.

2. Order Processing

Create order + items + update inventory + log event — all succeed or all fail.

3. User Registration

Create user + assign role + create profile + send welcome email trigger — atomic setup.

4. Batch Import with Validation

Import 1000 rows; if any row violates constraints, rollback the entire batch.

5. Schema Migrations

Some DDL operations can be wrapped in transactions (with limitations in MySQL).

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Forgetting START TRANSACTION	Every statement auto-commits immediately	Explicitly start transactions for multi-step operations
Long-running transactions	Holds locks, blocks other queries	Keep transactions short — validate before, transact briefly
No error handling in procedures	Failed operations leave transactions open	Use DECLARE EXIT HANDLER with ROLLBACK
Mixing DDL inside transactions	DDL (ALTER TABLE, CREATE TABLE) causes implicit commit	Avoid DDL inside transactions; run DDL separately
Not checking for partial failures	Some updates succeed, some silently fail	Verify ROW_COUNT() after each operation
Forgetting to COMMIT	Transaction stays open, locks held indefinitely	Always end with COMMIT or ROLLBACK

DDL and Implicit Commits

Some statements automatically commit any open transaction:

START TRANSACTION;
INSERT INTO orders ...;
CREATE TABLE temp (...);  -- ⚠️ This COMMITS the INSERT!
ROLLBACK;                  -- Too late — INSERT is already committed

Statements that cause implicit commits include: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE, GRANT, REVOKE.

---

Best Practices

• Keep transactions short — acquire data, validate, then transact quickly
• Validate before transacting — check preconditions outside the transaction when possible
• Always handle errors — use DECLARE EXIT HANDLER with ROLLBACK in procedures
• Avoid user interaction inside transactions — don't wait for user input in the middle of a transaction
• Use savepoints for complex workflows — partial rollbacks without losing all work
• Monitor long transactions — SHOW PROCESSLIST reveals open transactions
• Prefer InnoDB — it's the only MySQL engine with full ACID support

---

Hands-On Practice

Exercise 1: Basic Transaction (Easy)

Write a transaction that:

1. Inserts a new customer
2. Inserts their first order
3. Commits both

Then write a version that rolls back instead of committing. Verify the customer doesn't exist.

Exercise 2: Transfer with Validation (Medium)

Write a transaction that transfers 500 from account 1001 to 1002:

1. Start transaction
2. Debit 1001
3. Check that 1001's balance is still >= 0
4. If yes, credit 1002 and commit
5. If no, rollback

Exercise 3: Savepoint Recovery (Advanced)

Write a transaction that inserts an order with 3 items. After the second item, create a savepoint. Insert the third item with a deliberate error. Rollback to the savepoint, insert the correct third item, and commit. Verify only the correct items exist.

---

Connection to Other Concepts

Related Concept	How It Connects
Isolation Levels	Control how transactions see each other's uncommitted changes
Deadlocks	Occurred when two transactions wait for each other's locks
Stored Procedures	Procedures are the natural place for transaction logic
Error Handling	DECLARE HANDLER + ROLLBACK is the standard pattern
Constraints	Constraint violations cause transaction failures — handle them

What to Learn Next

• Isolation Levels and Locking — control what concurrent transactions can see

---

Visual Learning Diagram

flowchart TD
    A["START TRANSACTION"] --> B["Execute SQL operations"]
    B --> C{"All succeeded?"}
    C -->|Yes| D["COMMIT"]
    C -->|No| E["ROLLBACK"]
    D --> F["✅ Changes are permanent and durable"]
    E --> G["❌ All changes are undone"]

    H["SAVEPOINT sp1"] --> I["More operations"]
    I --> J{"Error?"}
    J -->|Yes| K["ROLLBACK TO sp1"]
    J -->|No| L["Continue to COMMIT"]
    K --> M["Retry from savepoint"]

    classDef success fill:#28a745,stroke:#fff,color:#fff
    classDef error fill:#dc3545,stroke:#fff,color:#fff
    class D,F success
    class E,G error

Quick Reference

-- Start and commit
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Rollback
START TRANSACTION;
DELETE FROM orders WHERE order_id = 999;
ROLLBACK;  -- Nothing was deleted

-- Savepoints
START TRANSACTION;
INSERT INTO orders ...;
SAVEPOINT sp1;
INSERT INTO order_items ...;
ROLLBACK TO sp1;  -- Undo items, keep order
COMMIT;

-- Check autocommit
SELECT @@autocommit;
SET autocommit = 0;  -- Disable (manual transactions)

What's Next

• Isolation Levels and Locking