Having trouble wth long running idle transactions

Open
#5 1 comment 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
postgresql, typescript
Domain
backend, databases

Research direction

Start by locating the Prisma Queue polling path that issues the shown PostgreSQL UPDATE ... RETURNING query, then inspect how its transaction is opened and completed. Done means the polling transaction is finished after the query, so the connection no longer remains idle in transaction for hours or triggers the RDS alarm.

Written by the indexing model from the issue text.

Description

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 *;
Dominant language
TypeScript
Stars
61
Forks
9
No merged PRs in 30d

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from mgcrea/prisma-queue

All issues in mgcrea/prisma-queue

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.