Transactions
Database Transactions
Transaction management - ACID properties, isolation levels, and concurrency control
Database Transactions
A transaction is the basic unit of database operations, ensuring that a group of operations either all succeed or all fail, guaranteeing data consistency and integrity.
What is a Transaction?
A transaction is a logical unit of database operations with the following characteristics:
-- Transfer example: must execute as a whole
BEGIN TRANSACTION;
-- Deduct from Account A
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- Deposit to Account B
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
-- Record transfer log
INSERT INTO transfer_logs (from_account, to_account, amount, created_at)
VALUES ('A', 'B', 100, NOW());
COMMIT; -- Commit if all succeed
-- or ROLLBACK; -- Rollback if any failsIf any of the above operations fail, all changes are rolled back, ensuring account balance consistency.
Transaction Lifecycle
┌─────────────────┐
│ BEGIN │
└────────┬────────┘
│
┌────────▼────────┐
│ ACTIVE STATE │
│ │
│ - Read data │
│ - Write data │
│ - Delete data │
└────────┬────────┘
│
┌──────────────┼──────────────┐
│ │ │
┌────────▼────────┐ │ ┌────────▼────────┐
│ COMMIT │ │ │ ROLLBACK │
│ (Success) │ │ │ (Failure) │
└────────┬────────┘ │ └────────┬────────┘
│ │ │
│ ┌────────▼────────┐ │
│ │ ABORT (Error) │ │
│ └────────┬────────┘ │
│ │ │
└──────────────┼──────────────┘
│
┌────────▼────────┐
│ END │
└─────────────────┘Why Transactions Matter
Scenario 1: Data Integrity
-- Problem without transactions
UPDATE accounts SET balance = balance - 100 WHERE id = 'A'; -- Success
-- System crash!
UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; -- Not executed
-- Result: Money disappeared
-- With transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
-- System crash! Transaction not committed
-- Result: All changes automatically rolled back, data remains consistentScenario 2: Concurrency Control
-- Two users buying the last item simultaneously
-- User A: SELECT stock FROM products WHERE id = 1; -- stock = 1
-- User B: SELECT stock FROM products WHERE id = 1; -- stock = 1
-- User A: UPDATE products SET stock = 0 WHERE id = 1;
-- User B: UPDATE products SET stock = 0 WHERE id = 1;
-- Problem: Both users think they got it
-- With transaction and lock
BEGIN;
SELECT stock FROM products WHERE id = 1 FOR UPDATE; -- Lock
IF stock > 0 THEN
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;
END IF;Topics
- ACID - The four properties of transactions: Atomicity, Consistency, Isolation, Durability
- Isolation Levels - From Read Uncommitted to Serializable
- Concurrency Control - Locking mechanisms, MVCC, deadlock handling