Transaction Boundaries — Transaction Definition | OneNoughtOne

0/131

Database Management SystemTransaction Definition

Transaction Definition

LevelIntermediate

Duration60 mins

TopicTransaction Definition

2 / 5

Transaction Boundaries

Defining Where Transactions Begin and End

Knowing that a transaction is an atomic unit of work is only half the story. The equally critical question is: where does this unit begin and where does it end? The answer isn't always obvious, and getting it wrong leads to subtle bugs—data inconsistencies that pass all tests but corrupt production systems. Transaction boundaries determine which operations are grouped together for atomic execution. Set them too narrow, and you lose consistency guarantees between related operations. Set them too wide, and you create performance bottlenecks and increase the risk of deadlocks. This page teaches you to set them just right.

What You Will Learn

By the end of this page, you will understand how transaction boundaries are defined in SQL and programming languages, the difference between explicit and implicit boundary definition, how to choose appropriate boundaries for different scenarios, and common pitfalls that arise from incorrect boundary placement.

Understanding Transaction Boundaries

A transaction boundary marks the beginning or end of a transaction. The operations between the start boundary and end boundary constitute the transaction's scope—everything within is atomic; everything outside is not guaranteed to be consistent with the transaction's operations. Boundaries can be: Explicit — Programmer directly specifies when a transaction starts and ends using SQL commands or API calls Implicit — The database system automatically determines boundaries based on configuration and operation types Most production code uses explicit boundaries for control and clarity, while implicit boundaries are convenient for ad-hoc queries and simple operations.

Transaction Boundary Types
Boundary Type	How It Works	When to Use
Explicit Start	Programmer issues BEGIN/START TRANSACTION	Multi-statement operations; operations requiring specific isolation level
Implicit Start	First operation automatically starts transaction	Single statements in autocommit mode; Oracle's default behavior
Explicit End (Commit)	Programmer issues COMMIT to make changes permanent	All production transactions; end of successful operation
Explicit End (Rollback)	Programmer issues ROLLBACK to undo all changes	Error handling; detected validation failures
Implicit End	Transaction ends with statement completion (autocommit)	Simple, independent operations; testing/debugging

Boundary Mismatch Disasters

The most insidious transaction bugs occur when intended boundaries don't match actual boundaries. A developer thinks they're in a transaction, but autocommit is on. Or they start a transaction but forget to commit or rollback, leaving connections in limbo. Always verify your boundary assumptions.

Explicit Boundary Definition in SQL

Each database system provides SQL syntax for explicitly defining transaction boundaries. While the concepts are universal, the exact syntax varies:

transaction_boundaries.sql

-- Explicit transaction with BEGIN and COMMIT
BEGIN;  -- or START TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    INSERT INTO transfer_log (from_id, to_id, amount, timestamp)
        VALUES (1, 2, 500, CURRENT_TIMESTAMP);
COMMIT;  -- Make all changes permanent
 
-- Explicit rollback on error
BEGIN;
    UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 100;
    -- Simulate check: if insufficient inventory, rollback
    -- In real code, this would be application logic
ROLLBACK;  -- Undo all changes in this transaction
 
-- Transaction with explicit isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SELECT * FROM critical_data WHERE id = 1 FOR UPDATE;
    UPDATE critical_data SET value = value + 1 WHERE id = 1;
COMMIT;

Key Observations: PostgreSQL/MySQL/SQL Server require an explicit BEGIN to start a multi-statement transaction Oracle starts a transaction implicitly with the first DML statement—no BEGIN command All systems use COMMIT to make changes permanent and ROLLBACK to undo them Savepoints provide checkpoints within a transaction for partial rollback, available in all major systems

Naming Transactions

SQL Server allows naming transactions (BEGIN TRANSACTION TransferFunds). While optional, named transactions improve log readability and debugging. They don't provide nesting—only the outermost COMMIT affects the database.

Implicit Boundaries and Autocommit

Most databases can operate in autocommit mode, where each SQL statement is automatically wrapped in its own transaction. This mode is convenient for interactive sessions but often inappropriate for production applications.

Autocommit Advantages

•Simple for ad-hoc queries
•No forgotten uncommitted transactions
•Each statement is immediately durable
•Lower lock hold times for simple operations
•Easier debugging in development

Autocommit Disadvantages

•No multi-statement atomicity
•Cannot rollback partial operations
•Performance overhead per statement
•Related operations can diverge
•False sense of transaction safety

autocommit_behavior.sql

-- Check current autocommit status (in psql)
\echo :AUTOCOMMIT
 
-- Toggle autocommit in psql
\set AUTOCOMMIT off
-- Now each statement doesn't auto-commit
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Changes are NOT yet permanent
COMMIT;  -- Now they're permanent
 
-- Application code typically uses connection settings
-- or explicit BEGIN to override autocommit

DDL Commits Automatically

In MySQL and Oracle, DDL statements (CREATE, ALTER, DROP) cause an implicit commit of any open transaction BEFORE and AFTER execution. This means you cannot rollback a CREATE TABLE, and any pending DML is committed when DDL executes. PostgreSQL supports transactional DDL.

Oracle's Implicit Transaction Start: Oracle is unique among major databases: it never uses autocommit by default. Instead: The first DML statement automatically starts a transaction The transaction remains open until explicit COMMIT or ROLLBACK DDL statements cause implicit commit before and after execution Disconnecting with uncommitted changes causes automatic rollback (in most clients) This behavior requires developers to be more conscious of transaction boundaries but also provides natural grouping of related operations.

Boundary Management in Application Code

Real applications manage transaction boundaries through database drivers and frameworks, not raw SQL. Understanding how your programming language and framework handle boundaries is essential for correct transaction behavior.

application_boundaries.ts
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
import { PrismaClient } from '@prisma/client';
 
// Prisma transaction block - automatic boundary management
async function transferFunds(fromId: string, toId: string, amount: number) {
    // $transaction creates explicit boundaries
    // If any operation throws, entire transaction rolls back
    return await prisma.$transaction(async (tx) => {
        // All operations use 'tx' client, not 'prisma'
        const sender = await tx.account.update({
            where: { id: fromId },
            data: { balance: { decrement: amount } },
        });
        
        if (sender.balance < 0) {
            throw new Error('Insufficient funds');
            // Transaction will rollback automatically
        }
        
        const receiver = await tx.account.update({
            where: { id: toId },
            data: { balance: { increment: amount } },
        });
        
        await tx.transferLog.create({
            data: { fromId, toId, amount, timestamp: new Date() },
        });
        
        return { sender, receiver };
    }); // COMMIT happens here if no error
}
 
// Interactive transaction with explicit timeout
await prisma.$transaction(
    async (tx) => {
        // Long-running operations
    },
    {
        maxWait: 5000,  // Max time to wait for transaction slot
        timeout: 10000, // Max transaction duration
        isolationLevel: 'Serializable',
    }
);

Framework Default Behavior

Each framework has default transaction behavior. Spring's @Transactional uses REQUIRED propagation (join existing or create new) and READ_COMMITTED isolation by default. Prisma's $transaction creates SERIALIZABLE transactions. Always know your framework's defaults and override when necessary.

Boundary Strategies

Choosing where to place transaction boundaries is a critical design decision. Several strategies exist, each with distinct advantages and appropriate use cases.

Transaction Boundary Strategies
Strategy	Description	Best For	Watch Out For
Per-Request	One transaction per HTTP request/RPC call	Web applications with simple request-response patterns	Long-running requests hold locks; background jobs may conflict
Per-Use-Case	Transaction matches business operation (e.g., 'transfer funds')	Domain-driven design; clear business operations	May need multiple transactions for complex operations
Per-Aggregate	Transaction covers one aggregate root in DDD	Microservices; event-sourced systems	Cross-aggregate consistency requires sagas or 2PC
Per-Statement	Each statement is its own transaction (autocommit)	Simple queries; read-heavy workloads	No atomicity across operations; data can diverge
Long Transaction	Transaction spans user think-time (pessimistic locking)	Reservation systems; exclusive editing	Scalability issues; deadlock risk; abandoned transactions

The Per-Use-Case Strategy in Depth: The most maintainable approach is often per-use-case boundaries, where each transaction corresponds to exactly one business operation: Use Case: Transfer Funds Transaction Scope: 1. Validate sufficient balance 2. Debit source account 3. Credit destination account 4. Record transfer in audit log 5. Commit This approach has several benefits: Clear semantics: Developers understand what's atomic Natural error handling: Failure rolls back the entire business operation Appropriate scope: Not too narrow (losing atomicity) or too wide (holding locks too long) Testable: Use cases can be tested in isolation

The Danger of Long Transactions

Avoid 'conversational' transactions that span user think-time. If a transaction starts at 'Begin Edit' and ends at 'Save', a user who goes to lunch leaves locks held for hours. Use optimistic concurrency control instead: read data without locking, detect conflicts at commit time.

Savepoints for Partial Rollback

Sometimes you need finer-grained control within a transaction. Savepoints create checkpoints that allow rolling back to an intermediate state without aborting the entire transaction.

savepoints.sql
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
-- Savepoint example: Order processing with fallback
BEGIN;
 
-- Step 1: Update order status
UPDATE orders SET status = 'processing' WHERE id = 101;
 
-- Create savepoint before risky operation
SAVEPOINT before_payment;
 
-- Step 2: Attempt to process payment
UPDATE payments SET status = 'charged', charged_at = NOW() 
WHERE order_id = 101;
 
-- Simulate payment failure scenario
-- In real code, this check would be application logic
-- If payment_gateway_error THEN
    ROLLBACK TO SAVEPOINT before_payment;
    -- Order status change is preserved!
    -- Only payment changes are undone
    UPDATE orders SET status = 'payment_failed' WHERE id = 101;
-- END IF;
 
-- Create another savepoint
SAVEPOINT before_shipping;
 
-- Step 3: Initiate shipping
UPDATE shipping SET status = 'initiated' WHERE order_id = 101;
 
-- If shipping unavailable, can rollback to before_shipping
-- while keeping payment changes
 
COMMIT;  -- Commits everything not rolled back

Savepoint Use Cases

•Try alternate strategies: Attempt operation A, if it fails, rollback and try operation B
•Batch processing with recovery: Process 100 records, mark savepoint every 10; if record 35 fails, rollback only to record 30's savepoint
•Optional sub-operations: Execute core logic, attempt enhancements (like sending notifications), rollback enhancements if they fail without affecting core
•Testing scenarios: In test code, create savepoint, run destructive test, rollback to savepoint to restore state

Savepoints Are Not Commits

Savepoints don't make changes durable—they're just markers within an active transaction. If the entire transaction rolls back (or the system crashes), all changes since BEGIN are lost, regardless of savepoints. Only COMMIT makes changes permanent.

Nested Transactions vs. Savepoints: Some databases (like SQL Server) support 'nested transactions' syntax, but these are effectively savepoints: BEGIN TRANSACTION Outer; -- operations BEGIN TRANSACTION Inner; -- more operations COMMIT TRANSACTION Inner; -- Only releases savepoint! -- We're still in Outer transaction ROLLBACK TRANSACTION Outer; -- Undoes EVERYTHING including 'committed' Inner True nested transactions (where inner commits are durable even if outer rolls back) are rare. PostgreSQL offers them via autonomous transactions in PL/pgSQL, and Oracle supports PRAGMA AUTONOMOUS_TRANSACTION in PL/SQL.

Common Boundary Mistakes

Transaction boundary errors are among the most insidious bugs—they often pass testing but cause data corruption in production. Here are the patterns to avoid:

Anti-Patterns to Avoid

•Assuming Transaction Exists — Writing code that expects to be in a transaction without verifying. In autocommit mode, each statement commits independently. Always explicitly start transactions or use framework mechanisms that guarantee them.
•Forgotten Commit/Rollback — Starting a transaction but not ending it. The connection remains 'in transaction' state, holding locks. Subsequent operations may block indefinitely or join the existing (stale) transaction.
•Transaction Spanning User Input — Starting a transaction, then waiting for user input, then committing. User delays hold locks for unbounded time. Use optimistic locking: read without transaction, detect conflicts at write time.
•Network Calls Inside Transactions — Making external API calls within transaction boundaries. If the API is slow or fails, your transaction holds locks the entire time. Move external calls outside the transaction when possible.
•Inconsistent Boundary Levels — Mixing boundary strategies in the same codebase. Some methods start their own transactions, others assume they're in one. Use consistent patterns and make transaction boundaries explicit in method signatures or documentation.
•Silent Autocommit Changes — Framework or configuration changes enabling/disabling autocommit without developer awareness. Code that worked with explicit transactions may corrupt data with autocommit on.

boundary_mistakes.ts
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
// ❌ WRONG: Assumes caller started transaction
async function updateAccountBalance(id: string, amount: number) {
    // If autocommit is on, this is its own transaction!
    await prisma.account.update({
        where: { id },
        data: { balance: { increment: amount } },
    });
}
 
// ❌ WRONG: Transaction spans external call
async function processOrderBad(orderId: string) {
    await prisma.$transaction(async (tx) => {
        const order = await tx.order.update({
            where: { id: orderId },
            data: { status: 'processing' },
        });
        
        // External API call - if slow, transaction holds locks!
        const shipping = await shippingApi.createLabel(order);
        
        await tx.order.update({
            where: { id: orderId },
            data: { shippingLabel: shipping.label },
        });
    });
}
 
// ✅ CORRECT: Explicit transaction, external calls outside
async function processOrderGood(orderId: string) {
    // Step 1: Transaction for marking as processing
    const order = await prisma.$transaction(async (tx) => {
        return tx.order.update({
            where: { id: orderId },
            data: { status: 'processing' },
        });
    });
    
    // Step 2: External call OUTSIDE transaction
    const shipping = await shippingApi.createLabel(order);
    
    // Step 3: Another transaction to save result
    await prisma.$transaction(async (tx) => {
        await tx.order.update({
            where: { id: orderId },
            data: { 
                shippingLabel: shipping.label,
                status: 'shipped',
            },
        });
    });
}

Transaction Boundary Testing

Write tests that specifically exercise transaction behavior: test that failures cause rollbacks, that concurrent access doesn't corrupt data, that commits actually persist. Use database connections in test mode to verify isolation.

Database Management SystemTransaction Definition

Transaction Definition

LevelIntermediate

Duration60 mins

TopicTransaction Definition

2 / 5

Transaction Boundaries

Defining Where Transactions Begin and End

Knowing that a transaction is an atomic unit of work is only half the story. The equally critical question is: where does this unit begin and where does it end? The answer isn't always obvious, and getting it wrong leads to subtle bugs—data inconsistencies that pass all tests but corrupt production systems. Transaction boundaries determine which operations are grouped together for atomic execution. Set them too narrow, and you lose consistency guarantees between related operations. Set them too wide, and you create performance bottlenecks and increase the risk of deadlocks. This page teaches you to set them just right.

What You Will Learn

By the end of this page, you will understand how transaction boundaries are defined in SQL and programming languages, the difference between explicit and implicit boundary definition, how to choose appropriate boundaries for different scenarios, and common pitfalls that arise from incorrect boundary placement.

Understanding Transaction Boundaries

A transaction boundary marks the beginning or end of a transaction. The operations between the start boundary and end boundary constitute the transaction's scope—everything within is atomic; everything outside is not guaranteed to be consistent with the transaction's operations. Boundaries can be: Explicit — Programmer directly specifies when a transaction starts and ends using SQL commands or API calls Implicit — The database system automatically determines boundaries based on configuration and operation types Most production code uses explicit boundaries for control and clarity, while implicit boundaries are convenient for ad-hoc queries and simple operations.

Transaction Boundary Types
Boundary Type	How It Works	When to Use
Explicit Start	Programmer issues BEGIN/START TRANSACTION	Multi-statement operations; operations requiring specific isolation level
Implicit Start	First operation automatically starts transaction	Single statements in autocommit mode; Oracle's default behavior
Explicit End (Commit)	Programmer issues COMMIT to make changes permanent	All production transactions; end of successful operation
Explicit End (Rollback)	Programmer issues ROLLBACK to undo all changes	Error handling; detected validation failures
Implicit End	Transaction ends with statement completion (autocommit)	Simple, independent operations; testing/debugging

Boundary Mismatch Disasters

The most insidious transaction bugs occur when intended boundaries don't match actual boundaries. A developer thinks they're in a transaction, but autocommit is on. Or they start a transaction but forget to commit or rollback, leaving connections in limbo. Always verify your boundary assumptions.

Explicit Boundary Definition in SQL

Each database system provides SQL syntax for explicitly defining transaction boundaries. While the concepts are universal, the exact syntax varies:

transaction_boundaries.sql

-- Explicit transaction with BEGIN and COMMIT
BEGIN;  -- or START TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
    INSERT INTO transfer_log (from_id, to_id, amount, timestamp)
        VALUES (1, 2, 500, CURRENT_TIMESTAMP);
COMMIT;  -- Make all changes permanent
 
-- Explicit rollback on error
BEGIN;
    UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 100;
    -- Simulate check: if insufficient inventory, rollback
    -- In real code, this would be application logic
ROLLBACK;  -- Undo all changes in this transaction
 
-- Transaction with explicit isolation level
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    SELECT * FROM critical_data WHERE id = 1 FOR UPDATE;
    UPDATE critical_data SET value = value + 1 WHERE id = 1;
COMMIT;

Key Observations: PostgreSQL/MySQL/SQL Server require an explicit BEGIN to start a multi-statement transaction Oracle starts a transaction implicitly with the first DML statement—no BEGIN command All systems use COMMIT to make changes permanent and ROLLBACK to undo them Savepoints provide checkpoints within a transaction for partial rollback, available in all major systems

Naming Transactions

SQL Server allows naming transactions (BEGIN TRANSACTION TransferFunds). While optional, named transactions improve log readability and debugging. They don't provide nesting—only the outermost COMMIT affects the database.

Implicit Boundaries and Autocommit

Most databases can operate in autocommit mode, where each SQL statement is automatically wrapped in its own transaction. This mode is convenient for interactive sessions but often inappropriate for production applications.

Autocommit Advantages

•Simple for ad-hoc queries
•No forgotten uncommitted transactions
•Each statement is immediately durable
•Lower lock hold times for simple operations
•Easier debugging in development

Autocommit Disadvantages

•No multi-statement atomicity
•Cannot rollback partial operations
•Performance overhead per statement
•Related operations can diverge
•False sense of transaction safety

autocommit_behavior.sql

-- Check current autocommit status (in psql)
\echo :AUTOCOMMIT
 
-- Toggle autocommit in psql
\set AUTOCOMMIT off
-- Now each statement doesn't auto-commit
UPDATE accounts SET balance = 100 WHERE id = 1;
-- Changes are NOT yet permanent
COMMIT;  -- Now they're permanent
 
-- Application code typically uses connection settings
-- or explicit BEGIN to override autocommit

DDL Commits Automatically

In MySQL and Oracle, DDL statements (CREATE, ALTER, DROP) cause an implicit commit of any open transaction BEFORE and AFTER execution. This means you cannot rollback a CREATE TABLE, and any pending DML is committed when DDL executes. PostgreSQL supports transactional DDL.

Oracle's Implicit Transaction Start: Oracle is unique among major databases: it never uses autocommit by default. Instead: The first DML statement automatically starts a transaction The transaction remains open until explicit COMMIT or ROLLBACK DDL statements cause implicit commit before and after execution Disconnecting with uncommitted changes causes automatic rollback (in most clients) This behavior requires developers to be more conscious of transaction boundaries but also provides natural grouping of related operations.

Boundary Management in Application Code

Real applications manage transaction boundaries through database drivers and frameworks, not raw SQL. Understanding how your programming language and framework handle boundaries is essential for correct transaction behavior.

application_boundaries.ts
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
import { PrismaClient } from '@prisma/client';
 
// Prisma transaction block - automatic boundary management
async function transferFunds(fromId: string, toId: string, amount: number) {
    // $transaction creates explicit boundaries
    // If any operation throws, entire transaction rolls back
    return await prisma.$transaction(async (tx) => {
        // All operations use 'tx' client, not 'prisma'
        const sender = await tx.account.update({
            where: { id: fromId },
            data: { balance: { decrement: amount } },
        });
        
        if (sender.balance < 0) {
            throw new Error('Insufficient funds');
            // Transaction will rollback automatically
        }
        
        const receiver = await tx.account.update({
            where: { id: toId },
            data: { balance: { increment: amount } },
        });
        
        await tx.transferLog.create({
            data: { fromId, toId, amount, timestamp: new Date() },
        });
        
        return { sender, receiver };
    }); // COMMIT happens here if no error
}
 
// Interactive transaction with explicit timeout
await prisma.$transaction(
    async (tx) => {
        // Long-running operations
    },
    {
        maxWait: 5000,  // Max time to wait for transaction slot
        timeout: 10000, // Max transaction duration
        isolationLevel: 'Serializable',
    }
);

Framework Default Behavior

Each framework has default transaction behavior. Spring's @Transactional uses REQUIRED propagation (join existing or create new) and READ_COMMITTED isolation by default. Prisma's $transaction creates SERIALIZABLE transactions. Always know your framework's defaults and override when necessary.

Boundary Strategies

Choosing where to place transaction boundaries is a critical design decision. Several strategies exist, each with distinct advantages and appropriate use cases.

Transaction Boundary Strategies
Strategy	Description	Best For	Watch Out For
Per-Request	One transaction per HTTP request/RPC call	Web applications with simple request-response patterns	Long-running requests hold locks; background jobs may conflict
Per-Use-Case	Transaction matches business operation (e.g., 'transfer funds')	Domain-driven design; clear business operations	May need multiple transactions for complex operations
Per-Aggregate	Transaction covers one aggregate root in DDD	Microservices; event-sourced systems	Cross-aggregate consistency requires sagas or 2PC
Per-Statement	Each statement is its own transaction (autocommit)	Simple queries; read-heavy workloads	No atomicity across operations; data can diverge
Long Transaction	Transaction spans user think-time (pessimistic locking)	Reservation systems; exclusive editing	Scalability issues; deadlock risk; abandoned transactions

The Per-Use-Case Strategy in Depth: The most maintainable approach is often per-use-case boundaries, where each transaction corresponds to exactly one business operation: Use Case: Transfer Funds Transaction Scope: 1. Validate sufficient balance 2. Debit source account 3. Credit destination account 4. Record transfer in audit log 5. Commit This approach has several benefits: Clear semantics: Developers understand what's atomic Natural error handling: Failure rolls back the entire business operation Appropriate scope: Not too narrow (losing atomicity) or too wide (holding locks too long) Testable: Use cases can be tested in isolation

The Danger of Long Transactions

Avoid 'conversational' transactions that span user think-time. If a transaction starts at 'Begin Edit' and ends at 'Save', a user who goes to lunch leaves locks held for hours. Use optimistic concurrency control instead: read data without locking, detect conflicts at commit time.

Savepoints for Partial Rollback

Sometimes you need finer-grained control within a transaction. Savepoints create checkpoints that allow rolling back to an intermediate state without aborting the entire transaction.

savepoints.sql
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
-- Savepoint example: Order processing with fallback
BEGIN;
 
-- Step 1: Update order status
UPDATE orders SET status = 'processing' WHERE id = 101;
 
-- Create savepoint before risky operation
SAVEPOINT before_payment;
 
-- Step 2: Attempt to process payment
UPDATE payments SET status = 'charged', charged_at = NOW() 
WHERE order_id = 101;
 
-- Simulate payment failure scenario
-- In real code, this check would be application logic
-- If payment_gateway_error THEN
    ROLLBACK TO SAVEPOINT before_payment;
    -- Order status change is preserved!
    -- Only payment changes are undone
    UPDATE orders SET status = 'payment_failed' WHERE id = 101;
-- END IF;
 
-- Create another savepoint
SAVEPOINT before_shipping;
 
-- Step 3: Initiate shipping
UPDATE shipping SET status = 'initiated' WHERE order_id = 101;
 
-- If shipping unavailable, can rollback to before_shipping
-- while keeping payment changes
 
COMMIT;  -- Commits everything not rolled back

Savepoint Use Cases

•Try alternate strategies: Attempt operation A, if it fails, rollback and try operation B
•Batch processing with recovery: Process 100 records, mark savepoint every 10; if record 35 fails, rollback only to record 30's savepoint
•Optional sub-operations: Execute core logic, attempt enhancements (like sending notifications), rollback enhancements if they fail without affecting core
•Testing scenarios: In test code, create savepoint, run destructive test, rollback to savepoint to restore state

Savepoints Are Not Commits

Savepoints don't make changes durable—they're just markers within an active transaction. If the entire transaction rolls back (or the system crashes), all changes since BEGIN are lost, regardless of savepoints. Only COMMIT makes changes permanent.

Nested Transactions vs. Savepoints: Some databases (like SQL Server) support 'nested transactions' syntax, but these are effectively savepoints: BEGIN TRANSACTION Outer; -- operations BEGIN TRANSACTION Inner; -- more operations COMMIT TRANSACTION Inner; -- Only releases savepoint! -- We're still in Outer transaction ROLLBACK TRANSACTION Outer; -- Undoes EVERYTHING including 'committed' Inner True nested transactions (where inner commits are durable even if outer rolls back) are rare. PostgreSQL offers them via autonomous transactions in PL/pgSQL, and Oracle supports PRAGMA AUTONOMOUS_TRANSACTION in PL/SQL.

Common Boundary Mistakes

Transaction boundary errors are among the most insidious bugs—they often pass testing but cause data corruption in production. Here are the patterns to avoid:

Anti-Patterns to Avoid

•Assuming Transaction Exists — Writing code that expects to be in a transaction without verifying. In autocommit mode, each statement commits independently. Always explicitly start transactions or use framework mechanisms that guarantee them.
•Forgotten Commit/Rollback — Starting a transaction but not ending it. The connection remains 'in transaction' state, holding locks. Subsequent operations may block indefinitely or join the existing (stale) transaction.
•Transaction Spanning User Input — Starting a transaction, then waiting for user input, then committing. User delays hold locks for unbounded time. Use optimistic locking: read without transaction, detect conflicts at write time.
•Network Calls Inside Transactions — Making external API calls within transaction boundaries. If the API is slow or fails, your transaction holds locks the entire time. Move external calls outside the transaction when possible.
•Inconsistent Boundary Levels — Mixing boundary strategies in the same codebase. Some methods start their own transactions, others assume they're in one. Use consistent patterns and make transaction boundaries explicit in method signatures or documentation.
•Silent Autocommit Changes — Framework or configuration changes enabling/disabling autocommit without developer awareness. Code that worked with explicit transactions may corrupt data with autocommit on.

boundary_mistakes.ts
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
// ❌ WRONG: Assumes caller started transaction
async function updateAccountBalance(id: string, amount: number) {
    // If autocommit is on, this is its own transaction!
    await prisma.account.update({
        where: { id },
        data: { balance: { increment: amount } },
    });
}
 
// ❌ WRONG: Transaction spans external call
async function processOrderBad(orderId: string) {
    await prisma.$transaction(async (tx) => {
        const order = await tx.order.update({
            where: { id: orderId },
            data: { status: 'processing' },
        });
        
        // External API call - if slow, transaction holds locks!
        const shipping = await shippingApi.createLabel(order);
        
        await tx.order.update({
            where: { id: orderId },
            data: { shippingLabel: shipping.label },
        });
    });
}
 
// ✅ CORRECT: Explicit transaction, external calls outside
async function processOrderGood(orderId: string) {
    // Step 1: Transaction for marking as processing
    const order = await prisma.$transaction(async (tx) => {
        return tx.order.update({
            where: { id: orderId },
            data: { status: 'processing' },
        });
    });
    
    // Step 2: External call OUTSIDE transaction
    const shipping = await shippingApi.createLabel(order);
    
    // Step 3: Another transaction to save result
    await prisma.$transaction(async (tx) => {
        await tx.order.update({
            where: { id: orderId },
            data: { 
                shippingLabel: shipping.label,
                status: 'shipped',
            },
        });
    });
}

Transaction Boundary Testing

Write tests that specifically exercise transaction behavior: test that failures cause rollbacks, that concurrent access doesn't corrupt data, that commits actually persist. Use database connections in test mode to verify isolation.

Transaction Boundaries

Defining Where Transactions Begin and End

Understanding Transaction Boundaries

Explicit Boundary Definition in SQL

Implicit Boundaries and Autocommit

Boundary Management in Application Code

Boundary Strategies

Savepoints for Partial Rollback

Common Boundary Mistakes

There’s more to understand.

Transaction Boundaries

Defining Where Transactions Begin and End

Understanding Transaction Boundaries

Explicit Boundary Definition in SQL

Implicit Boundaries and Autocommit

Boundary Management in Application Code

Boundary Strategies

Savepoints for Partial Rollback

Common Boundary Mistakes

There’s more to understand.