Deadlock investigation

[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/[email protected]/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)