Transaction Processing

From master.txt:

A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database using read and write operations.

DBMS enforces ACID:

• Atomicity: all-or-nothing; if a transaction aborts, its changes are not visible; if it commits, its changes become visible.
• Consistency: integrity constraints remain satisfied before and after the transaction.
• Isolation: changes in one transaction are not visible to others until commit.
• Durability: once committed, updates persist on disk even after failures.

This is why a money transfer must either complete both updates or roll back completely if the system fails mid-way.

Example 1: Transaction in SQL

-- Bank Transfer: Savings to Checking
BEGIN TRANSACTION;

-- Step 1: Deduct from savings
UPDATE Account
SET Balance = Balance - 500
WHERE AccountID = 101 AND AccountType = 'Savings';

-- Step 2: Add to checking
UPDATE Account
SET Balance = Balance + 500
WHERE AccountID = 101 AND AccountType = 'Checking';

-- Verify balance constraints
IF (SELECT Balance FROM Account WHERE AccountID=101 AND AccountType='Savings') < 0
ROLLBACK;
ELSE
COMMIT;
END TRANSACTION;

Example 2: Deadlock Scenario

Example 3: Recovery Scenario

Log Contents:
<T1 start>
<T1, X, 100, 150>
<T1, Y, 200, 250>
<T1 commit>
<T2 start>
<T2, X, 150, 200>
<T2, Y, 250, 300>
--- CRASH ---

Recovery:

1. Redo T1 (committed): X=150, Y=250
2. Undo T2 (not committed): X=150 (revert T2's change), Y=250

Final State: X=150, Y=250

Example 4: Isolation Levels in Action

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;
SELECT Balance FROM Account WHERE ID=101; -- Reads 1000
-- (Session 2 updates Balance to 500 but doesn't commit)
SELECT Balance FROM Account WHERE ID=101; -- Reads 500 (DIRTY READ!)
COMMIT;

-- With READ COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT Balance FROM Account WHERE ID=101; -- Reads 1000
-- (Session 2 updates to 500 but doesn't commit)
SELECT Balance FROM Account WHERE ID=101; -- Still reads 1000
COMMIT;

-- With SERIALIZABLE (PostgreSQL)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT SUM(Balance) FROM Account; -- Reads sum
-- (Session 2 inserts new account)
SELECT SUM(Balance) FROM Account; -- Same sum (no phantom)
COMMIT;

Best Practices:

1. Transaction Design:

   • Keep transactions short
   • Acquire locks in consistent order (prevent deadlock)
   • Avoid user interaction within transaction
   • Handle exceptions (rollback on error)

2. Choosing Isolation Level:

   • READ UNCOMMITTED: Never (except analytics)
   • READ COMMITTED: Default for most applications
   • REPEATABLE READ: Financial transactions, reports
   • SERIALIZABLE: Critical data, when correctness > performance

3. Performance Tuning:

   • Monitor lock wait times
   • Identify long-running transactions
   • Use row-level locking (not table-level)
   • Consider optimistic locking for low contention

4. Deadlock Prevention:

   • Access tables in same order
   • Use timeouts
   • Keep transactions short
   • Retry aborted transactions

5. Application-Level Considerations:

   • Implement retry logic
   • Use connection pooling
   • Consider eventual consistency (NoSQL)
   • Saga pattern for distributed transactions

Real-World Usage:

• Banking: SERIALIZABLE for transfers
• E-commerce: READ COMMITTED for order processing
• Social media: READ COMMITTED, eventual consistency for likes
• Stock trading: SERIALIZABLE with strict locking
• Analytics: READ UNCOMMITTED (stale data acceptable)