Concurrency Management and Resilience Patterns

Table of Contents

1. Challenge

   Resolve Lock Contention and Deadlock Scenarios

   • Generate a blocking chain by holding an exclusive lock in a background job while a second job attempts to update the same row.
   • Diagnose the blocking chain to identify the waiting session, lock type, and blocked query.
   • Generate a deadlock with two transactions acquiring locks in opposite order and analyze the deadlock graph.
   • Enable Read Committed Snapshot Isolation and verify that concurrent reads complete without blocking during an active write transaction.
2. Challenge

   Implement Application-Side Resilience Patterns

   • Build a Circuit Breaker that transitions through all three states: Closed during normal operations, Open after three consecutive failures, and Half-Open with a trial recovery call.
   • Implement Queue-Based Load Leveling by sending transaction messages to an Amazon SQS queue, then processing them one by one into the database with receive, insert, and delete operations.
   • Configure Command Query Responsibility Segregation by routing writes to SQL1 and reads to SQL2, verifying data replication through the Always On Availability Group within seconds.
3. Challenge

   Tune System Resources for Maximum Concurrency

   • Configure connection pooling parameters, including Min Pool Size, Max Pool Size, and Connection Timeout, and monitor pool behavior.
   • Set max server memory to reserve 4 GB for the operating system and verify buffer pool efficiency.
   • Implement table partitioning on the Transactions table by year, verify partition distribution across four partitions, and confirm partition elimination in execution plans.
   • Check lock escalation statistics and run a concurrent load test with 25 readers and 10 writers to validate zero blocked requests and an error rate below 5%.