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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
-- 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
43
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
 
// 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.

Summary: Transaction Boundaries

Transaction boundaries determine the scope of atomicity—which operations succeed or fail together. Let's consolidate the key concepts:

Key Takeaways

•Boundaries can be explicit or implicit — Explicit boundaries (BEGIN/COMMIT) give control; implicit boundaries (autocommit) provide convenience at the cost of multi-statement atomicity.
•Each database has its own syntax — PostgreSQL and MySQL use BEGIN, SQL Server uses BEGIN TRANSACTION, Oracle starts implicitly with first DML. Know your database's conventions.
•Autocommit is the default trap — Most databases default to autocommit mode. Each statement is its own transaction, which breaks multi-statement consistency if you're not careful.
•Frameworks provide boundary abstractions — @Transactional, $transaction, context managers—use them correctly and understand their defaults.
•Choose boundaries per use case — Transactions should correspond to complete business operations, not arbitrary code structure.
•Savepoints enable partial rollback — When you need checkpoints within a transaction, savepoints let you undo to specific points without aborting entirely.
•Avoid common mistakes — Forgotten commits, network calls in transactions, and spanning user input are patterns that lead to subtle, catastrophic bugs.

What's Next:

With boundaries understood, the next page covers the control statements in detail: BEGIN, COMMIT, and ROLLBACK. We'll explore their exact semantics, variations, and the mechanics of how they instruct the database to manage transaction state.

Page Complete

You now understand how to define and manage transaction boundaries—the crucial decisions that determine what operations are grouped together atomically. This knowledge is essential for writing correct, robust database applications.

2 / 5

Loading learning content...

Database Management SystemTransaction Definition

Transaction Definition

LevelIntermediate

Duration60 mins

TopicTransaction Definition

2 / 5

Transaction Boundaries

Defining Where Transactions Begin and End

What You Will Learn

Understanding Transaction Boundaries

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

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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

Implicit Boundaries and Autocommit

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
1
2
3
4
5
6
7
8
9
10
11
12
-- 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

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

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
43
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
 
// 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

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

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

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

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

Summary: Transaction Boundaries

Transaction boundaries determine the scope of atomicity—which operations succeed or fail together. Let's consolidate the key concepts:

Key Takeaways

•Boundaries can be explicit or implicit — Explicit boundaries (BEGIN/COMMIT) give control; implicit boundaries (autocommit) provide convenience at the cost of multi-statement atomicity.
•Each database has its own syntax — PostgreSQL and MySQL use BEGIN, SQL Server uses BEGIN TRANSACTION, Oracle starts implicitly with first DML. Know your database's conventions.
•Autocommit is the default trap — Most databases default to autocommit mode. Each statement is its own transaction, which breaks multi-statement consistency if you're not careful.
•Frameworks provide boundary abstractions — @Transactional, $transaction, context managers—use them correctly and understand their defaults.
•Choose boundaries per use case — Transactions should correspond to complete business operations, not arbitrary code structure.
•Savepoints enable partial rollback — When you need checkpoints within a transaction, savepoints let you undo to specific points without aborting entirely.
•Avoid common mistakes — Forgotten commits, network calls in transactions, and spanning user input are patterns that lead to subtle, catastrophic bugs.

What's Next:

Page Complete

2 / 5