Kenhuang Academy
  • English

      • 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