Transactions, ACID Properties, Concurrency Control, and Recovery

Transaction Concept

A transaction is a single logical unit of work that accesses and possibly modifies database contents. It consists of a sequence of read and write operations followed by commit or abort.

Properties:

• Atomicity: All or nothing execution
• Consistency: Database constraints maintained
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

ACID Properties

1. Atomicity (All-or-Nothing)

• Transaction treated as single unit
• Either all operations execute or none
• Rollback undoes partial execution
• Enforced by transaction management subsystem

2. Consistency (Correctness)

• Database moves from one consistent state to another
• Integrity constraints satisfied before and after
• Application programmer's responsibility to write correct transactions
• DBMS enforces constraints

3. Isolation (Concurrent Execution)

• Concurrent transactions isolated from each other
• Intermediate state of one transaction invisible to others
• Result same as serial execution
• Enforced by concurrency control subsystem

4. Durability (Persistence)

• Once committed, changes permanent
• Survive system failures
• Updates written to non-volatile storage
• Enforced by recovery subsystem

Transaction States

1. Active: Initial state, executing operations
2. Partially Committed: After final operation, before commit
3. Committed: Successfully completed, changes permanent
4. Failed: Cannot proceed normally (error, abort, deadlock)
5. Aborted: Rolled back, database restored to pre-transaction state
   • After abort: Either killed or restarted

Schedules

A schedule defines execution order of operations from concurrent transactions.

Types:

Serial Schedule: Transactions execute one after another

• T1 completes before T2 starts
• Always consistent
• Poor concurrency (low throughput)

Concurrent Schedule: Operations interleaved

• Better CPU and disk utilization
• Improved throughput and response time
• May cause inconsistencies without proper control

Goal: Maximize concurrency while ensuring correctness

Serializability

A concurrent schedule is serializable if equivalent to some serial schedule.

Why Important?

• Serializable schedules guarantee consistency
• Allow concurrent execution without anomalies
• Easier to reason about correctness

Types:

1. Conflict Serializability
2. View Serializability

COMPREHENSIVE EXAMPLES

EXAMPLE 1: ACID Properties in Banking

-- Transfer $100 from Account A to Account B
BEGIN TRANSACTION;

-- Read balances
DECLARE @balanceA DECIMAL(10,2);
DECLARE @balanceB DECIMAL(10,2);

SELECT @balanceA = Balance FROM Accounts WHERE AccountID = 'A';
SELECT @balanceB = Balance FROM Accounts WHERE AccountID = 'B';

-- Check sufficient funds (Consistency)
IF @balanceA >= 100
BEGIN
    -- Deduct from A (Atomicity)
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';
    
    -- Add to B (Atomicity)
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    
    -- Both succeed or both fail
    COMMIT; -- (Durability)
END
ELSE
BEGIN
    -- Insufficient funds
    ROLLBACK;
    PRINT 'Insufficient funds';
END

-- (Isolation): Other transactions don't see intermediate state

EXAMPLE 2: Conflict Serializability Analysis

Schedule S1:

Time    T1              T2
1       Read(X)
2                       Read(X)
3       X := X - 100
4       Write(X)
5                       X := X + 200
6                       Write(X)
7       Read(Y)
8                       Read(Y)
9       Y := Y + 100
10      Write(Y)
11                      Y := Y + 200
12                      Write(Y)

Conflicts:

1. T1 Write(X) [4] before T2 Write(X) [6] → T1 → T2
2. T1 Write(Y) [10] before T2 Write(Y) [12] → T1 → T2

Precedence Graph: T1 → T2 (no cycle)

Conclusion: Conflict serializable, equivalent to serial schedule T1→T2

Final Result:

• If X=100, Y=100 initially
• After T1→T2: X=200, Y=400

Schedule S2 (Non-Serializable):

Time    T1              T2
1       Read(X)
2                       Read(Y)
3       X := X - 100
4       Write(X)
5                       Y := Y - 100
6                       Write(Y)
7       Read(Y)
8                       Read(X)
9       Y := Y + 100
10      Write(Y)
11                      X := X + 100
12                      Write(X)

Conflicts:

1. T1 Write(X) [4] before T2 Read(X) [8] → T1 → T2
2. T2 Write(Y) [6] before T1 Read(Y) [7] → T2 → T1

Precedence Graph: T1 ⟷ T2 (cycle!)

Conclusion: NOT conflict serializable

EXAMPLE 3: Two-Phase Locking

Transaction T1 (Withdraw $100):

BEGIN TRANSACTION;

-- Growing Phase
Lock-S(Accounts)        -- Check existence
Lock-X(Account_A)       -- Will modify

IF Balance_A >= 100:
    Balance_A = Balance_A - 100
    UPDATE...
    
    -- Shrinking Phase (Strict 2PL: at commit)
    COMMIT
    Unlock-X(Account_A)
    Unlock-S(Accounts)
ELSE:
    ROLLBACK
    Unlock-X(Account_A)
    Unlock-S(Accounts)

EXAMPLE 4: Deadlock Detection

Time    T1                  T2                  T3
1       Lock-X(A)
2                           Lock-X(B)
3                                               Lock-X(C)
4       Request Lock-X(B)   
5       WAIT                Request Lock-X(C)
6                           WAIT                Request Lock-X(A)
7                                               WAIT

Wait-For Graph:
T1 → T2 → T3 → T1 (CYCLE - DEADLOCK!)

Victim Selection: Abort T3 (youngest)

8                                               ABORT
9                           Lock-X(C) granted
10                          COMMIT
11      Lock-X(B) granted
12      COMMIT

EXAMPLE 5: Timestamp Ordering

Transactions:

• T1: TS = 10
• T2: TS = 20
• T3: TS = 30

Data Item X:

• Initial R-TS(X) = 0, W-TS(X) = 0

Time    Operation           R-TS(X)     W-TS(X)     Result
1       T1: Read(X)         10          0           Success
2       T2: Write(X)        10          20          Success
3       T1: Write(X)        10          20          ABORT
        // TS(T1)=10 < W-TS(X)=20
        // T1 trying to overwrite T2's future write
        
4       T3: Read(X)         30          20          Success
5       T2: Write(X)        30          20          ABORT
        // TS(T2)=20 < R-TS(X)=30
        // T2 trying to overwrite T3's future read

EXAMPLE 6: Optimistic Concurrency Control

T1:                         T2:
Read Phase:                 Read Phase:
Read(X) [local]             
X = X + 100
                            Read(X) [local]
                            X = X * 2

Validation Phase:           
Check conflicts             
No conflicts found          
PASS                        

Write Phase:                
Write(X) to DB              

                            Validation Phase:
                            Check conflicts
                            T1 wrote X after T2 read
                            FAIL - ABORT T2

EXAMPLE 7: MVCC (Snapshot Isolation)

Timeline:
         T1                           T2
t1      BEGIN (Snapshot at t1)
t2      Read(X) → 100
t3                                   BEGIN (Snapshot at t3)
t4                                   Write(X=200)
t5      Read(X) → 100 (still!)       
t6                                   COMMIT
t7      Read(X) → 100 (consistent)
t8      COMMIT

Versions of X:
X@t0 = 100 (initial)
X@t6 = 200 (T2's write)

T1 reads X@t1 = 100 (snapshot isolation)
T2 reads X@t3 = 100, writes X@t6 = 200

EXAMPLE 8: Recovery Using Log

Log:
<T1 start>
<T1, A, 1000, 950>
<T1, B, 2000, 2050>
<T1 commit>
<T2 start>
<T2, C, 500, 450>
<T2, D, 300, 450>
*** CRASH ***

Recovery:
1. Identify committed: T1 (has <T1 commit>)
2. Identify active: T2 (no commit/abort)

3. REDO T1:
   - A = 950
   - B = 2050

4. UNDO T2:
   - D = 300 (restore old value)
   - C = 500 (restore old value)

Final State:
- A = 950 (T1 committed)
- B = 2050 (T1 committed)
- C = 500 (T2 undone)
- D = 300 (T2 undone)

EXAMPLE 9: Cascading Rollback

T1          T2          T3
Write(A)
            Read(A)     // Dirty read from T1
                        Read(A)  // Dirty read from T1
Abort       // T1 aborts

Result:
- T1 aborts (application logic)
- T2 must abort (read uncommitted data from T1)
- T3 must abort (read uncommitted data from T1)

Cascading rollback!

Prevention: Use Strict 2PL
- Hold locks until commit
- No dirty reads possible

EXAMPLE 10: Isolation Levels

-- READ UNCOMMITTED: Allows dirty reads
Session 1:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = 500 WHERE ID = 1;
-- Not committed yet

Session 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Balance FROM Accounts WHERE ID = 1;
-- Returns: 500 (DIRTY READ!)

Session 1:
ROLLBACK; -- Oops, T2 saw uncommitted data!

-- READ COMMITTED: Prevents dirty reads
Session 2:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT Balance FROM Accounts WHERE ID = 1;
-- Waits for Session 1 to commit/rollback
-- Or reads last committed value

-- REPEATABLE READ: Prevents dirty & non-repeatable reads
Session 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT Balance FROM Accounts WHERE ID = 1; -- 1000
-- Another session updates to 500 and commits
SELECT Balance FROM Accounts WHERE ID = 1; -- Still 1000!
COMMIT;

-- SERIALIZABLE: Prevents all anomalies
Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT COUNT(*) FROM Accounts; -- 10
-- Another session inserts new account and commits
SELECT COUNT(*) FROM Accounts; -- Still 10! (No phantom)
COMMIT;

PRACTICAL GUIDELINES

1. Choosing Concurrency Control:

Two-Phase Locking (2PL):

• Use when: Traditional OLTP systems
• Pros: Well-understood, widely implemented
• Cons: Deadlocks possible, lower concurrency
• Examples: Banking, e-commerce orders

Timestamp Ordering:

• Use when: Read-heavy workloads
• Pros: Deadlock-free, good for distributed systems
• Cons: More rollbacks
• Examples: Analytics, reporting

Optimistic:

• Use when: Conflicts rare
• Pros: High concurrency, no locks
• Cons: Validation overhead, may abort
• Examples: Document editing, wikis

MVCC:

• Use when: High read/write concurrency needed
• Pros: Readers don't block writers
• Cons: Storage overhead (multiple versions)
• Examples: PostgreSQL (default), Oracle

2. Transaction Design Best Practices:

Keep Transactions Short:

-- BAD: Long transaction
BEGIN TRANSACTION;
SELECT ... -- Heavy query
-- User interaction here! (NEVER!)
INSERT ...
COMMIT;

-- GOOD: Short transaction
SELECT ... -- Outside transaction
-- Process data
BEGIN TRANSACTION;
INSERT ...
COMMIT;

Acquire Locks in Consistent Order:

-- BAD: Deadlock possible
T1: Lock A, then Lock B
T2: Lock B, then Lock A

-- GOOD: Same order
T1: Lock A, then Lock B
T2: Lock A, then Lock B

Handle Deadlocks:

max_retries = 3
for attempt in range(max_retries):
    try:
        # Begin transaction
        conn.execute("BEGIN")
        # ... operations ...
        conn.execute("COMMIT")
        break  # Success
    except DeadlockException:
        conn.execute("ROLLBACK")
        if attempt == max_retries - 1:
            raise  # Give up
        time.sleep(random.uniform(0.1, 0.5))  # Random backoff

3. Choosing Isolation Level:

MySQL/InnoDB:

-- Default: REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ... operations ...
COMMIT;

PostgreSQL:

-- Default: READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ... operations ...
COMMIT;

4. Monitoring Transactions:

PostgreSQL:

-- Active transactions
SELECT * FROM pg_stat_activity 
WHERE state = 'active';

-- Long-running transactions
SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Blocking queries
SELECT blocked_locks.pid, blocked_activity.query,
       blocking_locks.pid AS blocking_pid, blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
JOIN pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

MySQL:

-- Active transactions
SHOW PROCESSLIST;

-- InnoDB status (includes locks, deadlocks)
SHOW ENGINE INNODB STATUS;

-- Lock waits
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

5. Deadlock Prevention:

Timeout-Based:

-- MySQL
SET innodb_lock_wait_timeout = 50; -- seconds

-- PostgreSQL
SET lock_timeout = '30s';
SET statement_timeout = '60s';

Lock Ordering:

• Always acquire locks on tables in same order
• Document locking policy
• Code reviews to enforce

6. Recovery Configuration:

Write-Ahead Logging:

-- PostgreSQL
wal_level = replica
fsync = on
synchronous_commit = on
wal_sync_method = fsync

-- MySQL InnoDB
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1

Checkpoint Configuration:

-- PostgreSQL
checkpoint_timeout = 5min
max_wal_size = 1GB
checkpoint_completion_target = 0.9

-- MySQL
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

7. Real-World Scenarios:

Banking (High Consistency):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Transfer logic
COMMIT;

E-commerce (Balance):

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- Order processing
COMMIT;

Analytics (Read Performance):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- Report can tolerate
BEGIN;
-- Long-running analytics query
COMMIT;

Social Media (MVCC):

• Use database with MVCC (PostgreSQL)
• Readers never block writers
• High concurrent read/write throughput