Multi-Tenant Advisory Portal Transactional Deadlock Resolver and Diagnostic Harness

Role: Principal Database Reliability Engineer specializing in high-concurrency client engagement platforms and distributed deadlock mitigation.

Context

• Client Portal Platform: {{consulting_platform_name}}
• Database Engine & Version: {{database_engine}}
• Raw Deadlock Trace: {{deadlock_trace_log}}
• Contested Service Endpoints: {{affected_service_endpoints}}
• Lock Wait Timeout Threshold: {{max_lock_wait_seconds}}
• Tenant Partitioning Schema: {{tenant_isolation_strategy}}

Task

Produce an automated forensic debugging script and diagnostic harness that parses the provided {{deadlock_trace_log}}, reconstructs the lock dependency graph across {{affected_service_endpoints}}, and executes non-destructive session mitigation on {{database_engine}} under {{tenant_isolation_strategy}} constraints.

Method

1. Ingest and tokenize {{deadlock_trace_log}} to extract conflicting transaction identifiers, blocking queries, and table locks.
2. Graph the transaction cyclic dependency tree to isolate the exact race condition triggering lock escalation.
3. Formulate dialect-specific SQL monitoring queries targeting active transactions waiting longer than {{max_lock_wait_seconds}}.
4. Design an automated, graceful session termination algorithm that prioritizes killing the lowest-cost transaction.
5. Write automated indexing and query refactoring recommendations to resolve row-level lock contention in {{consulting_platform_name}}.
6. Wrap the SQL diagnostics into a bash monitoring daemon with alerting webhooks and structured JSON metrics.
7. Formulate a verification test script to simulate concurrent tenant operations and confirm lock elimination.

Constraints

• The script MUST execute with minimum database privileges and avoid global table locking.
• The script MUST NOT terminate database connections belonging to non-affected tenant partitions.
• SQL queries must be optimized to run in less than 50ms to avoid compounding database load.
• Output scripts must be fully compatible with {{database_engine}}.

Output format

1. Lock Graph Forensic Analysis: Table mapping blocker PID, victim PID, contested resource, and query snippet.
2. Diagnostic SQL Script (diagnose_locks.sql): Comprehensive performance schema diagnostic queries.
3. Automation & Termination Script (kill_deadlocks.sh): Bash wrapper with safety checks and JSON logging.
4. Schema Remediation DDL: Index adjustments and transaction isolation level recommendations.

Self-review

• Does the termination script check tenant isolation before sending cancellation signals?
• Are all SQL queries parameterized and safe from injection?
• Does the script verify the state of pg_stat_activity or equivalent sys tables cleanly?