lock stock and two racing parallels

DeadlockHandler.java

public class DeadlockHandler {
    private static final Logger log = LoggerFactory.getLogger(DeadlockHandler.class);
    private final DataSource dataSource;
    private final DeadlockMetrics metrics;
    
    public DeadlockHandler(DataSource dataSource) {
        this.dataSource = dataSource;
        this.metrics = new DeadlockMetrics();
    }
    
    public <T> T executeWithDeadlockHandling(DatabaseOperation<T> operation) throws SQLException {
        int attempts = 0;
        int maxAttempts = 3;
        long backoffMs = 100; // Initial backoff time
        
        while (attempts < maxAttempts) {
            try {
                T result = operation.execute();
                if (attempts > 0) {
                    metrics.recordRetrySuccess();
                }
                return result;
            } catch (SQLException e) {
                attempts++;
                
                if (isDeadlock(e)) {
                    metrics.recordDeadlock();
                    log.warn("Deadlock detected (attempt {}/{})", attempts, maxAttempts);
                    
                    if (attempts == maxAttempts) {
                        throw new DeadlockException("Max retry attempts reached", e);
                    }
                    
                    // Exponential backoff with jitter
                    long jitter = (long) (Math.random() * backoffMs * 0.1);
                    Thread.sleep(backoffMs + jitter);
                    backoffMs *= 2; // Exponential increase
                    
                } else {
                    throw e; // Not a deadlock, rethrow
                }
            }
        }
        
        throw new DeadlockException("Unable to complete operation after " + maxAttempts + " attempts");
    }
    
    private boolean isDeadlock(SQLException e) {
        // Oracle deadlock error code: ORA-00060
        // PostgreSQL deadlock error code: 40P01
        return e.getErrorCode() == 60 || 
               "40P01".equals(e.getSQLState());
    }
    
    public class DeadlockException extends SQLException {
        public DeadlockException(String message) {
            super(message);
        }
        
        public DeadlockException(String message, Throwable cause) {
            super(message, cause);
        }
    }
    
    // Usage example with different transaction patterns
    public void transferInventory(String fromProduct, String toProduct, int quantity) {
        executeWithDeadlockHandling(() -> {
            try (Connection conn = dataSource.getConnection()) {
                conn.setAutoCommit(false);
                
                // Consistent ordering of locks to prevent deadlocks
                String firstProduct = fromProduct.compareTo(toProduct) < 0 ? 
                                    fromProduct : toProduct;
                String secondProduct = fromProduct.compareTo(toProduct) < 0 ? 
                                     toProduct : fromProduct;
                
                // Lock acquisition in consistent order
                try (PreparedStatement stmt1 = conn.prepareStatement(
                        "SELECT inventory_count FROM product_inventory " +
                        "WHERE product_id = ? FOR UPDATE")) {
                    stmt1.setString(1, firstProduct);
                    stmt1.executeQuery();
                    
                    try (PreparedStatement stmt2 = conn.prepareStatement(
                            "SELECT inventory_count FROM product_inventory " +
                            "WHERE product_id = ? FOR UPDATE")) {
                        stmt2.setString(1, secondProduct);
                        stmt2.executeQuery();
                        
                        // Perform the transfer
                        updateInventory(conn, fromProduct, -quantity);
                        updateInventory(conn, toProduct, quantity);
                        
                        conn.commit();
                    }
                }
            }
            return null;
        });
    }
    
    private void updateInventory(Connection conn, String productId, int change) 
            throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(
                "UPDATE product_inventory SET inventory_count = inventory_count + ? " +
                "WHERE product_id = ?")) {
            stmt.setInt(1, change);
            stmt.setString(2, productId);
            stmt.executeUpdate();
        }
    }
    
    // Get current deadlock metrics
    public DeadlockMetrics getMetrics() {
        return metrics;
    }
}

InventorySystemHealth.java

public class InventorySystemHealth {
    private static final Logger log = LoggerFactory.getLogger(InventorySystemHealth.class);
    private final DataSource dataSource;
    private final MetricRegistry metrics;
    private final HealthStatusManager healthStatus;
    private final MovingAverages baselineMetrics;
    
    // Configurable thresholds
    private static final double ERROR_RATE_THRESHOLD = 0.05;  // 5%
    private static final long RESPONSE_TIME_THRESHOLD = 500;   // 500ms
    private static final int MAX_CONNECTION_WAIT = 1000;      // 1 second
    private static final int ALERT_WINDOW_MINUTES = 5;
    
    public InventorySystemHealth(DataSource dataSource) {
        this.dataSource = dataSource;
        this.metrics = new MetricRegistry();
        this.healthStatus = new HealthStatusManager();
        this.baselineMetrics = new MovingAverages();
        
        // Start monitoring
        initializeMonitoring();
    }
    
    private void initializeMonitoring() {
        ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(2);
        
        // Regular health checks
        scheduler.scheduleAtFixedRate(this::performHealthCheck, 
            0, 1, TimeUnit.MINUTES);
            
        // Baseline metrics update
        scheduler.scheduleAtFixedRate(this::updateBaselines,
            0, 1, TimeUnit.HOURS);
    }
    
    private void performHealthCheck() {
        try {
            HealthCheckResult result = new HealthCheckResult();
            
            // Check database connectivity
            result.dbConnectivity = checkDatabaseConnectivity();
            
            // Check error rates
            result.errorRate = calculateErrorRate();
            
            // Check response times
            result.avgResponseTime = calculateAverageResponseTime();
            
            // Check connection pool
            result.connectionPoolHealth = checkConnectionPool();
            
            // Check transaction success rate
            result.transactionSuccess = checkTransactionSuccess();
            
            // Evaluate overall health
            evaluateHealth(result);
            
            // Take automatic actions if needed
            if (!result.isHealthy()) {
                triggerRecoveryActions(result);
            }
            
        } catch (Exception e) {
            log.error("Error performing health check", e);
            healthStatus.setUnhealthy("Health check failed: " + e.getMessage());
        }
    }
    
    private boolean checkDatabaseConnectivity() {
        try (Connection conn = dataSource.getConnection()) {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("SELECT 1");
                return true;
            }
        } catch (SQLException e) {
            log.error("Database connectivity check failed", e);
            return false;
        }
    }
    
    private double calculateErrorRate() {
        Timer.Context context = metrics.timer("error.rate").time();
        try {
            long errors = metrics.counter("errors").getCount();
            long total = metrics.counter("requests").getCount();
            return total > 0 ? (double) errors / total : 0.0;
        } finally {
            context.stop();
        }
    }
    
    private long calculateAverageResponseTime() {
        return metrics.timer("response.time").getSnapshot().getMean();
    }
    
    private ConnectionPoolHealth checkConnectionPool() {
        if (dataSource instanceof HikariDataSource) {
            HikariPoolMXBean poolProxy = ((HikariDataSource) dataSource).getHikariPoolMXBean();
            return new ConnectionPoolHealth(
                poolProxy.getActiveConnections(),
                poolProxy.getTotalConnections(),
                poolProxy.getThreadsAwaitingConnection()
            );
        }
        return null;
    }
    
    private void triggerRecoveryActions(HealthCheckResult result) {
        if (result.errorRate > ERROR_RATE_THRESHOLD) {
            log.warn("High error rate detected: {}. Triggering recovery.", 
                    result.errorRate);
            reduceLoad();
        }
        
        if (result.avgResponseTime > RESPONSE_TIME_THRESHOLD) {
            log.warn("Slow response time detected: {}ms. Optimizing resources.", 
                    result.avgResponseTime);
            optimizeResources();
        }
        
        if (!result.dbConnectivity) {
            log.error("Database connectivity issues detected. Attempting reconnection.");
            reconnectDatabase();
        }
    }
    
    private void reduceLoad() {
        // Implement circuit breaker pattern
        circuitBreaker.tripBreaker();
        
        // Scale up resources if possible
        try {
            resourceManager.scaleUpInstances();
        } catch (Exception e) {
            log.error("Failed to scale up resources", e);
        }
    }
    
    private void optimizeResources() {
        // Clear any resource-heavy caches
        cacheManager.clearNonEssentialCaches();
        
        // Optimize connection pool
        if (dataSource instanceof HikariDataSource) {
            HikariDataSource hikari = (HikariDataSource) dataSource;
            int currentMax = hikari.getMaximumPoolSize();
            hikari.setMaximumPoolSize(currentMax + 5);
        }
    }
    
    public HealthStatus getCurrentStatus() {
        return healthStatus.getCurrentStatus();
    }
    
    // Health check result container
    private static class HealthCheckResult {
        boolean dbConnectivity;
        double errorRate;
        long avgResponseTime;
        ConnectionPoolHealth connectionPoolHealth;
        double transactionSuccess;
        
        boolean isHealthy() {
            return dbConnectivity 
                && errorRate < ERROR_RATE_THRESHOLD
                && avgResponseTime < RESPONSE_TIME_THRESHOLD
                && (connectionPoolHealth == null || 
                    connectionPoolHealth.waitingThreads < MAX_CONNECTION_WAIT);
        }
    }
}

OracleConnectionPool.java

public class AdaptiveConnectionPool {
    private final HikariConfig config;
    private final HikariDataSource dataSource;
    private final LoadMonitor loadMonitor;
    
    public AdaptiveConnectionPool(String jdbcUrl, String username, String password) {
        this.config = new HikariConfig();
        this.loadMonitor = new LoadMonitor();
        
        // Base configuration
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(username);
        config.setPassword(password);
        
        // Critical settings for Oracle with pessimistic locking
        config.setMaximumPoolSize(50);
        config.setMinimumIdle(10);
        config.setIdleTimeout(300000); // 5 minutes
        config.setMaxLifetime(1800000); // 30 minutes
        config.setConnectionTimeout(10000); // 10 seconds
        
        // Oracle-specific optimizations
        config.addDataSourceProperty("oracle.jdbc.fanEnabled", "false");
        config.addDataSourceProperty("oracle.jdbc.implicitStatementCacheSize", "20");
        config.addDataSourceProperty("oracle.jdbc.maxCachedBufferSize", "19200");
        
        // Statement caching
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        this.dataSource = new HikariDataSource(config);
        
        // Start monitoring thread
        startAdaptivePoolSizing();
    }
    
    private void startAdaptivePoolSizing() {
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(() -> {
            try {
                adjustPoolSize();
            } catch (Exception e) {
                log.error("Error adjusting pool size", e);
            }
        }, 1, 1, TimeUnit.MINUTES);
    }
    
    private void adjustPoolSize() {
        double currentLoad = loadMonitor.getCurrentLoad();
        int activeConnections = dataSource.getHikariPoolMXBean().getActiveConnections();
        int totalConnections = dataSource.getHikariPoolMXBean().getTotalConnections();
        
        // Calculate optimal pool size based on load
        int optimalSize = calculateOptimalPoolSize(currentLoad, activeConnections);
        
        if (optimalSize > totalConnections && totalConnections < config.getMaximumPoolSize()) {
            // Gradually increase pool size
            config.setMaximumPoolSize(Math.min(optimalSize, config.getMaximumPoolSize()));
            log.info("Increasing pool size to: {}", optimalSize);
        } else if (optimalSize < totalConnections && optimalSize > config.getMinimumIdle()) {
            // Gradually decrease pool size
            config.setMaximumPoolSize(optimalSize);
            log.info("Decreasing pool size to: {}", optimalSize);
        }
    }
    
    private int calculateOptimalPoolSize(double currentLoad, int activeConnections) {
        // Base calculation on current load and active connections
        int optimal = (int) (activeConnections * (1 + currentLoad/100));
        
        // Add buffer for spikes
        optimal += Math.max(5, optimal * 0.1);
        
        // Ensure within bounds
        return Math.max(config.getMinimumIdle(),
               Math.min(optimal, config.getMaximumPoolSize()));
    }
    
    // Metrics for monitoring
    public PoolMetrics getMetrics() {
        HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
        return new PoolMetrics(
            poolMXBean.getActiveConnections(),
            poolMXBean.getTotalConnections(),
            poolMXBean.getThreadsAwaitingConnection(),
            loadMonitor.getCurrentLoad()
        );
    }
    
    public HikariDataSource getDataSource() {
        return dataSource;
    }
}

OracleInventoryManager.java

public class OracleInventoryManager {
    private final DataSource dataSource;
    private static final int MAX_RETRY_ATTEMPTS = 3;
    private static final int LOCK_TIMEOUT_MS = 5000;

    public OracleInventoryManager(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public boolean reserveInventory(String productId, int quantity) throws SQLException {
        String sql = """
            SELECT inventory_count 
            FROM product_inventory 
            WHERE product_id = ? 
            FOR UPDATE WAIT %d
            """.formatted(LOCK_TIMEOUT_MS / 1000);

        for (int attempt = 0; attempt < MAX_RETRY_ATTEMPTS; attempt++) {
            try (Connection conn = dataSource.getConnection()) {
                conn.setAutoCommit(false);
                try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                    stmt.setString(1, productId);
                    ResultSet rs = stmt.executeQuery();
                    
                    if (rs.next()) {
                        int currentInventory = rs.getInt("inventory_count");
                        if (currentInventory >= quantity) {
                            // Update inventory with lock held
                            try (PreparedStatement updateStmt = conn.prepareStatement(
                                "UPDATE product_inventory SET inventory_count = ? WHERE product_id = ?")) {
                                updateStmt.setInt(1, currentInventory - quantity);
                                updateStmt.setString(2, productId);
                                updateStmt.executeUpdate();
                                conn.commit();
                                return true;
                            }
                        }
                    }
                    conn.rollback();
                    return false;
                } catch (SQLException e) {
                    conn.rollback();
                    if (attempt == MAX_RETRY_ATTEMPTS - 1) {
                        throw e;
                    }
                    // Wait before retry
                    Thread.sleep(100 * (attempt + 1));
                }
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
                throw new SQLException("Operation interrupted", e);
            }
        }
        return false;
    }
}

PostgresConnectionPool.java

public class PostgresConnectionPool {
    private final HikariConfig config;
    private final HikariDataSource dataSource;
    private final RetryStatistics retryStats;

    public PostgresConnectionPool(String jdbcUrl, String username, String password) {
        this.config = new HikariConfig();
        this.retryStats = new RetryStatistics();
        
        // Base configuration
        config.setJdbcUrl(jdbcUrl);
        config.setUsername(username);
        config.setPassword(password);
        
        // MVCC optimized settings
        config.setMaximumPoolSize(20);  // Lower than Oracle due to MVCC efficiency
        config.setMinimumIdle(5);
        config.setIdleTimeout(600000);  // 10 minutes
        config.setMaxLifetime(1800000); // 30 minutes
        config.setConnectionTimeout(5000); // 5 seconds
        
        // PostgreSQL specific optimizations
        config.addDataSourceProperty("reWriteBatchedInserts", "true");
        config.addDataSourceProperty("prepareThreshold", "3");
        config.addDataSourceProperty("preparedStatementCacheQueries", "250");
        config.addDataSourceProperty("preparedStatementCacheSizeMiB", "32");
        
        // Transaction isolation for MVCC
        config.setTransactionIsolation("TRANSACTION_READ_COMMITTED");
        
        // Add health check query
        config.setConnectionTestQuery("SELECT 1");
        
        this.dataSource = new HikariDataSource(config);
        
        // Start monitoring
        startPerformanceMonitoring();
    }
    
    private void startPerformanceMonitoring() {
        ScheduledExecutorService scheduler = Executors.newSingleThreadScheduledExecutor();
        scheduler.scheduleAtFixedRate(() -> {
            try {
                adjustPoolBasedOnRetries();
                checkDeadlockRate();
            } catch (Exception e) {
                log.error("Error in performance monitoring", e);
            }
        }, 1, 1, TimeUnit.MINUTES);
    }
    
    private void adjustPoolBasedOnRetries() {
        double retryRate = retryStats.getRetryRate();
        int currentPoolSize = dataSource.getHikariPoolMXBean().getActiveConnections();
        
        if (retryRate > 0.1) { // More than 10% retries
            // Consider reducing pool size to decrease contention
            int newSize = Math.max(config.getMinimumIdle(),
                                 (int)(currentPoolSize * 0.8));
            config.setMaximumPoolSize(newSize);
            log.info("High retry rate detected. Reducing pool size to: {}", newSize);
        } else if (retryRate < 0.05 && currentPoolSize == config.getMaximumPoolSize()) {
            // Can try increasing if we're at max and seeing few retries
            int newSize = Math.min(currentPoolSize + 2, 50);
            config.setMaximumPoolSize(newSize);
            log.info("Low retry rate. Increasing pool size to: {}", newSize);
        }
    }
    
    private void checkDeadlockRate() {
        try (Connection conn = dataSource.getConnection()) {
            try (PreparedStatement stmt = conn.prepareStatement(
                    "SELECT count(*) FROM pg_stat_database WHERE datname = current_database() " +
                    "AND deadlocks > 0")) {
                ResultSet rs = stmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    log.warn("Deadlocks detected in database. Consider reviewing transaction patterns.");
                }
            }
        } catch (SQLException e) {
            log.error("Error checking deadlock rate", e);
        }
    }
    
    // Record serialization failures for monitoring
    public void recordSerializationFailure() {
        retryStats.recordRetry();
    }
    
    public DataSource getDataSource() {
        return dataSource;
    }
    
    // Get current pool metrics
    public PoolMetrics getMetrics() {
        HikariPoolMXBean poolMXBean = dataSource.getHikariPoolMXBean();
        return new PoolMetrics(
            poolMXBean.getActiveConnections(),
            poolMXBean.getTotalConnections(),
            poolMXBean.getThreadsAwaitingConnection(),
            retryStats.getRetryRate()
        );
    }
}

PostgresInventoryManager.java

public class PostgresInventoryManager {
    private final DataSource dataSource;
    private static final int MAX_RETRY_ATTEMPTS = 3;
    
    public PostgresInventoryManager(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    public boolean reserveInventory(String productId, int quantity) throws SQLException {
        String selectSql = """
            SELECT inventory_count, version 
            FROM product_inventory 
            WHERE product_id = ?
            """;
            
        String updateSql = """
            UPDATE product_inventory 
            SET inventory_count = ?, version = version + 1
            WHERE product_id = ? 
            AND version = ? 
            AND inventory_count >= ?
            """;
            
        for (int attempt = 0; attempt < MAX_RETRY_ATTEMPTS; attempt++) {
            try (Connection conn = dataSource.getConnection()) {
                conn.setAutoCommit(false);
                
                // First read the current state
                int currentInventory;
                long currentVersion;
                try (PreparedStatement stmt = conn.prepareStatement(selectSql)) {
                    stmt.setString(1, productId);
                    ResultSet rs = stmt.executeQuery();
                    
                    if (!rs.next()) {
                        conn.rollback();
                        return false;
                    }
                    
                    currentInventory = rs.getInt("inventory_count");
                    currentVersion = rs.getLong("version");
                }
                
                // Now try to update with version check
                if (currentInventory >= quantity) {
                    try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                        updateStmt.setInt(1, currentInventory - quantity);
                        updateStmt.setString(2, productId);
                        updateStmt.setLong(3, currentVersion);
                        updateStmt.setInt(4, quantity);
                        
                        int updatedRows = updateStmt.executeUpdate();
                        if (updatedRows == 1) {
                            conn.commit();
                            return true;
                        }
                    }
                }
                
                conn.rollback();
                
                // If we got here, either inventory was insufficient or version changed
                // Wait with exponential backoff before retry
                if (attempt < MAX_RETRY_ATTEMPTS - 1) {
                    Thread.sleep((long) Math.pow(2, attempt) * 100);
                }
                
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
                throw new SQLException("Operation interrupted", e);
            }
        }
        
        return false;
    }
}