Transactions

A transaction is a group of SQL operations that either all succeed or all fail. This chapter introduces transaction usage.

ACID Properties

1. Atomicity: All or nothing
2. Consistency: Data remains consistent
3. Isolation: Transactions are isolated
4. Durability: Changes are permanent after commit

Basic Syntax

-- Start transaction
START TRANSACTION;
-- Or
BEGIN;

-- Execute SQL operations
INSERT INTO users (name) VALUES ('Zhang San');
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Commit transaction
COMMIT;

-- Or rollback transaction
ROLLBACK;

Transfer Example

START TRANSACTION;

-- Deduct from account A
UPDATE accounts 
SET balance = balance - 100 
WHERE id = 1;

-- Add to account B
UPDATE accounts 
SET balance = balance + 100 
WHERE id = 2;

-- Check balance
IF (SELECT balance FROM accounts WHERE id = 1) >= 0 THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Levels:
-- READ UNCOMMITTED
-- READ COMMITTED
-- REPEATABLE READ (MySQL default)
-- SERIALIZABLE

Savepoints

START TRANSACTION;

INSERT INTO users (name) VALUES ('User 1');
SAVEPOINT sp1;

INSERT INTO users (name) VALUES ('User 2');
SAVEPOINT sp2;

-- Rollback to savepoint
ROLLBACK TO sp1;

COMMIT;

Best Practices

1. Keep transactions short
2. Avoid user interaction
3. Set appropriate isolation level
4. Handle deadlocks
5. Use indexes to reduce locking

Summary

• Transactions ensure data consistency
• ACID properties
• BEGIN/COMMIT/ROLLBACK
• Isolation levels
• Savepoints

Next Step: Learn DATABASE-DESIGN