Persistence Responsibilities - Learning Module

Loading content...

0/246

Transaction Management

The All-or-Nothing Imperative

Consider a bank transfer: debit $100 from Account A and credit $100 to Account B. What happens if the system crashes after the debit but before the credit? Without proper transaction management, Account A loses $100 that never arrives at Account B. The money has vanished into the void—an unacceptable outcome that would quickly destroy trust in any financial system.

This scenario illustrates why transaction management is not optional—it's fundamental to data integrity. A transaction ensures that a series of operations either all complete successfully or all roll back as if nothing happened. The persistence layer must provide this guarantee while managing the complexity of concurrent access, system failures, and performance requirements.

What You Will Learn

By the end of this page, you will understand the ACID properties that define transaction behavior, how to establish and control transaction boundaries in application code, the implications of different isolation levels, and the patterns that experienced engineers use to manage transactions effectively in real-world systems.

Understanding ACID Properties

The ACID properties define the guarantees that a properly functioning transaction system must provide. These guarantees were formalized in the 1980s and remain the gold standard for relational database transactions. Understanding ACID is essential for reasoning about data consistency.

The ACID Properties
Property	Definition	What It Guarantees	Failure Mode Without It
Atomicity	All operations in a transaction succeed or all fail together	No partial updates; the database never reflects half-completed work	Partial state changes leave data inconsistent
Consistency	Transactions move the database from one valid state to another	All constraints, triggers, and rules are satisfied after each transaction	Constraint violations, invalid data relationships
Isolation	Concurrent transactions don't interfere with each other	Each transaction sees a consistent snapshot; changes are invisible until commit	Dirty reads, lost updates, phantom reads
Durability	Committed transactions survive system failures	Once committed, data persists through power loss, crashes, and restarts	Data loss after commit acknowledgment

Atomicity in Depth:

Atomicity means a transaction is indivisible—all operations complete, or none do. This is implemented through:

Write-Ahead Logging (WAL): Before modifying actual data, the database writes intended changes to a log. If a crash occurs, the log can be replayed (for committed transactions) or discarded (for uncommitted ones).
Rollback Segments: The database keeps old versions of modified data so it can restore them if the transaction aborts.
Two-Phase Commit: For distributed transactions spanning multiple databases, coordinators ensure all participants either commit or abort together.

From the application's perspective, atomicity means you can think of complex, multi-step operations as single units. Either the bank transfer completes fully, or the accounts are unchanged.

Consistency in Depth:

Consistency ensures the database remains valid after every transaction. "Valid" means:

All primary key and unique constraints hold
All foreign key relationships are satisfied
All check constraints pass
All triggers execute without errors
Any application-defined invariants are maintained

Important Distinction: The database enforces schema-level consistency (constraints). Application-level consistency (business rules like "account balance ≥ 0") must be enforced by your application logic within transactions.

Durability in Depth:

Durability guarantees that committed data survives failures. Implementation techniques include:

Sync writes: Forcing data to disk before acknowledging commit
Replication: Copying data to multiple nodes before acknowledging commit
Battery-backed RAM: Keeping cached writes in non-volatile memory

Durability has a performance cost—synchronous disk writes are slow. Many databases offer tunable durability (e.g., PostgreSQL's synchronous_commit setting) to trade durability for speed in non-critical scenarios.

ACID is a Spectrum

While traditional relational databases provide strong ACID guarantees, many modern databases (especially distributed ones) trade some ACID properties for scalability and availability. Understanding which properties you need—and which you can relax—is crucial for choosing the right storage system. Not every application needs full serializability; not every application can tolerate eventual consistency.

Defining Transaction Boundaries

Transaction boundaries define where a transaction begins and where it ends (with either commit or rollback). Setting boundaries correctly is one of the most important decisions in persistence layer design.

Too Narrow (Multiple Small Transactions):

Transaction 1: Debit Account A
Transaction 2: Credit Account B  ← If this fails, Account A is already debited!

Too Wide (One Giant Transaction):

Transaction: Process all 10,000 orders in batch
← If one order fails, all 9,999 successful ones roll back!
← Holds locks for minutes, blocking other operations

Correct Boundaries:

Transaction: Debit Account A AND Credit Account B
← Both succeed or both fail
← Locks held only briefly

The right transaction boundary encompasses exactly the operations that must succeed or fail together—your logical unit of work.

transaction-boundaries
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
// Transaction boundaries in application code
 
// ❌ WRONG: Separate transactions - no atomicity guarantee
async function transferMoneyBroken(
    fromId: string, 
    toId: string, 
    amount: number
) {
    // Transaction 1
    await db.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', 
        [amount, fromId]);
    
    // ← DANGER ZONE: If anything fails here, money is lost!
    
    // Transaction 2 (separate, auto-committed)
    await db.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', 
        [amount, toId]);
}
 
// ✅ CORRECT: Single transaction - atomicity guaranteed
async function transferMoneyCorrect(
    fromId: string, 
    toId: string, 
    amount: number
) {
    const client = await pool.connect();
    
    try {
        // Begin explicit transaction
        await client.query('BEGIN');
        
        // Check sufficient balance (within transaction for consistency)
        const fromResult = await client.query(
            'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
            [fromId]
        );
        
        if (fromResult.rows[0].balance < amount) {
            await client.query('ROLLBACK');
            throw new InsufficientFundsError(fromId, amount);
        }
        
        // Perform both updates in same transaction
        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );
        
        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );
        
        // Commit - makes both changes permanent atomically
        await client.query('COMMIT');
        
    } catch (error) {
        // Rollback - undoes any changes made in this transaction
        await client.query('ROLLBACK');
        throw error;
        
    } finally {
        // ALWAYS release the connection
        client.release();
    }
}
 
// ✅ BETTER: Using a transactional decorator/wrapper
class TransactionManager {
    async runInTransaction<T>(
        work: (client: DatabaseClient) => Promise<T>
    ): Promise<T> {
        const client = await this.pool.connect();
        
        try {
            await client.query('BEGIN');
            const result = await work(client);
            await client.query('COMMIT');
            return result;
        } catch (error) {
            await client.query('ROLLBACK');
            throw error;
        } finally {
            client.release();
        }
    }
}
 
// Usage: Clean business logic with guaranteed transaction handling
async function transferMoney(fromId: string, toId: string, amount: number) {
    return txManager.runInTransaction(async (client) => {
        const from = await accountRepo.findByIdForUpdate(client, fromId);
        
        if (from.balance < amount) {
            throw new InsufficientFundsError(fromId, amount);
        }
        
        await accountRepo.updateBalance(client, fromId, from.balance - amount);
        await accountRepo.updateBalance(client, toId, amount);
        
        return { success: true, newBalance: from.balance - amount };
    });
}

Transaction Boundary Pitfall: External Calls

Never make external service calls (HTTP, message queues) inside database transactions! If the transaction takes 30 seconds due to a slow external service, you're holding database locks for 30 seconds. If the external call succeeds but the transaction rolls back, you've made an irrevocable external change. External integrations should happen AFTER commit, possibly using outbox pattern or saga pattern.

Transaction Isolation Levels

Isolation determines how concurrent transactions see each other's changes. Perfect isolation (serializability) means transactions execute as if they were run one after another in sequence. However, perfect isolation has performance costs. SQL standards define four isolation levels, each trading some isolation for better concurrency.

SQL Transaction Isolation Levels
Level	Dirty Reads	Non-Repeatable Reads	Phantom Reads	Performance
READ UNCOMMITTED	Possible	Possible	Possible	Highest
READ COMMITTED	Prevented	Possible	Possible	High
REPEATABLE READ	Prevented	Prevented	Possible	Medium
SERIALIZABLE	Prevented	Prevented	Prevented	Lowest

Understanding the Anomalies:

Dirty Read: Reading uncommitted changes from another transaction that might later be rolled back.

T1: UPDATE accounts SET balance = 0 WHERE id = 'A';
T2: SELECT balance FROM accounts WHERE id = 'A';  -- Reads 0
T1: ROLLBACK;
-- T2 read a value that never actually existed!

Non-Repeatable Read: Reading the same row twice in a transaction and getting different values because another transaction modified and committed between reads.

T1: SELECT balance FROM accounts WHERE id = 'A';  -- Returns 100
T2: UPDATE accounts SET balance = 50 WHERE id = 'A'; COMMIT;
T1: SELECT balance FROM accounts WHERE id = 'A';  -- Returns 50
-- T1 sees different values for the same query within same transaction!

Phantom Read: Running the same query twice and getting different rows because another transaction inserted/deleted rows that match the query criteria.

T1: SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 5
T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
T1: SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- Returns 6
-- T1 sees "phantom" row appear within same transaction!

isolation-levels
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
// Choosing the right isolation level for different use cases
 
class TransactionManager {
    
    // Default: READ COMMITTED - good balance for most operations
    async runReadCommitted<T>(work: TransactionWork<T>): Promise<T> {
        return this.runWithIsolation('READ COMMITTED', work);
    }
    
    // For reporting/analytics - can read slightly stale data
    async runReadOnly<T>(work: TransactionWork<T>): Promise<T> {
        const client = await this.pool.connect();
        try {
            await client.query('BEGIN TRANSACTION READ ONLY');
            const result = await work(client);
            await client.query('COMMIT');
            return result;
        } catch (error) {
            await client.query('ROLLBACK');
            throw error;
        } finally {
            client.release();
        }
    }
    
    // For financial operations requiring strict consistency
    async runSerializable<T>(work: TransactionWork<T>): Promise<T> {
        return this.runWithRetry(
            () => this.runWithIsolation('SERIALIZABLE', work),
            { maxRetries: 3, retryOn: SerializationFailure }
        );
    }
    
    private async runWithIsolation<T>(
        level: IsolationLevel,
        work: TransactionWork<T>
    ): Promise<T> {
        const client = await this.pool.connect();
        try {
            await client.query(`BEGIN ISOLATION LEVEL ${level}`);
            const result = await work(client);
            await client.query('COMMIT');
            return result;
        } catch (error) {
            await client.query('ROLLBACK');
            throw error;
        } finally {
            client.release();
        }
    }
}
 
// Usage examples with appropriate isolation levels
 
// Financial transfer: SERIALIZABLE prevents any anomalies
async function transferFunds(fromId: string, toId: string, amount: number) {
    return txManager.runSerializable(async (client) => {
        // At SERIALIZABLE level, this behaves as if no other 
        // transactions are running concurrently
        const from = await client.query(
            'SELECT balance FROM accounts WHERE id = $1',
            [fromId]
        );
        
        if (from.rows[0].balance < amount) {
            throw new InsufficientFundsError();
        }
        
        await client.query(
            'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
            [amount, fromId]
        );
        await client.query(
            'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
            [amount, toId]
        );
    });
}
 
// Dashboard report: READ COMMITTED is fine, no strong consistency needed
async function getDashboardStats() {
    return txManager.runReadCommitted(async (client) => {
        const orderCount = await client.query(
            'SELECT COUNT(*) FROM orders WHERE status = $1',
            ['pending']
        );
        const revenue = await client.query(
            'SELECT SUM(total) FROM orders WHERE created_at > $1',
            [today()]
        );
        return { orderCount: orderCount.rows[0].count, revenue: revenue.rows[0].sum };
    });
}

SERIALIZABLE Requires Retry Logic

At SERIALIZABLE level, the database may detect that concurrent transactions would create anomalies and abort one of them with a serialization failure. Your application code must be prepared to retry the aborted transaction. This is the trade-off: stronger isolation but more complex error handling.

Locking Strategies

Databases use locks to implement isolation. Understanding locking helps you predict and avoid performance problems like lock contention and deadlocks.

Shared Locks (Read Locks)

•Acquired for SELECT operations
•Multiple transactions can hold shared locks on same resource
•Prevents writes while held
•Released after read (or transaction end, depending on isolation)
•Low contention - readers don't block readers

Exclusive Locks (Write Locks)

•Acquired for UPDATE/DELETE/INSERT operations
•Only one transaction can hold exclusive lock
•Blocks all other access to the resource
•Held until transaction commits or rolls back
•High contention - writers block everyone

locking-examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- Explicit locking for different scenarios
 
-- SELECT FOR UPDATE: Acquire exclusive lock on rows
-- Use when you'll update the row later in the same transaction
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE;
-- Row is now locked - other transactions wait or fail
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
COMMIT;
-- Lock released
 
-- SELECT FOR SHARE: Acquire shared lock on rows
-- Use when you need to ensure rows don't change during transaction
-- but you won't update them yourself
BEGIN;
SELECT * FROM products WHERE category = 'electronics' FOR SHARE;
-- Rows are read-locked - can't be modified by others
-- But other readers can still access them
-- Do some calculation or validation...
COMMIT;
 
-- SKIP LOCKED: Non-blocking lock acquisition
-- Perfect for job queues and work distribution
BEGIN;
SELECT * FROM tasks 
WHERE status = 'pending' 
ORDER BY priority DESC
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- Skip already-locked rows, don't wait
-- Process the task...
UPDATE tasks SET status = 'processing' WHERE id = ?;
COMMIT;
 
-- NOWAIT: Fail immediately if lock not available
BEGIN;
SELECT * FROM accounts WHERE id = 'A' FOR UPDATE NOWAIT;
-- If row is locked, immediately throws error instead of waiting
-- Useful for fast-fail scenarios
COMMIT;
 
-- Advisory Locks: Application-level locks (PostgreSQL)
-- For coordinating across transactions or for custom locking logic
SELECT pg_advisory_lock(12345);  -- Acquire lock on arbitrary ID
-- Do work that requires mutual exclusion...
SELECT pg_advisory_unlock(12345);  -- Release lock

Deadlocks:

A deadlock occurs when two transactions each hold a lock that the other needs:

T1: Holds lock on Row A, waiting for lock on Row B
T2: Holds lock on Row B, waiting for lock on Row A
→ Neither can proceed!

Databases detect deadlocks and resolve them by aborting one transaction (the "victim"). Your application must be prepared to retry.

Preventing Deadlocks:

Lock ordering: Always acquire locks in a consistent order (e.g., by ID ascending)
Lock all at once: Acquire all needed locks at the beginning of the transaction
Keep transactions short: Less time holding locks = less chance of deadlock
Use optimistic locking: Avoid explicit locking where possible

Long Transactions Cause Lock Buildup

Every lock held by a long-running transaction potentially blocks other transactions. In high-throughput systems, a 5-second transaction holding row locks can queue up hundreds of waiting transactions, causing cascading delays. Keep transactions as short as possible. Do expensive computations BEFORE starting the transaction, not during.

Transaction Patterns in Practice

Several patterns have emerged for managing transactions effectively in real applications. These patterns address common challenges like transaction scope, failure handling, and integration with external systems.

Essential Transaction Patterns

•Unit of Work — Tracks all changes during a business transaction and commits/rolls back as a single unit. Implementations include EntityManager (JPA), Session (Hibernate), DbContext (Entity Framework).
•Transaction Script — A single procedure handles one complete business transaction. Simple but can lead to duplication. Common in CRUD-heavy applications.
•Domain Model + Repository — Rich domain objects encapsulate business logic; repositories manage persistence. Transaction scope typically spans an application service method.
•CQRS (Command Query Responsibility Segregation) — Separates read and write models. Writes use strong transactions; reads may use eventual consistency or replicas.
•Saga Pattern — For long-running transactions spanning multiple services. Uses compensating transactions instead of rollback.
•Outbox Pattern — Guarantees that database changes and message publishing happen atomically by writing messages to an outbox table within the same transaction.

unit-of-work-pattern
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
// Unit of Work pattern: Track and commit all changes together
 
interface UnitOfWork {
    // Repositories accessed through Unit of Work share the same transaction
    readonly orders: OrderRepository;
    readonly customers: CustomerRepository;
    readonly inventory: InventoryRepository;
    
    // Commit all tracked changes atomically
    commit(): Promise<void>;
    
    // Discard all tracked changes
    rollback(): Promise<void>;
}
 
class DatabaseUnitOfWork implements UnitOfWork {
    private connection: DatabaseConnection;
    private committed = false;
    
    // Lazy-initialized repositories sharing the same connection
    private _orders?: OrderRepository;
    private _customers?: CustomerRepository;
    
    constructor(private readonly pool: ConnectionPool) {}
    
    async initialize(): Promise<void> {
        this.connection = await this.pool.acquireConnection();
        await this.connection.query('BEGIN');
    }
    
    get orders(): OrderRepository {
        if (!this._orders) {
            // Repository uses this UoW's connection
            this._orders = new OrderRepository(this.connection);
        }
        return this._orders;
    }
    
    get customers(): CustomerRepository {
        if (!this._customers) {
            this._customers = new CustomerRepository(this.connection);
        }
        return this._customers;
    }
    
    async commit(): Promise<void> {
        if (this.committed) {
            throw new Error('Unit of Work already completed');
        }
        await this.connection.query('COMMIT');
        this.committed = true;
        this.connection.release();
    }
    
    async rollback(): Promise<void> {
        if (this.committed) {
            throw new Error('Unit of Work already completed');
        }
        await this.connection.query('ROLLBACK');
        this.committed = true;
        this.connection.release();
    }
}
 
// Usage in application service
class OrderPlacementService {
    constructor(private readonly unitOfWorkFactory: () => Promise<UnitOfWork>) {}
    
    async placeOrder(customerId: string, items: OrderItem[]): Promise<Order> {
        const uow = await this.unitOfWorkFactory();
        
        try {
            // All operations use the same transaction
            const customer = await uow.customers.findById(customerId);
            if (!customer) {
                throw new CustomerNotFoundError(customerId);
            }
            
            // Check inventory (within transaction - locked)
            for (const item of items) {
                const available = await uow.inventory.checkAvailability(item.productId);
                if (available < item.quantity) {
                    throw new InsufficientInventoryError(item.productId);
                }
            }
            
            // Reserve inventory
            for (const item of items) {
                await uow.inventory.reserve(item.productId, item.quantity);
            }
            
            // Create order
            const order = Order.create(customer, items);
            await uow.orders.save(order);
            
            // Everything succeeded - commit all changes atomically
            await uow.commit();
            
            return order;
            
        } catch (error) {
            // Any failure - roll back everything
            await uow.rollback();
            throw error;
        }
    }
}

The Outbox Pattern for Reliable Messaging:

A common challenge is ensuring that a database update and a message publication are atomic. If you commit the database change and then publish fails, the message is lost. If you publish first and the database commit fails, you've sent a message about something that didn't happen.

The Outbox Pattern solves this:

Within your database transaction, write both your data changes AND the message to an "outbox" table
Commit the transaction—now both are stored atomically
A separate process reads the outbox table and publishes messages
After successful publish, mark outbox entries as processed

This ensures messages are published exactly if and only if the corresponding data change commits.

Distributed Transactions and Their Alternatives

When data spans multiple databases or services, transaction management becomes significantly more complex. Distributed transactions attempt to maintain ACID properties across multiple independent systems.

Two-Phase Commit (2PC):

The classic approach to distributed transactions:

Phase 1 (Prepare):

Coordinator sends "prepare" to all participants
Each participant writes changes to durable storage and locks resources
Participants respond "ready" or "abort"

Phase 2 (Commit/Abort):

If all said "ready": Coordinator sends "commit" to all
If any said "abort" or timed out: Coordinator sends "abort" to all
Participants finalize and release locks

2PC Problems in Modern Systems

Two-Phase Commit has serious drawbacks: it's blocking (participants hold locks until coordinator responds), fragile (coordinator failure can leave participants in limbo), and slow (network round-trips for coordination). In microservices architectures, 2PC is generally avoided in favor of eventual consistency patterns.

Modern Alternatives:

1. Saga Pattern:

Instead of a distributed transaction, execute a sequence of local transactions. If a step fails, execute compensating transactions to undo previous steps.

Order Saga:
1. Create Order (local transaction)
2. Reserve Inventory → If fails, Cancel Order
3. Charge Payment → If fails, Release Inventory, Cancel Order
4. Ship Order → If fails, Refund Payment, Release Inventory, Cancel Order

Saga Coordination:

Choreography: Each service listens for events and decides its own actions
Orchestration: A central coordinator explicitly tells each service what to do

2. Eventual Consistency:

Accept that data may be temporarily inconsistent across services, but will converge to consistency given enough time. Requires careful analysis of business implications.

3. Change Data Capture (CDC):

Capture database changes as they happen and propagate them to other systems. Guarantees that downstream systems eventually receive all changes.

Distributed Transaction Approaches
Approach	Consistency	Availability	Complexity	Use When
2PC	Strong	Low (blocking)	Medium	All participants are databases you control
Sagas	Eventual	High	High	Long-running processes, microservices
Eventual Consistency	Eventual	High	Medium	Read-heavy, tolerance for stale data
Outbox + CDC	Eventual (ordered)	High	Medium	Reliable messaging needs

Summary: Transaction Management

Transaction management is fundamental to data integrity. Let's consolidate the key concepts:

Key Takeaways

•ACID properties define transaction guarantees — Atomicity, Consistency, Isolation, and Durability work together to ensure data integrity.
•Transaction boundaries must match business operations — Too narrow loses atomicity; too wide causes performance problems.
•Isolation levels trade consistency for performance — Choose the right level based on your application's tolerance for anomalies.
•Locking prevents concurrent access conflicts — Understand shared vs. exclusive locks and how to avoid deadlocks.
•Patterns like Unit of Work simplify transaction management — These patterns encapsulate transaction lifecycle handling.
•Distributed transactions are complex — Consider Sagas and eventual consistency as alternatives to 2PC in microservices.

What's Next:

Now that we understand data operations, storage abstraction, and transactions, the next page explores query optimization—how the persistence layer ensures efficient data retrieval through indexing, query planning, and performance tuning. This is where the theoretical meets the practical, turning correct code into fast code.

Page Complete

You now understand transaction management in the persistence layer. These concepts are essential for building systems that maintain data integrity even under concurrent access and in the face of failures. Next, we'll explore query optimization techniques.