Client-Server Architecture in Database Systems

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
105
106
107
108

// Application Server Transaction Management Examples
 
// Example 1: Declarative Transaction (Spring @Transactional)
// Container automatically manages transaction boundaries
@Service
public class OrderService {
    
    @Autowired
    private OrderRepository orderRepository;
    
    @Autowired
    private InventoryRepository inventoryRepository;
    
    @Autowired
    private PaymentGateway paymentGateway;
    
    /**
     * @Transactional tells the container:
     * 1. Start a transaction before this method executes
     * 2. Commit if method completes normally
     * 3. Rollback if any RuntimeException is thrown
     * 
     * Both orderRepository and inventoryRepository operations
     * are part of the SAME transaction - atomic!
     */
    @Transactional
    public Order createOrder(OrderRequest request) {
        // Deduct inventory (same transaction)
        inventoryRepository.decreaseStock(
            request.getProductId(), 
            request.getQuantity()
        );
        
        // Create order record (same transaction)
        Order order = new Order();
        order.setCustomerId(request.getCustomerId());
        order.setProductId(request.getProductId());
        order.setQuantity(request.getQuantity());
        order.setStatus(OrderStatus.PENDING);
        
        Order savedOrder = orderRepository.save(order);
        
        // If any operation fails, BOTH rollback automatically
        return savedOrder;
    }
    
    /**
     * Propagation controls how transactions interact.
     * REQUIRES_NEW: Always start a new, independent transaction
     */
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void logAuditEvent(String event, String details) {
        // This runs in its own transaction
        // Even if calling transaction rolls back, audit is preserved
        auditRepository.save(new AuditEvent(event, details));
    }
    
    /**
     * Example with multiple resources (XA/distributed)
     * This coordinates database + message queue
     */
    @Transactional
    public Order createOrderWithNotification(OrderRequest request) {
        // Database operation (Resource 1)
        Order order = createOrder(request);
        
        // Message queue operation (Resource 2)
        // XA transaction ensures both commit or both rollback
        jmsTemplate.convertAndSend("order-notifications", 
            new OrderCreatedEvent(order.getId())
        );
        
        return order;
        // Container coordinates two-phase commit across DB and MQ
    }
}
 
// Example 2: Programmatic Transaction Management
// For cases requiring explicit control
@Service
public class PaymentService {
    
    @Autowired
    private PlatformTransactionManager transactionManager;
    
    public PaymentResult processPaymentWithRetry(PaymentRequest request) {
        TransactionTemplate txTemplate = new TransactionTemplate(transactionManager);
        txTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_READ_COMMITTED);
        txTemplate.setTimeout(30); // 30 second timeout
        
        return txTemplate.execute(status -> {
            try {
                // Perform payment operations within transaction
                PaymentResult result = executePayment(request);
                
                if (!result.isSuccessful()) {
                    // Explicitly mark for rollback
                    status.setRollbackOnly();
                }
                
                return result;
            } catch (PaymentException e) {
                // Transaction will rollback automatically on exception
                throw e;
            }
        });
    }
}

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

// HikariCP Connection Pool Configuration (Spring Boot)
 
@Configuration
public class DatabaseConfig {
    
    @Bean
    @Primary
    public DataSource primaryDataSource() {
        HikariConfig config = new HikariConfig();
        
        // Basic connection settings
        config.setJdbcUrl("jdbc:postgresql://db-primary.example.com:5432/appdb");
        config.setUsername("appuser");
        config.setPassword(getSecretFromVault("db-password"));
        config.setDriverClassName("org.postgresql.Driver");
        
        // Pool size configuration
        config.setMinimumIdle(10);        // Keep 10 connections ready
        config.setMaximumPoolSize(50);    // Never exceed 50 connections
        
        // Timeout configuration
        config.setConnectionTimeout(30_000);  // Wait max 30s for connection
        config.setIdleTimeout(600_000);       // Close idle connection after 10min
        config.setMaxLifetime(1_800_000);     // Recycle connection after 30min
        
        // Validation configuration
        config.setConnectionTestQuery("SELECT 1");  // Lightweight validation query
        config.setValidationTimeout(5_000);         // Must validate within 5s
        
        // Performance optimizations
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        
        // Connection pool name for monitoring
        config.setPoolName("Primary-HikariPool");
        
        // Enable JMX monitoring
        config.setRegisterMbeans(true);
        
        // Leak detection (development/staging only)
        if (!isProduction()) {
            config.setLeakDetectionThreshold(60_000);  // Warn if held > 60s
        }
        
        return new HikariDataSource(config);
    }
    
    /**
     * Sizing formula for maximum pool size:
     * 
     * connections = ((core_count * 2) + effective_spindle_count)
     * 
     * For SSDs, effective_spindle_count ≈ 0, so:
     * connections ≈ core_count * 2
     * 
     * For a 16-core database server with SSD:
     * Optimal pool size ≈ 32 connections
     * 
     * Add application servers: if 5 app servers with 50 pool size each,
     * database sees up to 250 connections. Plan accordingly!
     */
}

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

// Application Server Security Configuration (Spring Security)
 
@Configuration
@EnableWebSecurity
@EnableMethodSecurity(prePostEnabled = true)
public class SecurityConfig {
    
    @Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http
            // CSRF protection for session-based apps
            .csrf(csrf -> csrf
                .csrfTokenRepository(CookieCsrfTokenRepository.withHttpOnlyFalse())
            )
            
            // URL-based authorization rules
            .authorizeHttpRequests(auth -> auth
                // Public endpoints - no authentication required
                .requestMatchers("/", "/health", "/public/**").permitAll()
                .requestMatchers("/api/auth/**").permitAll()
                
                // Admin endpoints - require ADMIN role
                .requestMatchers("/admin/**").hasRole("ADMIN")
                .requestMatchers("/api/admin/**").hasRole("ADMIN")
                
                // API endpoints - authenticated with specific roles
                .requestMatchers(HttpMethod.GET, "/api/orders/**")
                    .hasAnyRole("USER", "ADMIN")
                .requestMatchers(HttpMethod.POST, "/api/orders/**")
                    .hasRole("USER")
                .requestMatchers(HttpMethod.DELETE, "/api/orders/**")
                    .hasRole("ADMIN")
                
                // Everything else requires authentication
                .anyRequest().authenticated()
            )
            
            // JWT-based authentication for API
            .oauth2ResourceServer(oauth2 -> oauth2
                .jwt(jwt -> jwt
                    .jwtAuthenticationConverter(jwtAuthenticationConverter())
                )
            )
            
            // Session management (stateless for API)
            .sessionManagement(session -> session
                .sessionCreationPolicy(SessionCreationPolicy.STATELESS)
            );
        
        return http.build();
    }
    
    /**
     * Convert JWT claims to Spring Security authorities
     */
    @Bean
    public JwtAuthenticationConverter jwtAuthenticationConverter() {
        JwtGrantedAuthoritiesConverter grantedAuthoritiesConverter = 
            new JwtGrantedAuthoritiesConverter();
        grantedAuthoritiesConverter.setAuthoritiesClaimName("roles");
        grantedAuthoritiesConverter.setAuthorityPrefix("ROLE_");
        
        JwtAuthenticationConverter converter = new JwtAuthenticationConverter();
        converter.setJwtGrantedAuthoritiesConverter(grantedAuthoritiesConverter);
        return converter;
    }
}
 
// Method-level security example
@Service
public class OrderService {
    
    /**
     * @PreAuthorize checks BEFORE method execution
     * User can only access their own orders
     */
    @PreAuthorize("#customerId == authentication.principal.id or hasRole('ADMIN')")
    public List<Order> getOrdersForCustomer(Long customerId) {
        return orderRepository.findByCustomerId(customerId);
    }
    
    /**
     * Domain object security - filter results based on ownership
     */
    @PostFilter("filterObject.customerId == authentication.principal.id or hasRole('ADMIN')")
    public List<Order> searchOrders(OrderSearchCriteria criteria) {
        return orderRepository.search(criteria);
    }
}

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
105
106
107
108
109
110
111

// Modern Component Lifecycle Management (Spring Boot)
 
/**
 * SINGLETON SCOPE (default)
 * One instance shared across entire application
 * Perfect for stateless services
 */
@Service
public class OrderService {
    
    private final OrderRepository orderRepository;
    private final EventPublisher eventPublisher;
    
    // Constructor injection - container provides dependencies
    public OrderService(OrderRepository orderRepository, 
                        EventPublisher eventPublisher) {
        this.orderRepository = orderRepository;
        this.eventPublisher = eventPublisher;
    }
    
    // Thread-safe because stateless
    public Order processOrder(OrderRequest request) {
        Order order = new Order(request);
        orderRepository.save(order);
        eventPublisher.publish(new OrderCreatedEvent(order));
        return order;
    }
}
 
/**
 * REQUEST SCOPE
 * New instance per HTTP request
 * Useful for request-specific context
 */
@Component
@RequestScope
public class RequestContext {
    
    private String requestId;
    private Instant requestStart;
    private User authenticatedUser;
    
    @PostConstruct
    public void init() {
        this.requestId = UUID.randomUUID().toString();
        this.requestStart = Instant.now();
    }
    
    // Each request gets its own context instance
    // No thread-safety concerns within a single request
}
 
/**
 * PROTOTYPE SCOPE
 * New instance every time the bean is requested
 * Useful for non-shared stateful objects
 */
@Component
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public class ReportBuilder {
    
    private List<ReportSection> sections = new ArrayList<>();
    private ReportFormat format;
    
    // Each caller gets their own ReportBuilder
    public ReportBuilder withSection(ReportSection section) {
        sections.add(section);
        return this;
    }
    
    public Report build() {
        return new Report(sections, format);
    }
}
 
/**
 * LIFECYCLE CALLBACKS
 * Container-managed initialization and destruction
 */
@Component
public class CacheManager {
    
    private Cache<String, Object> cache;
    
    /**
     * Called by container after all dependencies injected
     * Guaranteed to run before the component is used
     */
    @PostConstruct
    public void initialize() {
        this.cache = Caffeine.newBuilder()
            .maximumSize(10_000)
            .expireAfterWrite(Duration.ofMinutes(10))
            .recordStats()
            .build();
        
        log.info("Cache initialized with 10K max entries");
    }
    
    /**
     * Called by container before destroying the component
     * Opportunity to release resources, flush data
     */
    @PreDestroy
    public void shutdown() {
        log.info("Cache stats before shutdown: {}", cache.stats());
        cache.invalidateAll();
        cache.cleanUp();
        log.info("Cache cleaned up");
    }
}