Two transactions deadlock occasionally in production

You

Two transactions deadlock occasionally in production.

ChatGPT

They take the same locks in different orders, and only concurrency makes that visible. Establish a global ordering, usually by sorting the ids you touch before touching them. Retry logic is a bandage: correct, necessary, and not a substitute for the ordering.

Tool steps: 3 (reasoning summary, call to python, execution output)

reasoning summary

Checked the query plan, then compared index options.

call to python

rows = run_sql("EXPLAIN (ANALYZE) SELECT ...")
print(rows[:3])

execution output

Seq Scan on events  (cost=0.00..812431.00 rows=41203122 width=16)
  Rows Removed by Filter: 38996878
Planning Time: 0.214 ms
Execution Time: 11842.663 ms

You

Rewriting to async made throughput worse.

ChatGPT

Something synchronous is running inside the event loop, usually a blocking library call or CPU work. One blocking call stalls every other task on that loop. Run those in a thread executor, and measure with a loop-lag metric so the next one is visible immediately.