Detect and handle (or prevent) deadlocks

[gabestein]

What went wrong, step-by-step?

Deleting/creating lots of pubs in parallel via the API appears to be capable of causing a deadlock.

stevie-ecs-production-container-logs ecs/core/b77fd5ab3dde456ca5c5479a28658810 2025-03-13T18:10:47.490Z {"level":50,"time":1741889447489,"err":{"type":"DatabaseError","message":"deadlock detected","stack":"error: deadlock detected
    at /usr/src/app/node_modules/.pnpm/pg@8.12.0/node_modules/pg/lib/client.js:526:17
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async u.executeQuery (/usr/src/app/core/.next/server/chunks/7186.js:96:42756)
    at async /usr/src/app/core/.next/server/chunks/7186.js:97:141678
    at async #_ (/usr/src/app/core/.next/server/chunks/7186.js:97:59496)
    at async eI.executeQuery (/usr/src/app/core/.next/server/chunks/7186.js:97:141633)
    at async j.execute (/usr/src/app/core/.next/server/chunks/7186.js:97:129132)
    at async j.executeTakeFirst (/usr/src/app/core/.next/server/chunks/7186.js:97:129398)
    at async j.executeTakeFirstOrThrow (/usr/src/app/core/.next/server/chunks/7186.js:97:129471)
    at async Object.executeTakeFirstOrThrow (/usr/src/app/core/.next/server/chunks/2274.js:1:2765)
    at u.executeQuery (/usr/src/app/core/.next/server/chunks/7186.js:97:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async /usr/src/app/core/.next/server/chunks/7186.js:97:141678
    at async #_ (/usr/src/app/core/.next/server/chunks/7186.js:97:59496)
    at async eI.executeQuery (/usr/src/app/core/.next/server/chunks/7186.js:97:141633)
    at async j.execute (/usr/src/app/core/.next/server/chunks/7186.js:97:129132)
    at async j.executeTakeFirst (/usr/src/app/core/.next/server/chunks/7186.js:97:129398)
    at async j.executeTakeFirstOrThrow (/usr/src/app/core/.next/server/chunks/7186.js:97:129471)
    at async Object.executeTakeFirstOrThrow (/usr/src/app/core/.next/server/chunks/2274.js:1:2765)
    at async /usr/src/app/core/.next/server/chunks/2274.js:1:26172","length":428,"name":"error","severity":"ERROR","code":"40P01","detail":"Process 4198 waits for ShareLock on transaction 362068; blocked by process 4191.
Process 4191 waits for ShareLock on transaction 362070; blocked by process 4198.","hint":"See server log for query details.","where":"while deleting tuple (518,15) in relation \"pub_values\"
SQL statement \"DELETE FROM ONLY \"public\".\"pub_values\" WHERE $1 OPERATOR(pg_catalog.=) \"pubId\"\"","file":"deadlock.c","line":"1152","routine":"DeadLockReport"},"msg":"deadlock detected"}

What did you expect to happen?

At minimum (thanks to @kalilsn):

• we handle that error instead of the app instance hanging
• we update the queries so they don't deadlock

Ideally, we also show these errors in Sentry.

What URL can we use to see the issue?

What browser/operating system were you using?

Optional

Have any screenshots? (Attach them by dragging the image here)

Who reported this? (If not you)

[gabestein]

@kalilsn -- last minute change here, so if you have an idea of timing/what you plan to do to address, please update!

[kalilsn]

That timing sounds good. I'm just going to try to recreate and fix this particular bug, which is probably caused by us starting a transaction and then accidentally waiting for a query that's not part of the transaction. If it's easy to fix, I'll also make sure this kind of error actually makes it to sentry but I don't really know why that would be broken in the first place so I don't want to commit to it.

[kalilsn]

The specific instance was fixed by @tefkah in #1224. And the underlying problem that makes the app totally hang forever should be fixed by a combination of our health check changes in #1220 and the pool changes in #1226