search

mgcrea / prisma-queue

Minimalist postgresql job queue for Prisma
MIT License
47 stars 4 forks source link

Having trouble wth long running idle transactions #5

Open eloff opened 3 months ago

eloff commented 3 months ago

We're having some trouble with very long running 'idle in transsaction' connections from Prisma Queue.

Would it be possible to finish the transaction after running the polling query? It's definately the polling query and it stays in the 'idle in transaction' state for hours, which sets off an alarm in RDS.

UPDATE ""queue_jobs"" SET ""processedAt"" = NOW(), ""attempts"" = ""attempts"" + 1
           WHERE id = (
             SELECT id
             FROM ""queue_jobs""
             WHERE (""queue_jobs"".""queue"" = $1)
               AND (""queue_jobs"".""finishedAt"" IS NULL)
               AND (""queue_jobs"".""runAt"" < NOW())
               AND (""queue_jobs"".""notBefore"" IS NULL OR ""queue_jobs"".""notBefore"" < NOW())
             ORDER BY ""queue_jobs"".""priority"" ASC, ""queue_jobs"".""runAt"" ASC
             FOR UPDATE SKIP LOCKED
             LIMIT 1
           )
           RETURNING *;