Promise.all in interactive $transaction unexpectedly merges operations

[ludralph]

Discussed in #23845

^{Originally posted by @ArthurMelin April 10, 2024}

Bug description

We found some weird behavior when using Promise.all inside interactive transactions.
The two queries inside the Promise.all get merged together.

How to reproduce

Inside an interactive $transaction, make two findUnique queries, each for a different unique field, batched together with a Promise.all (to hopefully reduce impact of network latency).

Expected behavior

Two queries should be made. The result of Promise.all should contain the result of each one.

Instead, only one query is made with a merge of the where conditions of both operations. The result of Promise.all contains the result of the query and a null.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client_js {
  provider      = "prisma-client-js"
  output        = "../node/prisma"
  binaryTargets = ["debian-openssl-1.1.x", "debian-openssl-3.0.x", "linux-musl"]
}

// ...

model User {
  user_id            String    @id @default(uuid())
  email              String    @unique
  // ...
  stripe_customer_id String?   @unique
}

await this.db.$transaction(async (tx) => {
  const [userMatch, customerMatch] = await Promise.all([
      tx.user.findUnique({ where: { user_id }),
      tx.user.findUnique({ where: { stripe_customer_id }}),
  ]);
  console.log("userMatch", userMatch);
  console.log("customerMatch", customerMatch);
});

Debug logs

prisma:query BEGIN
prisma:client Prisma Client call: +473ms
prisma:client prisma.user.findUnique({
  where: {
    user_id: "805aa449-3120-458b-9bf9-0c3beef0d47e"
  }
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
  "modelName": "User",
  "action": "findUnique",
  "query": {
    "arguments": {
      "where": {
        "user_id": "805aa449-3120-458b-9bf9-0c3beef0d47e"
      }
    },
    "selection": {
      "$composites": true,
      "$scalars": true
    }
  }
}
 +0ms
prisma:client Prisma Client call: +0ms
prisma:client prisma.user.findUnique({
  where: {
    stripe_customer_id: "cus_PtSOvgsLPWntH7"
  }
}) +0ms
prisma:client Generated request: +0ms
prisma:client {
  "modelName": "User",
  "action": "findUnique",
  "query": {
    "arguments": {
      "where": {
        "stripe_customer_id": "cus_PtSOvgsLPWntH7"
      }
    },
    "selection": {
      "$composites": true,
      "$scalars": true
    }
  }
}
 +0ms
prisma:client:libraryEngine requestBatch +1ms
prisma:query SELECT "public"."user"."user_id", "public"."user"."enabled", "public"."user"."email", "public"."user"."email_confirmed", "public"."user"."roles", "public"."user"."created_at", "public"."user"."last_login", "public"."user"."stripe_customer_id" FROM "public"."user" WHERE ("public"."user"."user_id" = $1 OR "public"."user"."stripe_customer_id" = $2) OFFSET $3
userMatch {
  user_id: '805aa449-3120-458b-9bf9-0c3beef0d47e',
  enabled: true,
  email: 'arthur.melin@wiseone.io',
  email_confirmed: false,
  roles: [],
  created_at: 2024-04-09T15:03:25.406Z,
  last_login: 2024-04-09T15:03:25.889Z,
  stripe_customer_id: 'cus_PtSOvgsLPWntH7'
}
customerMatch null
prisma:query COMMIT

Environment & setup

• OS: Arch Linux on WSL 2 (5.15.133.1-microsoft-standard-WSL2)
• Database: PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (docker)
• Node.js version: v21.7.2

Prisma Version

prisma                  : 5.12.1
@prisma/client          : 5.12.1
Computed binaryTarget   : debian-openssl-3.0.x
Operating System        : linux
Architecture            : x64
Node.js                 : v21.7.2
Query Engine (Node-API) : libquery-engine 473ed3124229e22d881cb7addf559799debae1ab (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine           : schema-engine-cli 473ed3124229e22d881cb7addf559799debae1ab (at node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm             : @prisma/prisma-schema-wasm 5.12.0-21.473ed3124229e22d881cb7addf559799debae1ab
Default Engines Hash    : 473ed3124229e22d881cb7addf559799debae1ab
Studio                  : 0.499.0
```</div>

[ludralph]

I was able to reproduce this behaviour for interactive $transactions.

I also tried using the sequential operation of the $transaction api and observed that a merge is actually happening. For the simple query below:

const [userMatch, customerMatch] =  await prisma.$transaction([
   prisma.user.findUnique({ where: { user_id: 'af542b08-c800-4639-8ebd-7d2c4efb8386' }}),
   prisma.user.findUnique({ where: { stripe_customer_id: 'cus_JJZ2Z6J9JZ2Z678J' }}),
  ])

I was expecting two distinct results but the response i got was null for both userMatch and customerMatch.
This is the log from running the query

prisma:query BEGIN
prisma:query SELECT "public"."User"."user_id", "public"."User"."email", "public"."User"."stripe_customer_id" FROM "public"."User" WHERE ("public"."User"."user_id" = $1 AND "public"."User"."stripe_customer_id" = $2) OFFSET $3
userMatch null
customerMatch null
prisma:query COMMIT