Database Transactions at LLD Level
Transaction Boundaries in Code
Read aloud is unavailable in this browser.
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.
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.
| 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 |
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
123456789101112131415161718192021222324252627282930313233343536373839404142434445
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.
| 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 |
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475
@Servicepublic 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); }} @Servicepublic 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)); }}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.
- •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.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364
// ❌ ANTI-PATTERN: Auto-commit for related operationspublic 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@Transactionalpublic 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 transactionpublic 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@Transactionalpublic 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}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.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104
// ✅ CORRECT: All related operations in one transaction@Servicepublic 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@Servicepublic 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@Transactionalpublic 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@Transactionalpublic 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@Transactionalpublic 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);}- •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
}
}
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:
- •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.
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.
KEEP LEARNING
There’s more to understand.
You’re reading the introduction. Create a free account to read the complete lesson, save notes and keep track of your progress.