Spring Boot's open-in-view Default: The Silent Path to HikariCP Pool Exhaustion on PostgreSQL

// You have seen this in your Spring Boot startup logs.
// Most of us scroll past it. That is about to change.

2026-03-05 09:14:02.341  WARN 1 --- [main] JpaBaseConfiguration$JpaWebConfiguration :

  spring.jpa.open-in-view is enabled by default.
  Therefore, database queries may be performed during view rendering.
  Explicitly configure spring.jpa.open-in-view to disable this warning.

// Spring Boot has been warning you since version 2.0 (March 2018).
// It is the only default that ships with a built-in apology.

// What open-in-view=true actually does:
//
// 1. OpenEntityManagerInViewInterceptor registers as a Spring MVC interceptor
// 2. Before the controller method runs, it opens an EntityManager (and a Session)
// 3. That Session binds to the current thread via a TransactionSynchronizationManager
// 4. The Session holds a JDBC connection (from HikariCP)
// 5. The connection is held until the HTTP response is FULLY rendered and sent
//
// Timeline for a typical request:
//
//  |--- Controller ---|--- Service ---|--- External API ---|--- View ---|
//  t=0ms              t=5ms           t=8ms                 t=2008ms     t=2015ms
//  ^                                                                     ^
//  |-- JDBC connection acquired here                  returned here ----|
//  |                                                                     |
//  |  Connection held for 2,015ms                                        |
//  |  Actual database work: ~10ms                                        |
//  |  Time spent idle in transaction: ~2,000ms                           |
//
// The connection is not "leaked." It is doing exactly what OSIV asked.
// The problem is that OSIV asks for something unreasonable.

// Simplified from Spring's actual OpenEntityManagerInViewInterceptor.
// The real source is in spring-orm, ~80 lines. This is the essence.

public class OpenEntityManagerInViewInterceptor implements AsyncWebRequestInterceptor {

    @Override
    public void preHandle(WebRequest request) {
        // Creates a new EntityManager (which wraps a Hibernate Session)
        EntityManagerFactory emf = obtainEntityManagerFactory();
        EntityManager em = createEntityManager();

        // Binds it to the current thread — any @Autowired repository
        // or @PersistenceContext injection on this thread will use THIS em.
        EntityManagerHolder emHolder = new EntityManagerHolder(em);
        TransactionSynchronizationManager.bindResource(emf, emHolder);

        // The EntityManager is now open. If any code on this thread
        // touches a JPA entity, the Session will acquire a JDBC connection
        // from HikariCP. That connection will not be returned until
        // afterCompletion() runs — after the response is fully sent.
    }

    @Override
    public void afterCompletion(WebRequest request, Exception ex) {
        // This runs AFTER the response body has been written to the socket.
        EntityManagerHolder emHolder = (EntityManagerHolder)
            TransactionSynchronizationManager.unbindResource(obtainEntityManagerFactory());

        // NOW the EntityManager closes. NOW the Session closes.
        // NOW the JDBC connection returns to HikariCP.
        EntityManagerFactoryUtils.closeEntityManager(emHolder.getEntityManager());
    }
}

// The gap between preHandle() and afterCompletion() is the entire HTTP
// request lifecycle. Every millisecond of controller logic, service calls,
// external API calls, JSON serialization, and response writing.
// The JDBC connection is held for all of it.

@RestController
@RequestMapping("/api/orders")
public class OrderController {

    @Autowired private OrderRepository orderRepository;
    @Autowired private PaymentGatewayClient paymentClient;
    @Autowired private ShippingService shippingService;

    @GetMapping("/{id}")
    public OrderDetailResponse getOrder(@PathVariable Long id) {
        // OSIV has already opened a Session and acquired a JDBC connection.

        Order order = orderRepository.findById(id).orElseThrow();
        // Connection used: ~2ms for a primary key lookup.

        PaymentStatus status = paymentClient.getStatus(order.getPaymentId());
        // Connection idle: 150-800ms waiting on Stripe's API.
        // pg_stat_activity shows: state = 'idle in transaction'
        // Locks from the SELECT above? Still held.

        ShippingEstimate estimate = shippingService.calculate(order);
        // Connection idle: 50-300ms waiting on FedEx/UPS API.
        // Total idle time climbing.

        return new OrderDetailResponse(order, status, estimate);
        // View rendering begins. If order.getItems() triggers a lazy load,
        // THAT is why OSIV exists — to allow lazy loading in the view layer.
        // But the price is holding the connection for the entire request.
    }
}
// With OSIV enabled, this endpoint holds a connection for 200-1100ms.
// With 10 connections in HikariCP (the default), you can serve
// at most 9-50 concurrent requests before the pool is exhausted.
// That is not a typo. Nine to fifty.

-- What OSIV looks like from PostgreSQL's perspective
-- Run this during normal traffic to see the damage:

SELECT pid,
       state,
       now() - state_change AS time_in_state,
       now() - xact_start AS transaction_age,
       LEFT(query, 100) AS last_query,
       application_name
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND application_name = 'HikariPool-1'
  AND state != 'idle'
ORDER BY time_in_state DESC;

-- Typical output with OSIV enabled during normal load:
--
-- pid   | state                | time_in_state | transaction_age | last_query
-- ------+----------------------+---------------+-----------------+--------------------------
-- 14923 | idle in transaction  | 00:00:01.847  | 00:00:01.851    | SELECT ... FROM orders ...
-- 14891 | idle in transaction  | 00:00:01.203  | 00:00:01.208    | SELECT ... FROM orders ...
-- 14887 | idle in transaction  | 00:00:00.956  | 00:00:00.961    | SELECT ... FROM users ...
-- 14902 | idle in transaction  | 00:00:00.744  | 00:00:00.748    | SELECT ... FROM orders ...
-- 14916 | idle in transaction  | 00:00:00.531  | 00:00:00.536    | SELECT ... FROM products ...
-- 14894 | idle in transaction  | 00:00:00.412  | 00:00:00.418    | SELECT ... FROM orders ...
-- 14909 | idle in transaction  | 00:00:00.287  | 00:00:00.291    | SELECT ... FROM users ...
-- 14885 | active               | 00:00:00.003  | 00:00:00.008    | SELECT ... FROM orders ...
-- 14930 | active               | 00:00:00.001  | 00:00:00.004    | SELECT ... FROM products ...
-- 14898 | idle                 | 00:00:12.441  | (null)          | DISCARD ALL
--
-- 7 out of 10 connections sitting "idle in transaction."
-- The database is doing nothing. The connections are doing nothing.
-- But they are HELD. No other thread can use them.
-- The two "active" connections are the only ones doing actual work.

// HikariCP metrics during OSIV-induced pool exhaustion
// (exposed via Spring Boot Actuator at /actuator/metrics)

// HEALTHY pool (no OSIV, or fast endpoints only):
// hikaricp.connections.active:    2
// hikaricp.connections.idle:      8
// hikaricp.connections.pending:   0
// hikaricp.connections.timeout:   0
// hikaricp.connections.usage.p99: 8ms

// OSIV pool under moderate load (20 req/sec with external API calls):
// hikaricp.connections.active:    10    <-- ALL connections in use
// hikaricp.connections.idle:      0     <-- none available
// hikaricp.connections.pending:   14    <-- 14 threads BLOCKED waiting
// hikaricp.connections.timeout:   847   <-- 847 requests have FAILED
// hikaricp.connections.usage.p99: 1,847ms  <-- connections held ~2 seconds

// The pending metric is the pain metric.
// Each pending thread is a user staring at a spinner.
// Each timeout is a user seeing a 500 error.

// The stack trace in the timeout exception:
// java.sql.SQLTransientConnectionException:
//   HikariPool-1 - Connection is not available,
//   request timed out after 30000ms.
//   at com.zaxxer.hikari.pool.HikariPool.createTimeoutException
//   at com.zaxxer.hikari.pool.HikariPool.getConnection
//   at ...OpenEntityManagerInViewInterceptor.preHandle  <-- OSIV

-- Using pg_stat_statements to measure the OSIV tax
-- This query shows the gap between query execution time and
-- connection hold time for your most expensive queries.

SELECT
    LEFT(query, 80) AS query,
    calls,
    ROUND(total_exec_time::numeric, 1) AS total_ms,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(max_exec_time::numeric, 1) AS max_ms,
    rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'mydb')
ORDER BY total_exec_time DESC
LIMIT 10;

-- Typical output:
-- query                                        | calls | total_ms | mean_ms | max_ms | rows
-- ---------------------------------------------+-------+----------+---------+--------+------
-- SELECT o.* FROM orders o WHERE o.id = $1     | 84201 |   1247.3 |    0.01 |   2.4  | 84201
-- SELECT i.* FROM order_items i WHERE i.orde.. | 84201 |    987.6 |    0.01 |   1.8  | 241k
-- SELECT u.* FROM users u WHERE u.id = $1      | 31044 |    412.8 |    0.01 |   3.1  | 31044
-- SELECT p.* FROM products p WHERE p.id = $1   | 241k  |   3841.2 |    0.02 |   4.7  | 241k

-- These queries are FAST. Mean execution: 0.01-0.02ms.
-- PostgreSQL is doing its job admirably. The queries themselves are fine.

-- But look at the connection-level metrics from HikariCP:
-- hikaricp.connections.usage.mean: 847ms
-- hikaricp.connections.usage.p99:  2,140ms

-- The queries take 0.01ms. The connections are held for 847ms.
-- That ratio — 0.01ms query time vs 847ms hold time — is OSIV.
-- The connection is idle for 99.999% of the time it is checked out.

-- The difference in query plans is not the problem.
-- The queries themselves are identical with or without OSIV.
-- What changes is WHEN the connection is held.

-- But OSIV often masks N+1 queries by making lazy loading invisible.
-- Without OSIV, you would have seen LazyInitializationException
-- and been forced to write a proper JOIN FETCH. With OSIV, Hibernate
-- silently fires these queries during JSON serialization:

-- Request to GET /api/orders?status=PENDING (returns 25 orders)

-- Query 1: Load orders
EXPLAIN ANALYZE
SELECT o.id, o.status, o.created_at, o.customer_id
FROM orders o
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC
LIMIT 25;

-- Planning Time: 0.142 ms
-- Execution Time: 0.387 ms
--
-- Index Scan using idx_orders_status_created on orders o
--   Index Cond: (status = 'PENDING')
--   Rows Removed by Filter: 0
--   Actual Rows: 25
--   Buffers: shared hit=28

-- Then, during Jackson serialization (with OSIV keeping the Session open),
-- accessing order.getItems() on each of the 25 orders fires:

-- Queries 2-26: One per order (the N+1 pattern)
EXPLAIN ANALYZE
SELECT i.id, i.order_id, i.product_id, i.quantity, i.unit_price
FROM order_items i
WHERE i.order_id = 42;   -- repeated 25 times with different IDs

-- Planning Time: 0.089 ms
-- Execution Time: 0.134 ms (per query)
--
-- Index Scan using idx_order_items_order_id on order_items i
--   Index Cond: (order_id = 42)
--   Actual Rows: 3
--   Buffers: shared hit=4

-- Total: 1 + 25 = 26 queries
-- Total execution time: 0.387 + (25 * 0.134) = 3.737ms of DB time
-- Total connection hold time with OSIV: ~850ms (includes serialization)
-- Connection utilization: 0.4%

-- The fix (one query with JOIN FETCH):
EXPLAIN ANALYZE
SELECT o.id, o.status, o.created_at, o.customer_id,
       i.id, i.product_id, i.quantity, i.unit_price
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'PENDING'
ORDER BY o.created_at DESC;

-- Planning Time: 0.298 ms
-- Execution Time: 0.912 ms
--
-- Sort
--   Sort Key: o.created_at DESC
--   Sort Method: quicksort  Memory: 42kB
--   -> Hash Right Join
--        Hash Cond: (i.order_id = o.id)
--        -> Seq Scan on order_items i
--              Actual Rows: 847
--        -> Hash
--              -> Index Scan using idx_orders_status_created on orders o
--                    Index Cond: (status = 'PENDING')
--                    Actual Rows: 25
--                    Buffers: shared hit=28

-- 1 query instead of 26. 0.912ms instead of 3.737ms.
-- But the real win: connection held for 1ms, not 850ms.

# application.yml — the one-line fix
spring:
  jpa:
    open-in-view: false

# Or application.properties:
# spring.jpa.open-in-view=false

# That is it. One property. The warning disappears.
# The connection is no longer held for the entire request lifecycle.
#
# What changes:
# - The Session/EntityManager is scoped to @Transactional boundaries only
# - JDBC connections are acquired when a transaction starts
# - JDBC connections are returned when the transaction commits/rollbacks
# - Lazy loading outside a transaction throws LazyInitializationException
#
# That last point is the reason OSIV exists in the first place.
# It is also the reason disabling it requires code changes.

// Connection timeline comparison: OSIV=true vs OSIV=false
// Same endpoint, same code, same external API calls.

// ═══════════════════════════════════════════════════════════════════
// OSIV=true (default)
// ═══════════════════════════════════════════════════════════════════
//
// Thread-1:  [===CONN HELD====================================]
//            |  DB  |    Stripe API    |  FedEx  | DB |  JSON  |
//            0ms    3ms               503ms     803ms 805ms  810ms
//
// Connection hold time: 810ms
// Database work:         5ms
// Idle-in-transaction: 805ms
// Pool connections used: 1 for 810ms
//
// At 20 req/sec, you need: 20 * 0.810 = 16.2 connections
// HikariCP default pool: 10
// Result: pool exhaustion at 12 req/sec

// ═══════════════════════════════════════════════════════════════════
// OSIV=false (recommended)
// ═══════════════════════════════════════════════════════════════════
//
// Thread-1:  [=CONN=]                              [=CONN=]
//            | DB   |    Stripe API    |  FedEx  | DB   |  JSON  |
//            0ms    3ms               503ms     803ms 805ms  810ms
//
// Connection hold time: 3ms + 2ms = 5ms (two short checkouts)
// Database work:         5ms
// Idle-in-transaction:   0ms
// Pool connections used: 1 for 5ms total
//
// At 20 req/sec, you need: 20 * 0.005 = 0.1 connections
// HikariCP default pool: 10
// Result: pool is 99% idle at 20 req/sec
// Result: pool handles 2,000 req/sec before saturation

// Before: OSIV masks lazy loading problems
@RestController
public class OrderController {

    @GetMapping("/orders/{id}")
    public OrderResponse getOrder(@PathVariable Long id) {
        Order order = orderService.findById(id);  // @Transactional
        // Transaction is committed. Connection returned to pool.

        // With OSIV=true, this works — OSIV keeps the Session open.
        // With OSIV=false, this throws LazyInitializationException:
        List<OrderItem> items = order.getItems();  // LAZY collection
        return new OrderResponse(order, items);
    }
}

// After: explicit fetching in the service layer
@Service
public class OrderService {

    @Transactional(readOnly = true)
    public OrderWithItems findByIdWithItems(Long id) {
        Order order = orderRepository.findById(id).orElseThrow();
        // Force initialization inside the transaction:
        Hibernate.initialize(order.getItems());
        // Or use a fetch join query:
        // Order order = orderRepository.findByIdWithItems(id);
        return new OrderWithItems(order, order.getItems());
    }
}

// Repository with explicit fetch join:
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.id = :id")
    Optional<Order> findByIdWithItems(@Param("id") Long id);

    // Or use an EntityGraph:
    @EntityGraph(attributePaths = {"items", "items.product"})
    Optional<Order> findWithItemsById(Long id);
}

// EntityGraph: the surgical alternative to JOIN FETCH
// Define the graph on the entity:

@Entity
@NamedEntityGraph(
    name = "Order.withItemsAndProducts",
    attributeNodes = {
        @NamedAttributeNode(value = "items", subgraph = "items-subgraph")
    },
    subgraphs = {
        @NamedSubgraph(
            name = "items-subgraph",
            attributeNodes = { @NamedAttributeNode("product") }
        )
    }
)
public class Order {
    @Id
    private Long id;

    @OneToMany(mappedBy = "order", fetch = FetchType.LAZY)
    private List<OrderItem> items;

    // ...
}

// Use it in the repository:
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Method 1: annotation-based EntityGraph
    @EntityGraph(value = "Order.withItemsAndProducts", type = EntityGraph.EntityGraphType.LOAD)
    Optional<Order> findById(Long id);

    // Method 2: ad-hoc EntityGraph for a specific query
    @EntityGraph(attributePaths = {"items", "items.product", "customer"})
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    List<Order> findByStatusWithDetails(@Param("status") String status);
}

// What Hibernate generates for the EntityGraph:
//
// SELECT o.id, o.status, o.created_at,
//        i.id, i.quantity, i.price,
//        p.id, p.name, p.sku
// FROM orders o
// LEFT JOIN order_items i ON i.order_id = o.id
// LEFT JOIN products p ON p.id = i.product_id
// WHERE o.id = ?
//
// One query. All related data loaded. No N+1. No OSIV required.

// DTO projections: the cleanest exit from OSIV dependency
// Instead of returning entities (which have lazy associations),
// return exactly the data you need.

// Spring Data JPA interface projection:
public interface OrderSummary {
    Long getId();
    String getStatus();
    LocalDateTime getCreatedAt();
    String getCustomerName();   // derived from JOIN
    int getItemCount();         // derived from COUNT
    BigDecimal getTotalAmount(); // derived from SUM
}

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("""
        SELECT o.id AS id,
               o.status AS status,
               o.createdAt AS createdAt,
               c.name AS customerName,
               COUNT(i.id) AS itemCount,
               SUM(i.quantity * i.unitPrice) AS totalAmount
        FROM Order o
        JOIN o.customer c
        LEFT JOIN o.items i
        WHERE o.status = :status
        GROUP BY o.id, o.status, o.createdAt, c.name
        ORDER BY o.createdAt DESC
        """)
    List<OrderSummary> findOrderSummariesByStatus(@Param("status") String status);
}

// What Hibernate generates:
// SELECT o.id, o.status, o.created_at, c.name,
//        COUNT(i.id), SUM(i.quantity * i.unit_price)
// FROM orders o
// JOIN customers c ON c.id = o.customer_id
// LEFT JOIN order_items i ON i.order_id = o.id
// WHERE o.status = 'PENDING'
// GROUP BY o.id, o.status, o.created_at, c.name
// ORDER BY o.created_at DESC

// Benefits:
// - No entity returned = no lazy associations = no OSIV needed
// - SELECT only the columns you need = less data over the wire
// - Aggregation in SQL = no application-side loop to sum items
// - Immutable projection = thread-safe, cacheable
// - Connection held only during the query execution (~1-3ms)

// @Transactional(readOnly = true): a significant optimization

@Service
public class OrderQueryService {

    // Read-only transactions tell Hibernate to skip dirty checking.
    // Hibernate normally compares every loaded entity to its original
    // state at flush time — even if you only read it. ReadOnly skips this.

    @Transactional(readOnly = true)
    public List<OrderSummary> getRecentOrders(String status, int limit) {
        return orderRepository.findOrderSummariesByStatus(status);
    }

    // What readOnly = true actually does:
    // 1. Sets the Session FlushMode to MANUAL (no auto-flush)
    // 2. PostgreSQL receives SET transaction_read_only = on
    // 3. Hibernate skips dirty checking (no snapshot comparison)
    // 4. PostgreSQL can route to read replicas (if your proxy supports it)
    //
    // Performance impact:
    // - Dirty checking on 100 entities: ~0.5-2ms per flush
    // - With readOnly = true: 0ms (no flush, no comparison)
    // - For list endpoints returning 50-500 entities, this saves
    //   measurable CPU time in both Hibernate and PostgreSQL

    // DO NOT use readOnly for methods that write data.
    // PostgreSQL will throw: ERROR: cannot execute INSERT in a read-only transaction
}

// Combine readOnly with DTO projections for maximum efficiency:
// - readOnly = true: no dirty checking, no flush
// - DTO projection: no entity tracking, no lazy associations
// - Result: connection held for query duration only (~1-5ms),
//   zero Hibernate overhead, no OSIV dependency

// The @Transactional antipattern that OSIV enables and conceals:

@Service
public class OrderService {

    @Transactional
    public OrderResult processOrder(CreateOrderRequest request) {
        // 1. Database work: create order (~3ms)
        Order order = orderRepository.save(new Order(request));

        // 2. External API: charge payment (~500ms)
        //    Connection is idle. Transaction is open. Locks are held.
        PaymentResult payment = paymentGateway.charge(request.getPaymentInfo());

        // 3. External API: reserve inventory (~200ms)
        //    Still idle. Still holding the connection.
        inventoryService.reserve(order.getItems());

        // 4. External API: send confirmation email (~100ms)
        //    800ms of idle connection time and counting.
        emailService.sendConfirmation(order);

        // 5. Database work: update order status (~2ms)
        order.setStatus(payment.isSuccess() ? "CONFIRMED" : "FAILED");
        orderRepository.save(order);

        return new OrderResult(order);
    }
    // Total connection hold time: ~805ms
    // Actual database time: ~5ms
    // Connection utilization: 0.6%
}

// The fix: narrow the transaction boundary

@Service
public class OrderService {

    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        return orderRepository.save(new Order(request));
        // Connection held: ~3ms. Released immediately.
    }

    // No @Transactional — no connection held during external calls
    public OrderResult processOrder(CreateOrderRequest request) {
        Order order = createOrder(request);

        PaymentResult payment = paymentGateway.charge(request.getPaymentInfo());
        inventoryService.reserve(order.getItems());
        emailService.sendConfirmation(order);

        return confirmOrder(order.getId(), payment);
    }

    @Transactional
    public OrderResult confirmOrder(Long orderId, PaymentResult payment) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        order.setStatus(payment.isSuccess() ? "CONFIRMED" : "FAILED");
        orderRepository.save(order);
        return new OrderResult(order);
        // Connection held: ~2ms. Released immediately.
    }
}
// Total connection hold time across both transactions: ~5ms
// Same functionality. 160x less connection pressure.

// A common trap when narrowing @Transactional boundaries:
// Spring's @Transactional uses AOP proxies. Self-invocation bypasses the proxy.

@Service
public class OrderService {

    // THIS DOES NOT WORK as expected:
    public OrderResult processOrder(CreateOrderRequest request) {
        Order order = createOrder(request);  // Direct call — no proxy!
        // createOrder() runs WITHOUT a transaction, because Spring's
        // proxy is not involved in self-invocation.
        // ...
    }

    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        return orderRepository.save(new Order(request));
    }
}

// Fix 1: Extract to a separate @Service class (simplest, recommended)

@Service
public class OrderPersistenceService {
    @Transactional
    public Order createOrder(CreateOrderRequest request) {
        return orderRepository.save(new Order(request));
    }

    @Transactional
    public OrderResult confirmOrder(Long orderId, PaymentResult payment) {
        Order order = orderRepository.findById(orderId).orElseThrow();
        order.setStatus(payment.isSuccess() ? "CONFIRMED" : "FAILED");
        return new OrderResult(orderRepository.save(order));
    }
}

@Service
public class OrderService {
    @Autowired private OrderPersistenceService persistence;

    public OrderResult processOrder(CreateOrderRequest request) {
        Order order = persistence.createOrder(request);
        // Connection released. External calls happen here.
        PaymentResult payment = paymentGateway.charge(request.getPaymentInfo());
        inventoryService.reserve(order.getItems());
        emailService.sendConfirmation(order);
        return persistence.confirmOrder(order.getId(), payment);
    }
}

// Fix 2: Inject the bean into itself (works, but reads strangely)
// Fix 3: Use TransactionTemplate for programmatic control (next example)

// TransactionTemplate: programmatic transaction boundaries
// Useful when you need fine-grained control without creating extra classes.

@Service
public class OrderService {

    @Autowired private TransactionTemplate txTemplate;
    @Autowired private OrderRepository orderRepository;

    public OrderResult processOrder(CreateOrderRequest request) {

        // Transaction 1: create the order (connection held ~3ms)
        Order order = txTemplate.execute(status -> {
            return orderRepository.save(new Order(request));
        });

        // No transaction. No connection. External calls are free.
        PaymentResult payment = paymentGateway.charge(request.getPaymentInfo());
        inventoryService.reserve(order.getItems());
        emailService.sendConfirmation(order);

        // Transaction 2: update the order status (connection held ~2ms)
        return txTemplate.execute(status -> {
            Order o = orderRepository.findById(order.getId()).orElseThrow();
            o.setStatus(payment.isSuccess() ? "CONFIRMED" : "FAILED");
            orderRepository.save(o);
            return new OrderResult(o);
        });
    }
}

// TransactionTemplate advantages:
// - No AOP proxy gotchas — transactions are explicit, not annotation-magic
// - Fine-grained control within a single method
// - Clear visual boundary: "this lambda holds a connection, nothing else does"
// - Works with both read-write and read-only transactions:

// Read-only transaction:
txTemplate.setReadOnly(true);
List<Order> orders = txTemplate.execute(status -> {
    return orderRepository.findByStatus("PENDING");
});

# HikariCP leak detection — your best diagnostic tool
spring:
  datasource:
    hikari:
      maximum-pool-size: 10
      leak-detection-threshold: 2000  # 2 seconds (in milliseconds)

# When a connection is held longer than 2 seconds, HikariCP logs:
#
# WARN  com.zaxxer.hikari.pool.ProxyLeakTask -
#   Connection leak detection triggered for conn0: url=jdbc:postgresql://...,
#   stack trace follows:
#   java.lang.Exception: Apparent connection leak detected
#     at com.zaxxer.hikari.pool.ProxyLeakTask.run(ProxyLeakTask.java:35)
#     ...
#     at com.example.OrderController.getOrder(OrderController.java:47)
#     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
#     at org.springframework.web.servlet.FrameworkServlet.service(...)
#     at org.springframework.web.servlet.DispatcherServlet.doDispatch(...)
#     at org.springframework.web.filter.OpenEntityManagerInViewFilter.doFilterInternal(...)
#                                                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
#                                                       OSIV is in the stack trace.
#
# Every single "leak" will trace back to OpenEntityManagerInViewFilter
# or OpenEntityManagerInViewInterceptor. That is not a coincidence.
# That is the diagnosis.

// Hibernate statistics: finding hidden OSIV queries
// Enable in application.yml (dev/staging only — adds overhead):

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true

# Hibernate logs per-session statistics after each request:
#
# Session Metrics {
#     84723 nanoseconds spent acquiring 1 JDBC connections;
#     0 nanoseconds spent releasing 0 JDBC connections;
#     1847291 nanoseconds spent preparing 26 JDBC statements;
#     4921847 nanoseconds spent executing 26 JDBC statements;
#     0 nanoseconds spent executing 0 JDBC batches;
#     0 nanoseconds spent performing 0 L2C puts;
#     0 nanoseconds spent performing 0 L2C hits;
#     0 nanoseconds spent performing 0 L2C misses;
#     1241893 nanoseconds spent executing 1 flushes;
#     0 nanoseconds spent executing 0 partial-flushes;
# }
#
# The telling number: "26 JDBC statements" for a single request.
# 1 query to load the entity, 25 queries from lazy loading during
# serialization. OSIV made all 25 lazy loads possible — and invisible.
#
# With OSIV disabled, you would have seen LazyInitializationException
# on the first lazy access. Instead, you got 25 silent SELECT statements
# that each hold the connection a little longer.

// Programmatic access to statistics:
@Component
public class HibernateStatsLogger implements HandlerInterceptor {

    @Autowired private EntityManagerFactory emf;

    @Override
    public void afterCompletion(HttpServletRequest req, HttpServletResponse res,
                                 Object handler, Exception ex) {
        Statistics stats = emf.unwrap(SessionFactory.class).getStatistics();
        long queryCount = stats.getQueryExecutionCount();
        if (queryCount > 10) {
            log.warn("Request {} executed {} queries — review for N+1",
                     req.getRequestURI(), queryCount);
        }
        stats.clear();
    }
}

-- PostgreSQL-side defense against OSIV: idle_in_transaction_session_timeout
-- This does not fix OSIV. It limits the blast radius.

-- postgresql.conf (or ALTER SYSTEM):
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();

-- Effect: PostgreSQL will TERMINATE any session that sits in
-- 'idle in transaction' state for longer than 30 seconds.

-- What HikariCP sees:
-- PSQLException: FATAL: terminating connection due to
--   idle-in-transaction timeout
--
-- HikariCP detects the broken connection, evicts it from the pool,
-- and creates a replacement. The thread that was using the connection
-- gets an exception. Your application must handle this gracefully.

-- This is a safety net, not a solution. A well-behaved application
-- should never trigger this timeout. But with OSIV enabled and
-- external APIs having a bad day, 30-second idle-in-transaction
-- durations are entirely possible.

-- Recommended settings for OSIV-affected applications:
-- idle_in_transaction_session_timeout = '30s'   -- kill stuck sessions
-- statement_timeout = '30s'                     -- kill runaway queries
-- idle_session_timeout = '10min'                -- kill abandoned sessions (PG 14+)

-- Coordinate with HikariCP:
-- spring.datasource.hikari.max-lifetime should be LESS than
-- idle_session_timeout to ensure HikariCP retires connections
-- before PostgreSQL kills them. Dead connections in the pool
-- cause checkout failures until HikariCP's keepaliveTime or
-- connectionTestQuery detects the problem.

# Spring Boot Actuator + Micrometer: monitoring for OSIV symptoms
# Add to application.yml:

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,hikaricp
  metrics:
    tags:
      application: my-spring-app

# Key metrics to alert on:

# 1. Pool exhaustion warning (alert at > 0 for > 30 seconds)
hikaricp.connections.pending > 0

# 2. Connection usage P99 (alert if > 500ms)
hikaricp.connections.usage{quantile="0.99"} > 500

# 3. Timeout counter (alert on any increment)
hikaricp.connections.timeout > previous_value

# 4. Active connection ratio (alert if > 80% for > 60 seconds)
hikaricp.connections.active / hikaricp.connections.max > 0.8

# Grafana query for connection usage distribution:
# histogram_quantile(0.99,
#   rate(hikaricp_connections_usage_seconds_bucket[5m]))

# A healthy Spring Boot + PostgreSQL application looks like:
#   pending:         0 (always)
#   active/max:      10-30% (brief spikes to 50% are normal)
#   usage.p99:       5-50ms
#   timeout:         0 (ever-increasing = production incident)
#
# An OSIV-affected application looks like:
#   pending:         0-20 (fluctuates with traffic)
#   active/max:      80-100% (sustained)
#   usage.p99:       500-5000ms
#   timeout:         climbing

// Migrating a large Spring Boot application away from OSIV
// A staged approach for applications with 100+ endpoints.

// Phase 1: Instrument (1 day)
// Enable leak detection, add Actuator metrics, identify worst endpoints.
spring:
  datasource:
    hikari:
      leak-detection-threshold: 2000
  jpa:
    open-in-view: true   # Keep OSIV on — we are observing, not changing

// Phase 2: Fix the worst offenders (1-2 weeks)
// With OSIV still ON, refactor the top 5-10 endpoints by connection hold time:
// - Replace lazy loading with JOIN FETCH or EntityGraph
// - Narrow @Transactional boundaries around external API calls
// - Switch to DTO projections where practical
// These fixes work with or without OSIV. They reduce connection hold time NOW.

// Phase 3: Write integration tests for LazyInitializationException (1 week)
// Before disabling OSIV, write tests that verify every endpoint
// returns correct data. When OSIV is disabled, any endpoint that
// relied on lazy loading in the controller/view layer will fail.
// The test failures ARE the migration checklist.

@SpringBootTest
@TestPropertySource(properties = "spring.jpa.open-in-view=false")
class OrderControllerTest {

    @Autowired private MockMvc mockMvc;

    @Test
    void getOrder_returnsItemsWithoutOsiv() throws Exception {
        mockMvc.perform(get("/api/orders/1"))
            .andExpect(status().isOk())
            .andExpect(jsonPath("$.items").isArray())
            .andExpect(jsonPath("$.items.length()").value(greaterThan(0)));
        // If this fails with LazyInitializationException,
        // the service layer needs a JOIN FETCH or EntityGraph.
    }
}

// Phase 4: Disable OSIV (1 hour)
spring:
  jpa:
    open-in-view: false

// Phase 5: Monitor (ongoing)
// Watch HikariCP metrics. Connection usage P99 should drop
// from seconds to milliseconds. Pending should stay at zero.
// If any endpoint throws LazyInitializationException in production,
// the test from Phase 3 missed it — fix and add the test.

// A brief and slightly exasperated history of OSIV in Spring Boot:
//
// 2014: Spring Boot 1.0 ships with open-in-view=true by default.
//       No warning. No documentation of the connection-holding behavior.
//       The "it just works" philosophy applied to lazy loading.
//
// 2016: Vlad Mihalcea (Hibernate developer advocate) publishes
//       "The Open Session in View Anti-Pattern" — the definitive
//       takedown of OSIV. Thousands of developers read it.
//       Spring Boot's default does not change.
//
// 2018: Spring Boot 2.0 adds the WARN log message at startup.
//       The warning says "database queries may be performed during
//       view rendering." It does NOT mention connection holding,
//       pool exhaustion, or idle-in-transaction state.
//       The default remains true.
//
// 2023: Spring Boot 3.x. The warning still appears. The default
//       is still true. The GitHub issue requesting a default change
//       (spring-projects/spring-boot#7107) has 100+ thumbs-up
//       and remains open.
//
// 2026: You are reading this article. The default is still true.
//       It will probably still be true when your grandchildren
//       inherit your Spring Boot applications.

// A note on Spring WebFlux and OSIV
// If you have migrated to Spring WebFlux (reactive), OSIV does not apply.
// WebFlux does not use the Servlet API, so OpenEntityManagerInViewFilter
// and OpenEntityManagerInViewInterceptor are never registered.

// However, WebFlux introduces its own connection-holding patterns:

// Reactive JPA (Hibernate Reactive):
@Transactional
public Mono<OrderResponse> getOrder(Long id) {
    return orderRepository.findById(id)
        .flatMap(order -> {
            // If this calls an external API, the reactive chain
            // does NOT hold a JDBC connection while waiting.
            // Reactive is non-blocking — the thread is released.
            return paymentClient.getStatus(order.getPaymentId())
                .map(status -> new OrderResponse(order, status));
        });
}

// R2DBC (reactive database driver):
// No Hibernate Session. No OSIV. No lazy loading at all.
// Connections are acquired per-query and released immediately.
// This is the reactive model's natural advantage for connection efficiency.

// The catch: Hibernate Reactive is young, limited, and lacks
// many features of traditional Hibernate. R2DBC requires rewriting
// your data layer. Neither is a quick fix for an OSIV problem.
//
// If you are on Spring MVC (the vast majority of Spring Boot apps),
// the fix is spring.jpa.open-in-view=false + explicit fetching.
// Migration to WebFlux/R2DBC is a separate, larger decision
// that should not be motivated solely by connection pool issues.

# Gold Lapel's connection pooling absorbs SOME of the OSIV pressure.
# But let us be honest about what a proxy can and cannot fix.

# Without Gold Lapel (direct to PostgreSQL):
# - 10 app connections held idle-in-transaction by OSIV
# - 10 PostgreSQL backend connections occupied doing nothing
# - 11th request waits 30 seconds, then times out
# - PostgreSQL max_connections consumed by idle sessions

# With Gold Lapel:
spring:
  datasource:
    url: jdbc:postgresql://localhost:6432/mydb   # GL proxy port
    hikari:
      maximum-pool-size: 20    # can be larger — connections go to GL, not Postgres
      connection-timeout: 5000

# - 20 app connections to Gold Lapel (lightweight, multiplexed)
# - Gold Lapel maintains 10-20 backend connections to PostgreSQL
# - OSIV still holds app-side connections for full request duration
# - BUT those are connections to GL, not to PostgreSQL directly
# - PostgreSQL sees only GL's managed backend pool
# - max_connections pressure is eliminated
#
# What GL DOES fix:
# - PostgreSQL connection count stays bounded regardless of app behavior
# - Autoscaling events don't create connection storms
# - Backend connections are recycled efficiently
# - Monitoring shows clean pool utilization on the Postgres side
#
# What GL CANNOT fix:
# - Your app threads are still blocked waiting for connections
# - HikariCP pool is still exhausted at the application layer
# - Latency from pool contention is unchanged
# - The fundamental problem — holding connections during HTTP calls — persists
#
# The real fix is disabling OSIV. Gold Lapel buys you breathing room,
# not absolution.