Middleware has different behavior for transactions in array vs. interactive transactions.

[AndrewSouthpaw]

Bug description

We have implemented retry middleware to handle various retryable errors or transactions that come back from CockroachDB (retryable transactions, connection draining, etc.). A very simplified, not-actually-used-in-prod-because-it-would-be-a-bad-idea version is here:

export const retryWithExponentialBackoff = async (
  params: Prisma.MiddlewareParams,
  next: (params: Prisma.MiddlewareParams) => Promise<unknown>,
) => {
  for (let i = 0; i <= MAX_ATTEMPTS; i++) {
    try {
      return await next(params);
    } catch (e) {
      if (i === MAX_ATTEMPTS) {
        throw e;
      }
      await sleep(100);
    }
  }
};

I've discovered that this middleware will have different outcomes for using $transaction, depending on whether I use the array form or interactive transaction:

// array
await db.$transaction([createARecord, failAQuery])
// itx
await db.$transaction((tx) => {
  await createARecord()
  await failAQuery()
})

If in array form, after running you will still have a record created from createARecord (unexpected). If in itx form, there will be no record afterward (expected).

It seems related to #7584, but that issue was about blocking query execution, my concern is about what appears to be not honoring transaction guarantees.

How to reproduce

Prisma model:

model Test {
  id String @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))
  foo String
}

App code (roughly, omitting some boilerplate):

export const retryMiddleware = async (
  params: Prisma.MiddlewareParams,
  next: (params: Prisma.MiddlewareParams) => Promise<unknown>,
) => {
  for (let i = 0; i <= MAX_ATTEMPTS; i++) {
    try {
      return await next(params);
    } catch (e) {
      if (i === MAX_ATTEMPTS) {
        throw e;
      }
      await sleep(100);
    }
  }
};

prismaDb.$use(retryMiddleware)

And finally two tests. The first one fails, the second one does not.

it("❌ retry middleware violates transactions in array form", async () => {
  try {
    await prismaDb.$transaction([
      prismaDb.test.create({ data: { foo: "hello" } }),
      prismaDb.test.create({ data: { foo: null } }),
    ]);
  } catch (e) {
    /* noop */
  }
  expect(await prismaDb.test.count()).toEqual(0); // fails
});

it("✅ retry middleware does not violate transactions in itx form", async () => {
  try {
    await prismaDb.$transaction(async (tx) => {
      await tx.test.create({ data: { foo: "hello" } });
      await tx.test.create({ data: { foo: null } });
    });
  } catch (e) {
    /* noop */
  }
  expect(await prismaDb.test.count()).toEqual(0); // passees
});

Expected behavior

The same outcome happens for both forms of $transaction, where no records are created after running the transaction with the middleware.

Discussion

If I console.log the params before calling await next(params), we get this output for the array output case:

    call 0 with params {
      args: { data: { foo: 'hello' } },
    }

    call 0 with params {
      args: { data: { foo: null } },
    }

    call 1 with params {   <-- this call does not show up in the itx case
      args: { data: { foo: 'hello' } },
    }

    call 1 with params {
      args: { data: { foo: null } },
    }

    call 2 with params {
      args: { data: { foo: null } },
    }

    call 3 with params {
      args: { data: { foo: null } },
    }

    call 4 with params {
      args: { data: { foo: null } },
    }

    call 5 with params {
      args: { data: { foo: null } },
    }

And this for the itx case, notice it doesn't ever call with the good data payload again:

    call 0 with params {
      args: { data: { foo: 'hello' } },
    }

    call 0 with params {
      args: { data: { foo: null } },
    }

    call 1 with params {
      args: { data: { foo: null } },
    }

    call 2 with params {
      args: { data: { foo: null } },
    }

    call 3 with params {
      args: { data: { foo: null } },
    }

    call 4 with params {
      args: { data: { foo: null } },
    }

    call 5 with params {
      args: { data: { foo: null } },
    }

Here are some other issues that have been filed, which all seem related but don't exactly articulate the issue I'm raising, so I figured I'd keep it separate for now:

• A transaction rollback should go through Prisma middleware #13851
• Transaction with bad input should rollback (when using middleware) #6705
• Middleware + $transactions do not play well together #7584

Prisma information

shown above

Environment & setup

• OS: macOS 12.4
• Database: CockroachDB 22.2.8
• Node.js version: 16.10.0

Prisma Version

prisma                  : 4.13.0
@prisma/client          : 4.13.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.13.0-50.1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a
Default Engines Hash    : 1e7af066ee9cb95cf3a403c78d9aab3e6b04f37a
Studio                  : 0.484.0
Preview Features        : tracing, extendedWhereUnique

[sabinadams]

I appreciate the thorough details here! Taking a look now and will keep you updated

[janpio]

I can confirm the behavior. Unclear for now, where something is going wrong here.

[janpio]

Investigation into this surfaced 2 related smaller inconveniences around $transaction([...]) batch transactions: #19202 + #19203

[janpio]

@sabinadams will have a more thorough analysis later, but here is already an issue about preventing or fixing this problem in the future potentially: #19204

[AndrewSouthpaw]

Thanks for the updates so far!

[sabinadams]

Hey there Andrew!

After investigating the issue and some discussion with the engineering team we have determined that this issue is caused due to the fact that all queries in a Sequential Transaction (array-based transaction) must be passed into a single $transaction call in the same event loop. Attempting a retry using the next function within a middleware causes the retry to occur in another event loop, and is thus not recognized as being part of the batch.

The issue Jan linked above (#19204) is what we believe needs to be done to solve this issue.

In the meantime, as you have found, re-implementing this as an Interactive Transaction will work as those behave differently than the array-based transactions.

Alternatively, you could also take advantage of Prisma Client Extensions to achieve this behavior. There is even an example here: https://github.com/prisma/prisma-client-extensions/tree/main/retry-transactions

Please let me know if you have any questions or concerns. I'd be happy to give a hand where I can to get past this hurdle.