Prisma•2w ago

Silent and Stalled transaction

This is a strange one. I have a transaction running in a child process (worker). The transaction uses a temporary table to insert a bunch of ids to do an efficient join on a very large dataset. The code for this looks something like:

const remoteIdsSql = ids.map((id) => Prisma.sql`(${id})`);
const postgresDriverParamLimit = 32767;
const results = await this.db.$transaction(
  async (tx) => {
    await tx.$executeRaw`
      CREATE TEMP TABLE
        "temp_remote_ids" (
          "id" VARCHAR(200) PRIMARY KEY
        )
      ON COMMIT DROP`;

    for (const idsSqlChunk of chunkGenerator(idsSqlArray, postgresDriverParamLimit)) {
      await tx.$executeRaw`
        INSERT INTO
          "temp_remote_ids" ("id")
        VALUES
          ${Prisma.join(idsSqlChunk)}`;
    }

    const people = await tx.$queryRaw<People[]>`
      SELECT
        p.*
      FROM
        public."People" p
      INNER JOIN
        "temp_remote_ids" t ON p."id" = t."id"`;

    return people;
  },
  {
    timeout: 30000,
  },
);

const remoteIdsSql = ids.map((id) => Prisma.sql`(${id})`);
const postgresDriverParamLimit = 32767;
const results = await this.db.$transaction(
  async (tx) => {
    await tx.$executeRaw`
      CREATE TEMP TABLE
        "temp_remote_ids" (
          "id" VARCHAR(200) PRIMARY KEY
        )
      ON COMMIT DROP`;

    for (const idsSqlChunk of chunkGenerator(idsSqlArray, postgresDriverParamLimit)) {
      await tx.$executeRaw`
        INSERT INTO
          "temp_remote_ids" ("id")
        VALUES
          ${Prisma.join(idsSqlChunk)}`;
    }

    const people = await tx.$queryRaw<People[]>`
      SELECT
        p.*
      FROM
        public."People" p
      INNER JOIN
        "temp_remote_ids" t ON p."id" = t."id"`;

    return people;
  },
  {
    timeout: 30000,
  },
);

When I am running Prisma 6.0.1, I have no problems, the query runs just fine, no issues whatsoever. But if I upgrade to 6.1.0 or higher the worker stalls, and the query never completes. It is as if the transaction is failing silently. I have no errors in my logs, and my process analytics indicate the workers are still active, but no work is being completed. I have spent a lot of time trying to figure out what could possibly be going on here. I don't have a small reproduction to share, as this is a large project I am working on. My current theory is that this has something to do with Alpine 3.20, Prisma 6.0.1, and transactions. I have tried changing telemetry packages, downgrading @prisma/instrumentation, and upgrading Alpine to no avail. Any advice or even outlandish ideas would be appreciated here Thank you

8 Replies

Prisma AI Help•2w ago

You selected the carefully hand-crafted route. A dev artisan will respond soon. Meanwhile, the #ask-ai channel awaits if you're curious!

Nurul•2w ago

Hey 👋 For my understanding: In your snippet you create a temporary table named temp_remote_ids but then insert into table called temp_ids and also do join on temp_ids. Is that intended? Can you enable additional logging and see if you get any errors?

Debugging (Reference) | Prisma Documentation

This page explains how to enable debugging output for Prisma Client by setting the DEBUG environment variable.

VincentOP•7d ago

@Nurul apologies no, that was just a typo as I copy pasted it, its been fixed 👍 Everything is fine and dandy for many months on 6.0.1 with this in place, it's only the newer versions that I can't use, which is unfortunate, as 6.1.0 fixes so many tracing related bugs (basically all of them). I would share the entire project if it wasn't closed source, I can share more details of the technical stack and what not if you are curious. I will configure DEBUG="prisma*" for additional logging and see what I learn. That's a great idea, and the team may have already tried this, but regardless I will give it another shot, as we use this for debugging all the time. Another thing I forgot to mention above. This issue was not reproducible locally on macOS in a dev environment. We only saw prisma exhibit this behavior when running on linux in a docker container. Regardless, I will heed your advice and see what happens. @Nurul are all logs printed by prisma to stderr when in debug mode indicative of a problem? I see prisma printing to stderr when I test with DEBUG set, but don't see any hints as to what is actually wrong in the logs. There are over 50k lines of logs to go through when the issue occurs, so I am wondering if I can assume the stderr messages are telling us those queries are failing or not. The very first stderr message that prisma printed was this:

prisma:client Prisma Client call: +1115510ms

prisma:client Prisma Client call: +1115510ms

Which looks like the very first call it made took 18 minutes?

VincentOP•7d ago

The last logs before the workers hang entirely, are these.

VincentOP•7d ago

Here is a spreadsheet with the logs broken up by prisma operation into separate sheets if it helps. I don't see anything obviously wrong in the logs at a glance (except for that timestamp perhaps?). I had to obfuscated some of the operations, but that's the full dump otherwise. https://docs.google.com/spreadsheets/d/1W-DkbxLKtitGTEw8UMuescXt1IeuokkvrwFIZD82lpk/edit?usp=sharing

Google Docs

Prisma diagnostics

VincentOP•3d ago

@Nurul (Prisma) after further diagnosis of this issue, I am fairly certain this has something to do with SSL. Although libraryStarted is true for me, I don’t see the logs I would expect to see, and just as the user describes in this issue I am experiencing a completely silent and hung transaction. The process emits no logs from the point after the Prisma promise object is awaited from the transaction. https://github.com/prisma/prisma/issues/15678 this leads me to believe there existed kind of incompatibility between Prisma, OpenSSL and the version of Alpine I was using.

GitHub

libraryEngine silently fails to start (crash in container on Comp...

Bug description I have a Docker container running on Compute Engine. I can confirm with psql that my db connection string is correct. When I run a typscript script, the script exits with no output....

VincentOP•3d ago

So then I found this in the docs: https://www.prisma.io/docs/orm/reference/prisma-schema-reference#binarytargets-options Which definitely was misconfigured in my schema.

Prisma Schema API | Prisma Documentation

API reference documentation for the Prisma Schema Language (PSL).

VincentOP•3d ago

So I think my issue here was that prisma didn't warn when the SSL version was misconfigured at all. But I'll be able to verify if fixing the binaryTarget is the sole solution to my issue

Gaming

Programming

Silent and Stalled transaction

Did you find this page helpful?