1. Introduction to Transactions

A transaction is a set of operations that are treated as a single unit. All operations in a transaction are either committed or rolled back together. By default, each SQL statement is an automatic transaction.

2. Transaction Operations

-- 1. Check Alice's account balance
SELECT * FROM account WHERE name = 'Alice';
-- 2. Subtract 1000 from Alice
UPDATE account SET money = money - 1000 WHERE name = 'Alice';
-- Simulate an error here (Alice's money decreases but Bob's doesn't increase)
-- 3. Add 1000 to Bob
UPDATE account SET money = money + 1000 WHERE name = 'Bob';

-- Check auto-commit mode
SELECT @@AUTOCOMMIT;
-- Set auto-commit off (0 = manual, 1 = auto; only for current session)
SET @@AUTOCOMMIT = 0;
-- Commit transaction
COMMIT;
-- Rollback transaction
ROLLBACK;

-- After setting manual commit, rewrite the above as:
SELECT * FROM account WHERE name = 'Alice';
UPDATE account SET money = money - 1000 WHERE name = 'Alice';
UPDATE account SET money = money + 1000 WHERE name = 'Bob';
COMMIT;

START TRANSACTION;
SELECT * FROM account WHERE name = 'Alice';
UPDATE account SET money = money - 1000 WHERE name = 'Alice';
UPDATE account SET money = money + 1000 WHERE name = 'Bob';
COMMIT;

-- Start transaction:
START TRANSACTION or BEGIN TRANSACTION;
-- Commit:
COMMIT;
-- Rollback:
ROLLBACK;

3. ACID Properties

• Atomicity: A transaction is an indivisible unit; either all operations succeed or all fail.
• Consistency: After a transaction, all data remains in a consistent state (e.g., total money of Alice and Bob remains unchanged after a transfer).
• Isolation: Transactions are executed independently without interference from concurrent operations.
• Durability: Once a transaction is committed or rolled back, changes to the database are permanent.

4. Concurrency Transaction Problems

Concurrency problems occur when multiple transactions run simultaneously.

5. Transaction Isolation Levels

MySQL's default isolation level is Repeatable Read. Higher isolation levels reduce performance, so modify with caution.