Transactions ensure database operations are reliable and consistent. ACID properties (Atomicity, Consistency, Isolation, Durability) guarantee data integrity even when things go wrong.

Basic Transaction Syntax

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

The ACID Properties

Atomicity (All or Nothing)

BEGIN TRANSACTION;
    INSERT INTO orders (customer_id, total) VALUES (123, 500.00);
    INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 456, 2);
    -- If either fails, both are rolled back
COMMIT;

Consistency (Valid State)

Database constraints (foreign keys, checks) ensure data remains valid after each transaction.

Isolation (Concurrent Transactions)

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT * FROM inventory WHERE product_id = 100;
    -- Other transactions can't modify this row until we commit
COMMIT;

Durability (Permanent Changes)

Once committed, changes survive system crashes and are permanently saved to disk.

Rollback Example

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    -- Check if balance went negative
    IF (SELECT balance FROM accounts WHERE account_id = 1) < 0 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

Key Takeaways

• Atomicity: Transactions are all-or-nothing operations
• Consistency: Data always follows defined rules
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data persists permanently