# Root Cause Analysis (RCA) Post-Mortem ## PHASE 1: EXECUTIVE SUMMARY & IMPACT **Incident Status:** Resolved **Duration:** 45 Minutes **Summary:** The primary production database experienced a severe performance degradation and subsequent connection pool exhaustion, leading to a complete outage of the checkout service. The issue was triggered by an unoptimized database migration applied during peak traffic hours. **Business Impact:** - The checkout flow was completely unavailable for 45 minutes. - Approximately 1,200 shopping carts were abandoned. - Estimated immediate revenue loss: $50,000 USD. - Significant spike in Tier 1 Customer Support tickets regarding failed payments. ## PHASE 2: INCIDENT TIMELINE *(All times in UTC)* - **14:00:** Deployment pipeline executes the scheduled database migration for the new user rewards feature. - **14:02:** Datadog alerts trigger: `High DB CPU Usage (>95%)` and `API Gateway 504 Gateway Timeout`. - **14:05:** Incident Commander is paged. SRE team begins investigation. - **14:12:** SRE identifies the `users` table is locked by an `ALTER TABLE` statement, causing a massive queue of pending transactions. - **14:20:** Decision made to manually kill the blocking migration process (PID 4921) via the database console to restore read/write capabilities. - **14:25:** The migration process is killed. Connection pools begin to drain and normalize. - **14:40:** Database CPU drops to 30%. API Gateway error rates return to baseline. - **14:45:** Incident declared resolved. Checkout flow verified as operational. ## PHASE 3: ROOT CAUSE ANALYSIS (5 WHYS) 1. **Why did the checkout service go offline?** Because the API Gateway timed out waiting for the database to respond. 2. **Why was the database unresponsive?** Because the connection pool was exhausted and CPU was at 100%. 3. **Why was the connection pool exhausted?** Because all incoming queries to the `users` table were stuck waiting for a lock to be released. 4. **Why was the `users` table locked?** Because a database migration script executed an `ALTER TABLE ADD COLUMN` statement without the `CONCURRENTLY` flag during peak traffic. 5. **Why was an unoptimized, blocking migration deployed during peak hours?** Because the CI/CD pipeline does not currently analyze migration scripts for table-locking operations, and deployments are not restricted by time-of-day traffic thresholds. ## PHASE 4: PREVENTATIVE MEASURES (ACTION ITEMS) - **CI/CD (High Priority):** Implement a static analysis linter (e.g., Squawk or strong_migrations) in the deployment pipeline to automatically fail any PR that attempts a blocking database operation. - **Process (Medium Priority):** Establish a "Deployment Freeze" window for heavy infrastructure changes during known peak traffic hours (12:00 PM to 4:00 PM UTC). - **Monitoring (Medium Priority):** Create a specific Datadog alert for `Long-Running DB Locks (> 10 seconds)` to detect blocking queries before they exhaust the entire connection pool.