SQL Transactions

A transaction is a logical unit of work containing one or more SQL statements. All statements either complete successfully (commit) or fail together (rollback), ensuring data consistency.

Transaction Lifecycle

flowchart TB
    A[BEGIN TRANSACTION] --> B[Execute SQL Statements]
    B --> C{Error Occurred?}
    C -->|No| D[COMMIT]
    C -->|Yes| E[ROLLBACK]
    
    D --> F[Changes Permanent]
    E --> G[Changes Undone]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#00BCD4
    style E fill:#F44336

Key Points:

• BEGIN: Starts transaction, marks beginning of atomic unit
• Execute: Run INSERT, UPDATE, DELETE statements
• COMMIT: Makes all changes permanent and visible to others
• ROLLBACK: Undoes all changes, restores database to initial state
• Atomic Unit: All operations succeed together or fail together

ACID Properties

graph TB
    A[ACID Properties] --> B[Atomicity]
    A --> C[Consistency]
    A --> D[Isolation]
    A --> E[Durability]
    
    B --> B1[All or Nothing]
    B --> B2[No Partial Updates]
    
    C --> C1[Valid State to Valid State]
    C --> C2[Constraints Maintained]
    
    D --> D1[Concurrent Transactions]
    D --> D2[No Interference]
    
    E --> E1[Permanent Changes]
    E --> E2[Survive Crashes]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#FF9800
    style D fill:#9C27B0
    style E fill:#F44336

Key Points:

• Atomicity: Transaction fully completes or fully fails, no partial updates
• Consistency: Database moves from one valid state to another, rules maintained
• Isolation: Concurrent transactions don't interfere with each other
• Durability: Committed changes survive system failures and crashes

Atomicity Example

sequenceDiagram
    participant App
    participant Transaction
    participant AccountA
    participant AccountB
    
    App->>Transaction: BEGIN
    Transaction->>AccountA: Debit $100
    AccountA->>Transaction: Success
    Transaction->>AccountB: Credit $100
    AccountB->>Transaction: Error!
    Transaction->>AccountA: ROLLBACK
    Transaction->>App: Transaction Failed
    
    Note over Transaction: Both operations<br/>cancelled together

Key Points:

• Bank Transfer: Classic example of atomicity requirement
• All or Nothing: Both debit and credit must succeed
• Rollback: Error in any step cancels entire transaction
• Data Integrity: Prevents money from disappearing or duplicating

Isolation Levels Comparison

graph TB
    A[Isolation Levels] --> B[READ UNCOMMITTED]
    A --> C[READ COMMITTED]
    A --> D[REPEATABLE READ]
    A --> E[SERIALIZABLE]
    
    B --> B1[Dirty Reads: Yes]
    B --> B2[Fastest, Least Safe]
    
    C --> C1[Dirty Reads: No]
    C --> C2[Default Level]
    
    D --> D1[Non-Repeatable: No]
    D --> D2[Phantom: Yes]
    
    E --> E1[All Anomalies: No]
    E --> E2[Slowest, Most Safe]
    
    style A fill:#2196F3
    style B fill:#F44336
    style C fill:#FF9800
    style D fill:#4CAF50
    style E fill:#9C27B0

Key Points:

• READ UNCOMMITTED: Reads uncommitted data, allows dirty reads
• READ COMMITTED: Only reads committed data, most common default
• REPEATABLE READ: Same query returns same results within transaction
• SERIALIZABLE: Highest isolation, transactions appear sequential
• Trade-off: Higher isolation = better consistency but lower concurrency

Dirty Read Problem

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    participant DB as Database
    
    T1->>DB: BEGIN
    T1->>DB: UPDATE balance = 500
    T2->>DB: BEGIN (READ UNCOMMITTED)
    T2->>DB: SELECT balance
    DB->>T2: Returns 500 (Dirty Read!)
    T1->>DB: ROLLBACK
    Note over DB: Balance back to 1000
    T2->>T2: Uses 500 (Wrong!)
    T2->>DB: COMMIT

Key Points:

• Dirty Read: Reading uncommitted data from another transaction
• Problem: Data may be rolled back, leading to incorrect results
• READ UNCOMMITTED: Only isolation level that allows dirty reads
• Solution: Use READ COMMITTED or higher isolation level

Locking Mechanisms

graph TB
    A[Database Locks] --> B[Shared Lock S]
    A --> C[Exclusive Lock X]
    
    B --> B1[Read Operations]
    B --> B2[Multiple Allowed]
    B --> B3[Blocks Exclusive]
    
    C --> C1[Write Operations]
    C --> C2[Only One Allowed]
    C --> C3[Blocks All Others]
    
    D[Lock Compatibility] --> E[S + S = Compatible]
    D --> F[S + X = Blocked]
    D --> G[X + X = Blocked]
    
    style A fill:#2196F3
    style B fill:#4CAF50
    style C fill:#F44336
    style D fill:#FF9800

Key Points:

• Shared Lock (S): Multiple transactions can read simultaneously
• Exclusive Lock (X): Only one transaction can write, blocks all others
• Lock Compatibility: Shared locks compatible with each other
• Deadlock Risk: Two transactions waiting for each other's locks

Deadlock Scenario

sequenceDiagram
    participant T1 as Transaction 1
    participant T2 as Transaction 2
    participant R1 as Resource A
    participant R2 as Resource B
    
    T1->>R1: Lock Resource A
    T2->>R2: Lock Resource B
    T1->>R2: Request Lock on B
    Note over T1: Waiting...
    T2->>R1: Request Lock on A
    Note over T2: Waiting...
    Note over T1,T2: DEADLOCK!<br/>Both waiting forever

Key Points:

• Circular Wait: T1 waits for T2, T2 waits for T1
• Detection: Database detects deadlock and kills one transaction
• Prevention: Access resources in consistent order
• Retry Logic: Application should retry failed transaction

Savepoint Usage

flowchart TB
    A[BEGIN TRANSACTION] --> B[Operation 1]
    B --> C[SAVEPOINT sp1]
    C --> D[Operation 2]
    D --> E{Error?}
    E -->|Yes| F[ROLLBACK TO sp1]
    E -->|No| G[SAVEPOINT sp2]
    F --> H[Operation 3]
    G --> H
    H --> I[COMMIT]
    
    style A fill:#2196F3
    style C fill:#4CAF50
    style E fill:#FF9800
    style F fill:#F44336
    style I fill:#00BCD4

Key Points:

• Partial Rollback: Undo to specific point, not entire transaction
• Multiple Savepoints: Create checkpoints throughout transaction
• Error Recovery: Retry failed operation without starting over
• Nested Logic: Useful for complex multi-step operations

Two-Phase Commit (Distributed)

sequenceDiagram
    participant Coordinator
    participant DB1 as Database 1
    participant DB2 as Database 2
    
    Note over Coordinator,DB2: Phase 1: Prepare
    Coordinator->>DB1: Prepare to commit
    Coordinator->>DB2: Prepare to commit
    DB1->>Coordinator: Ready
    DB2->>Coordinator: Ready
    
    Note over Coordinator,DB2: Phase 2: Commit
    Coordinator->>DB1: COMMIT
    Coordinator->>DB2: COMMIT
    DB1->>Coordinator: Done
    DB2->>Coordinator: Done

Key Points:

• Distributed Transactions: Span multiple databases or systems
• Phase 1 (Prepare): All participants prepare and vote
• Phase 2 (Commit): If all ready, coordinator orders commit
• Atomicity: All databases commit or all rollback together

Code Examples

Basic Transaction

-- Bank transfer example
BEGIN TRANSACTION;

UPDATE accounts 
SET balance = balance - 100 
WHERE account_id = 1;

UPDATE accounts 
SET balance = balance + 100 
WHERE account_id = 2;

-- Check if both succeeded
IF @@ERROR = 0
    COMMIT;
ELSE
    ROLLBACK;

Isolation Level

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

BEGIN TRANSACTION;
SELECT * FROM orders WHERE status = 'pending';
-- Process orders
COMMIT;

Savepoints

BEGIN TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (1, 10);
-- Error occurred
ROLLBACK TO order_created;

-- Retry with different product
INSERT INTO order_items (order_id, product_id) VALUES (1, 20);
COMMIT;

Deadlock Prevention

-- Always access tables in same order
BEGIN TRANSACTION;

-- Lock in consistent order: accounts then orders
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE id = 100;

COMMIT;

Best Practices

1. Keep Transactions Short: Minimize lock duration
2. Consistent Order: Access resources in same order to prevent deadlocks
3. Error Handling: Always handle errors and rollback appropriately
4. Appropriate Isolation: Use lowest level that meets requirements
5. Avoid User Input: Don't wait for user input during transaction
6. Index Properly: Reduce lock contention with good indexes

---