Database Transactions - Learning Module

Loading content...

0/246

Transaction Boundaries in Code

Where Does a Transaction Begin and End?

Knowing that databases provide ACID guarantees is only half the battle. The more challenging question is: where should transaction boundaries be drawn in your application code?

A transaction that's too narrow—spanning only a single database operation—provides no benefit over auto-commit. A transaction that's too broad—staying open for minutes while the user fills out a form—creates contention nightmares and risks running out of database connections.

Transaction boundary design is both an art and a science. It requires understanding your business operations, data consistency requirements, and the performance implications of different scoping decisions. Get it wrong, and you'll face either data corruption from insufficient atomicity or performance degradation from excessive locking.

What You Will Learn

This page teaches you how to identify the correct boundaries for database transactions in application code. You'll learn the principles that guide boundary decisions, common patterns for transaction management, anti-patterns that lead to data corruption or poor performance, and practical techniques for implementing transactions across different architectural styles.

The Unit of Work Principle

The fundamental principle for transaction boundary design is the Unit of Work concept: a transaction should encompass exactly one complete logical business operation that must succeed or fail as a whole.

This principle has several implications:

Identify the business operation: What is the user or system trying to accomplish? Not a technical operation (UPDATE a row), but a business operation (transfer money between accounts).
Enumerate all state changes: What data modifications are required to complete this operation? All of these must be within a single transaction.
Define the consistency requirements: What invariants must hold after the operation completes? The transaction must ensure these are maintained.
Minimize the scope: Include exactly what's needed—no more, no less. Extra operations increase lock duration and contention.

Identifying Transaction Boundaries for Common Operations
Business Operation	Required State Changes	Transaction Scope
User Registration	Insert user + Insert profile + Insert email verification token	Single transaction: all three must succeed or none
Place Order	Insert order + Insert line items + Reserve inventory + Update customer stats	Single transaction: partial orders corrupt inventory tracking
Transfer Funds	Debit source + Credit destination + Record transaction log	Single transaction: money must not appear or disappear
Update User Email	Update email + Invalidate old sessions + Send verification (async)	Transaction: update + session invalidation. Email send is outside transaction.
Archive Old Records	Move records to archive + Delete from active	Single transaction per batch, but can have multiple smaller transactions

Ask the Right Question

When designing transaction boundaries, ask: 'If this operation partially completes and the remaining part fails, is the data in a state that makes sense for the business?' If the answer is no, those parts must be in the same transaction.

Transaction Demarcation Patterns

There are several established patterns for managing transaction boundaries in application code. Each has trade-offs in terms of flexibility, testability, and complexity.

Programmatic demarcation explicitly controls transaction boundaries in code using try-catch-finally blocks or similar constructs. This gives maximum control but requires careful coding.

Advantages:

Complete control over transaction scope
Clear visibility of what's transactional
Works with any framework or library

Disadvantages:

Boilerplate code repeated everywhere
Easy to forget rollback handling
Nesting transactions becomes complex

ProgrammaticDemarcation.java
Java
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
public class OrderService {
    private final DataSource dataSource;
    
    public Order placeOrder(OrderRequest request) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            
            // TRANSACTION BOUNDARY STARTS
            conn.setAutoCommit(false);
            
            // All operations within transaction
            Order order = createOrder(conn, request);
            List<OrderItem> items = createOrderItems(conn, order.getId(), request.getItems());
            reserveInventory(conn, items);
            CustomerStats stats = updateCustomerStats(conn, request.getCustomerId());
            
            // TRANSACTION BOUNDARY ENDS - SUCCESS
            conn.commit();
            
            return order;
            
        } catch (Exception e) {
            // TRANSACTION BOUNDARY ENDS - FAILURE
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException re) {
                    // Log but don't throw - original exception is more important
                    logger.error("Rollback failed", re);
                }
            }
            throw new OrderPlacementException("Failed to place order", e);
        } finally {
            // ALWAYS close the connection
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ce) {
                    logger.error("Failed to close connection", ce);
                }
            }
        }
    }
}

Transaction Propagation

When transactional methods call other transactional methods, the question arises: should the called method join the existing transaction or start its own? This is called transaction propagation, and understanding it is crucial for complex business operations.

Different propagation modes handle this question differently. The right choice depends on whether the operations must succeed or fail together.

Transaction Propagation Modes
Propagation	Existing Transaction	No Transaction	Use When
REQUIRED (default)	Join it	Create new	Default choice - method needs transaction but can share
REQUIRES_NEW	Suspend, create new	Create new	Must commit independently, e.g., audit logging
MANDATORY	Join it	Exception!	Method must be called within existing transaction
SUPPORTS	Join it	Run without	Transaction optional - works either way
NOT_SUPPORTED	Suspend it	Run without	Must run without transaction, e.g., external calls
NEVER	Exception!	Run without	Must never run in transaction
NESTED	Create savepoint	Create new	Can rollback independently but still in parent

PropagationExamples.java
Java
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
@Service
public class OrderProcessingService {
    
    @Autowired
    private OrderService orderService;
    
    @Autowired
    private AuditService auditService;
    
    @Autowired
    private ExternalPaymentGateway paymentGateway;
    
    /**
     * Main order processing - creates the transaction boundary
     */
    @Transactional(propagation = Propagation.REQUIRED)
    public ProcessingResult processOrder(OrderRequest request) {
        
        // Creates order in SAME transaction (REQUIRED joins existing)
        Order order = orderService.createOrder(request);
        
        // Audit must succeed even if order processing fails later
        // REQUIRES_NEW: suspends current tx, creates independent tx
        auditService.logOrderAttempt(request);
        
        // Payment call must NOT be in transaction
        // NOT_SUPPORTED: suspends transaction during external call
        PaymentResult payment = paymentGateway.processPayment(order);
        
        if (payment.isSuccessful()) {
            // This runs in the same transaction as createOrder
            orderService.confirmOrder(order, payment);
        } else {
            // Rollback will undo createOrder, but NOT the audit log
            throw new PaymentFailedException(payment.getErrorCode());
        }
        
        return new ProcessingResult(order, payment);
    }
}
 
@Service
public class AuditService {
    
    /**
     * REQUIRES_NEW: Always runs in its own transaction.
     * If the calling transaction rolls back, this commit is preserved.
     * Critical for audit trails that must not be lost.
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logOrderAttempt(OrderRequest request) {
        AuditEntry entry = new AuditEntry(
            "ORDER_ATTEMPT",
            request.getCustomerId(),
            LocalDateTime.now()
        );
        auditRepository.save(entry);
        // This commits independently!
    }
}
 
@Service  
public class ExternalPaymentGateway {
    
    /**
     * NOT_SUPPORTED: Suspends any existing transaction.
     * External service calls shouldn't hold database transactions open.
     * Network latency would cause connection pool exhaustion.
     */
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public PaymentResult processPayment(Order order) {
        // No database transaction is held open during this call
        return httpClient.post("/payments", buildPaymentRequest(order));
    }
}

Self-Invocation Trap

In proxy-based frameworks like Spring, calling a @Transactional method from within the same class bypasses the proxy—the annotation has no effect! The call goes directly to the method, not through the transactional proxy. Either inject the service into itself, use AspectJ compile-time weaving, or restructure the code with separate classes.

Anti-patterns in Transaction Boundaries

Knowing correct patterns is valuable, but recognizing and avoiding anti-patterns is equally important. These are the common mistakes that lead to data corruption, performance problems, or both.

Dangerous Transaction Anti-patterns

•Auto-commit for Related Operations — Running each INSERT or UPDATE as its own committed transaction when they should be atomic. If step 3 of 5 fails, you have inconsistent data.
•Long-running Transactions — Holding transactions open while waiting for user input, external service calls, or file I/O. This holds locks, blocks other transactions, and exhausts connection pools.
•Transaction Per Request (Too Broad) — Opening a transaction at the start of an HTTP request and committing at the end. Serializes all concurrent requests and creates massive contention.
•Read-Modify-Write Outside Transaction — Reading data in one connection, computing in memory, then writing in a new connection. Another transaction can modify the data between your read and write.
•Swallowing Exceptions — Catching exceptions inside a transaction and continuing. The data may be in an inconsistent state, but the transaction will commit.
•Nested Transactions Without Understanding — Using REQUIRES_NEW without understanding that the new transaction can commit while the outer fails, or that suspended transactions hold resources.

AntiPatternExamples.java
Java
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
// ❌ ANTI-PATTERN: Auto-commit for related operations
public class BrokenOrderService {
    
    public void placeOrder(OrderRequest request) {
        // Each statement auto-commits independently!
        Order order = orderRepository.save(new Order(request)); // COMMIT 1
        
        for (ItemRequest item : request.getItems()) {
            orderItemRepository.save(new OrderItem(order, item)); // COMMIT 2, 3, ...
            inventoryService.reserve(item.getProductId(), item.getQuantity()); // COMMIT N
        }
        // If inventory reserve fails on item 3, order and items 1-2 exist!
        // Result: corrupted data, inventory mismatch
    }
}
 
// ❌ ANTI-PATTERN: Long-running transaction
@Transactional
public void processWithExternalCall(Request request) {
    // Transaction starts
    Data data = repository.findById(request.getId());
    
    // DANGER: External HTTP call inside transaction!
    // Transaction holds database connection for 500ms - 30 seconds
    ExternalResult result = httpClient.call(externalService, data);  // ← Problem
    
    data.setStatus(result.getStatus());
    repository.save(data);
    // Transaction commits after external call completes
}
// Result: Connection pool exhaustion, database contention
 
// ❌ ANTI-PATTERN: Read-Modify-Write without transaction
public void updateBalance_BROKEN(String accountId, BigDecimal amount) {
    // READ - gets current balance
    Account account = accountRepository.findById(accountId);  // Connection 1, auto-commit
    
    // MODIFY - calculate new balance (in memory, no protection)
    BigDecimal newBalance = account.getBalance().add(amount);
    
    // WRITE - save new balance
    account.setBalance(newBalance);
    accountRepository.save(account);  // Connection 2, auto-commit
    
    // PROBLEM: Another transaction could modify balance between
    // our read and write. That modification is LOST.
}
 
// ❌ ANTI-PATTERN: Swallowing exceptions
@Transactional
public void processWithSwallowedException(Request request) {
    Order order = createOrder(request);
    
    for (Item item : request.getItems()) {
        try {
            inventoryService.reserve(item);  // Might throw
        } catch (InsufficientInventoryException e) {
            // "Handle" the exception by ignoring it
            logger.warn("Could not reserve: " + item);  // ← DANGEROUS
            // Transaction continues! Partial data committed.
        }
    }
    // Commits with some items unreserved - broken invariant
}

The Auto-commit Trap

Auto-commit mode (the default in JDBC) commits after every single statement. This is convenient for simple scripts but catastrophic for multi-operation business logic. Always explicitly manage transactions for any operation involving multiple data modifications that must be atomic.

Correct Transaction Boundary Design

Now let's look at the corrected versions of the anti-patterns, along with principles for designing robust transaction boundaries.

CorrectPatterns.java
Java
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
// ✅ CORRECT: All related operations in one transaction
@Service
public class OrderService {
    
    @Transactional
    public Order placeOrder(OrderRequest request) {
        // Single transaction wraps ALL related operations
        Order order = new Order(request.getCustomerId());
        orderRepository.save(order);
        
        for (ItemRequest item : request.getItems()) {
            OrderItem orderItem = new OrderItem(order, item);
            orderItemRepository.save(orderItem);
            inventoryService.reserveWithinTransaction(item.getProductId(), item.getQuantity());
        }
        
        // If ANY operation fails, ALL are rolled back
        return order;
    }
}
 
// ✅ CORRECT: External calls OUTSIDE transaction
@Service
public class OrderProcessingService {
    
    public ProcessingResult process(OrderRequest request) {
        // 1. Validate and prepare (no transaction needed yet)
        validateRequest(request);
        
        // 2. External call BEFORE transaction
        PaymentToken token = paymentGateway.authorize(request.getPaymentInfo());
        
        // 3. Short, focused transaction for data changes only
        Order order = orderService.placeOrder(request, token);  // @Transactional inside
        
        // 4. External call AFTER transaction committed
        paymentGateway.capture(token);
        
        // 5. Non-critical operations outside transaction
        notificationService.sendConfirmation(order);
        
        return new ProcessingResult(order);
    }
}
 
// ✅ CORRECT: Read-Modify-Write within transaction with locking
@Transactional
public void updateBalance(String accountId, BigDecimal amount) {
    // SELECT ... FOR UPDATE locks the row within transaction
    Account account = accountRepository.findByIdForUpdate(accountId);
    
    // Modification is protected by the lock
    BigDecimal newBalance = account.getBalance().add(amount);
    account.setBalance(newBalance);
    
    // Save and commit - lock released
    accountRepository.save(account);
}
 
// Alternative: Optimistic locking with version check
@Transactional
public void updateBalanceOptimistic(String accountId, BigDecimal amount) {
    int retries = 3;
    while (retries > 0) {
        try {
            Account account = accountRepository.findById(accountId);
            // Account has @Version field
            account.setBalance(account.getBalance().add(amount));
            accountRepository.save(account);  // Throws on version mismatch
            return;
        } catch (OptimisticLockException e) {
            retries--;
            if (retries == 0) throw e;
            // Retry with fresh data
        }
    }
}
 
// ✅ CORRECT: Fail fast, don't swallow exceptions
@Transactional
public OrderResult processOrder(Request request) {
    Order order = createOrder(request);
    
    List<ReservationFailure> failures = new ArrayList<>();
    
    for (Item item : request.getItems()) {
        try {
            inventoryService.reserve(item);
        } catch (InsufficientInventoryException e) {
            failures.add(new ReservationFailure(item, e));
        }
    }
    
    // Make explicit decision about failures
    if (!failures.isEmpty()) {
        // Either fail the whole transaction...
        throw new OrderFailedException("Cannot fulfill order", failures);
        
        // ...or explicitly handle partial success (if business allows)
        // return OrderResult.partialSuccess(order, failures);
    }
    
    return OrderResult.success(order);
}

Transaction Boundary Design Principles

•Keep transactions short — Do only database operations inside transactions. Move external calls, complex calculations, and I/O outside.
•One business operation = one transaction — All state changes for a logical operation should be atomic.
•Acquire locks at the start — Use SELECT FOR UPDATE early to prevent concurrent modification during your read-modify-write cycle.
•Fail explicitly — If something goes wrong, throw an exception. Never continue with partial data.
•Use appropriate propagation — Understand when operations should join vs. be independent.
•Test transaction behavior — Write integration tests that verify rollback works correctly.

Transaction Management Across Architectures

Different architectural styles have different natural boundaries for transactions. Understanding these helps you make appropriate choices.

Layered/Clean Architecture

Application/Use Case layer typically owns transaction boundaries:

Controllers handle HTTP, have no transaction knowledge
Use Cases/Services define business operations = transactions
Repositories are called within transactions but don't define them

// Use Case layer - transaction boundary
@Service
public class PlaceOrderUseCase {
    @Transactional
    public Output execute(Input input) {
        // Orchestrates repositories
    }
}

Domain-Driven Design

Aggregate boundaries align with transaction boundaries:

Each aggregate is loaded and saved in one transaction
Repository handles one aggregate at a time
Cross-aggregate operations use domain events

// Repository saves one aggregate per transaction
@Transactional
public void save(OrderAggregate order) {
    // Saves the entire aggregate atomically
    orderRepository.save(order);
    eventPublisher.publish(order.domainEvents());
}

CQRS (Command Query Responsibility Segregation)

In CQRS architecture, transaction boundaries differ between commands and queries:

Commands (Write Side):

Each command handler owns a transaction
Command execution is atomic
May update one or more aggregates

Queries (Read Side):

Usually no transaction needed (read optimized)
Or use read-only transactions for consistency
May query denormalized views

@Service
public class PlaceOrderCommandHandler {
    @Transactional
    public void handle(PlaceOrderCommand cmd) {
        Order order = Order.create(cmd);
        orderRepository.save(order);  // Write side
    }
}

@Service
public class OrderQueryService {
    @Transactional(readOnly = true)  // Read optimization
    public OrderView getOrder(UUID id) {
        return orderViewRepository.findById(id);  // Read side
    }
}

Transaction Boundaries = Business Operations

Regardless of architecture, the principle holds: transaction boundaries should match business operations. In DDD, aggregates are designed to match business invariants—so aggregate boundaries naturally align with transactions. In Clean Architecture, Use Cases represent business operations—so Use Cases own transactions.

Summary: Transaction Boundaries in Code

Transaction boundaries are where ACID theory meets practice. Let's consolidate the key insights:

Key Takeaways

•Unit of Work defines boundaries — A transaction should encompass exactly one complete business operation.
•Choose appropriate demarcation pattern — Programmatic for control, template for reuse, declarative for convenience.
•Understand propagation — Methods joining vs. creating transactions affects atomicity and isolation.
•Avoid the anti-patterns — Auto-commit for related ops, long-running transactions, read-modify-write outside transactions.
•Keep transactions short and focused — Only database operations inside; external calls outside.
•Architecture guides boundaries — Use Cases, Command Handlers, or Aggregate operations typically own transactions.

What's next:

Correct boundaries are essential, but what happens when multiple transactions operate on the same data? The next page dives deep into isolation levels—how databases control concurrent access, the trade-offs between different levels, and how to choose the right isolation for your use case.

Page Complete

You now understand how to correctly define transaction boundaries in application code. You know the patterns, anti-patterns, and principles that guide these decisions. Next, we'll explore how isolation levels control what happens when multiple transactions compete for the same data.