Database Transactions

Transactions ensure data integrity during operations.

Transaction Basics

BEGIN;
  Operation 1
  Operation 2
  Operation 3
COMMIT;  -- or ROLLBACK on error

Transaction States

Active → Partially Committed → Committed
   │                              │
   └──→ Failed → Aborted          │
                                  │
                              Terminated

Transaction Example

BEGIN TRANSACTION;

-- Transfer $100 from Alice to Bob
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- Log the transaction
INSERT INTO transactions (from_user, to_user, amount) 
VALUES ('Alice', 'Bob', 100);

COMMIT;
-- If any operation fails: ROLLBACK

Transaction Properties

Property	Description
Atomicity	All or nothing
Consistency	Valid state transitions
Isolation	Concurrent safety
Durability	Persists after commit

Transaction Benefits

1. Data Integrity
   - Consistent state
   - No partial updates

2. Concurrency Control
   - Multiple users safely
   - No race conditions

3. Error Recovery
   - Rollback on failure
   - Automatic cleanup

4. ACID Compliance
   - Reliable operations
   - Guaranteed results

Transaction Limitations

1. Performance Overhead
   - Locking reduces concurrency
   - Logging adds I/O

2. Scalability
   - Distributed transactions complex
   - Two-phase commit expensive

3. Deadlocks
   - Circular waits
   - Manual resolution needed

When to Use Transactions

Use transactions when:
- Multiple operations must succeed/fail together
- Data integrity critical
- Financial operations
- Inventory management
- User account operations

Avoid transactions when:
- Simple single operations
- High throughput needed
- Eventual consistency acceptable

Isolation levels control how transactions interact with each other.

Isolation Problems

1. Dirty Read
   T1 reads T2's uncommitted data
   T2 rolls back
   T1 has invalid data

2. Non-Repeatable Read
   T1 reads row twice
   T2 modifies row between reads
   T1 gets different values

3. Phantom Read
   T1 reads range of rows
   T2 inserts new row in range
   T1 sees different row count

4. Lost Update
   T1 and T2 read same row
   Both update
   One update lost

Isolation Levels

Level	Dirty Read	Non-Repeatable	Phantom
Read Uncommitted	Yes	Yes	Yes
Read Committed	No	Yes	Yes
Repeatable Read	No	No	Yes
Serializable	No	No	No

Isolation Level Examples

-- Read Uncommitted (lowest isolation)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Can read uncommitted data from other transactions

-- Read Committed (default in PostgreSQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Only reads committed data

-- Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Same query returns same results

-- Serializable (highest isolation)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transactions appear serial

Isolation Level Tradeoffs

Higher Isolation:
+ Better consistency
+ No anomalies
- Lower concurrency
- More locking
- Lower performance

Lower Isolation:
+ Higher concurrency
+ Better performance
- Possible anomalies
- Application must handle

Choosing Isolation Level

Read Uncommitted:
- Monitoring, analytics
- Dirty reads acceptable

Read Committed:
- Most applications
- Default choice

Repeatable Read:
- Financial reports
- Consistent reads needed

Serializable:
- Critical operations
- No anomalies allowed

MVCC (Multi-Version Concurrency Control)

PostgreSQL uses MVCC:
- Each transaction sees snapshot
- No read locks needed
- Readers don't block writers
- Writers don't block readers

Benefits:
- High concurrency
- No deadlocks from reads
- Consistent reads

Deadlocks occur when transactions wait for each other indefinitely.

Deadlock Example

Transaction T1:
1. Lock row A
2. Try to lock row B (waiting for T2)

Transaction T2:
1. Lock row B
2. Try to lock row A (waiting for T1)

Result: Both waiting forever = Deadlock

Deadlock Detection

Database detects deadlock:
- Wait-for graph
- Timeout detection
- Choose victim transaction
- Rollback victim
- Other transaction completes

Deadlock Prevention

1. Lock Ordering
   - Always lock rows in same order
   - Prevents circular waits

2. Lock Timeout
   - Set timeout for locks
   - Fail fast if can't acquire

3. Small Transactions
   - Hold locks for short time
   - Reduce deadlock window

4. Avoid Locks
   - Use optimistic locking
   - Use MVCC

Lock Ordering Example

-- BAD: Inconsistent ordering
-- T1: UPDATE accounts WHERE id = 1; UPDATE orders WHERE id = 1;
-- T2: UPDATE orders WHERE id = 1; UPDATE accounts WHERE id = 1;

-- GOOD: Consistent ordering
-- T1: UPDATE accounts WHERE id = 1; UPDATE orders WHERE id = 1;
-- T2: UPDATE accounts WHERE id = 1; UPDATE orders WHERE id = 1;

Optimistic Locking

-- Use version column
UPDATE products
SET name = 'New Name', version = version + 1
WHERE id = 1 AND version = 5;

-- If version changed, update fails
-- No locks held during read

Deadlock Best Practices

1. Use lock ordering: Consistent lock acquisition order
2. Keep transactions small: Minimize lock duration
3. Set lock timeouts: Fail fast on contention
4. Use optimistic locking: When contention is low
5. Monitor deadlocks: Track and analyze patterns