Sequential transactions run out of order when using client extensions and middleware

[LucianBuzzo]

Bug description

When using a batch transaction inside a client query extension, if you are also using a middleware that uses await then the batch transaction will run out of order.

How to reproduce

The bug can be easily demonstrated using the example given in the docs for batch transactions in queries, including SET ROLE statements.

Schema:

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

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}

model User {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
}

enum Role {
  USER
  ADMIN
}

Minimal reproduction:

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient({
	log: ["query"],
});

prisma.$use(async (params, next) => {
	await "test";

	return next(params);
});

const run = async () => {
	try {
		await prisma.$executeRawUnsafe("CREATE ROLE test_user");
	} catch (e) {}

	const xprisma = prisma.$extends({
		query: {
			user: {
				async findFirst({ args, query }) {
					const [_, result] = await prisma.$transaction([
						prisma.$queryRawUnsafe("SET ROLE test_user"),
						query(args),
						prisma.$queryRawUnsafe("SET ROLE none"),
					]);
					return result;
				},
			},
		},
	});
	const result = await xprisma.user.findFirst();

	console.log(result);
};

run();

The SQL is then executed out of order:

prisma:query CREATE ROLE test_user
prisma:query BEGIN
prisma:query SELECT "public"."User"."id", "public"."User"."createdAt", "public"."User"."email", "public"."User"."name", "public"."User"."role" FROM "public"."User" WHERE 1=1 LIMIT $1 OFFSET $2
prisma:query SET ROLE test_user
prisma:query SET ROLE none
prisma:query COMMIT

If you comment out the await 'test' line, then the transaction will run sequentially.

Expected behavior

I expect batch transactions to run sequentially when combining query extension and middleware with await statement.

Prisma information

// Add your schema.prisma

// Add your code using Prisma Client

Environment & setup

• OS: macOS
• Database: PostgreSQL 11
• Node.js version: 16.8.0

Prisma Version

prisma                  : 4.9.0
@prisma/client          : 4.9.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.9.0-42.ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5
Default Engines Hash    : ceb5c99003b99c9ee2c1d2e618e359c14aef2ea5
Studio                  : 0.479.0
Preview Features        : clientExtensions

[LucianBuzzo]

I think it's also worth adding that using an interactive transaction doesn't work here either, though for a different reason. With the following code, the query(args) statement runs outside of the transaction entirely:

const xprisma = prisma.$extends({
	query: {
		user: {
			async findFirst({ args, query }) {
				const result = await prisma.$transaction(async (tx) => {
					await tx.$queryRawUnsafe("SET ROLE test_user");
					const res = await query(args);
					await tx.$queryRawUnsafe("SET ROLE none");
					return res;
				});

				return result;
			},
		},
	},
});

[LucianBuzzo]

You can force the query to run in a transaction by "reconstructing" the prisma query like so:

const result = await prisma.$transaction(async (tx) => {
    await tx.$queryRawUnsafe("SET ROLE test_user");
    const res = await (tx as any)[model][operation](args);
    await tx.$queryRawUnsafe("SET ROLE none");
    return res;
})

However, this will result in middleware running again for the reconstructed query, which is highly undesirable.
You can bypass this by using a secondary PrismaClient, which doesn't have any middleware attached to it, but then we run into issues with connection pool management, as we can't (AFAICT) share a connection pool between multiple clients.

[LucianBuzzo]

We eventually solved this by using private methods of Prisma to bypass the middleware, it's not ideal, but it works for now:

// The interactive transaction ensures that the queries run in the correct order
const queryResults = await prisma.$transaction(async (tx) => {
    await tx.$queryRawUnsafe(`SET ROLE test_user`);
    // We need to manually reconstruct the query, and attached the "secret" transaction ID.
    // This ensures that the query will run inside the transaction AND that middlewares will not be re-applied
    // https://github.com/prisma/prisma/blob/4.11.0/packages/client/src/runtime/getPrismaClient.ts#L1013
    const txId = (tx as any)[Symbol.for("prisma.client.transaction.id")];
    // See https://github.com/prisma/prisma/blob/4.11.0/packages/client/src/runtime/getPrismaClient.ts#L860
    const __internalParams = (params as any).__internalParams;
    const result = await prisma._executeRequest({
        ...__internalParams,
        // The transaction data isn't included in __internalParams
        transaction: {
            kind: "itx",
            id: txId,
        },
    });
    // Switch role back to admin user
    await tx.$queryRawUnsafe("SET ROLE none");

    return result;
});

[andyjy]

Also hit this; big thanks and +100 helpfulness points to @LucianBuzzo for the clear + detailed explanation.

Successfully using your workaround in the last comment for now (tweaked to use the _executeRequest() call from your linked repo in place of the reference to __internalParams currently included in the comment above):

...
// See https://github.com/prisma/prisma/blob/4.11.0/packages/client/src/runtime/getPrismaClient.ts#L860
const result = await prisma._executeRequest({
  args,
  clientMethod: `${model.toLowerCase()}.${operation}`,
  jsModelName: model.toLowerCase(),
  action: operation,
  model,
  transaction: {
    kind: "itx",
    id: txId,
  },
});
...

[andyjy]

...neither forms of the workaround work with the prisma data proxy 😢

[janpio]

Can you open an issue for that please @andyjy? In a separate thread with all the information we might be able to debug and hopefully fix this.

[andyjy]

@janpio sorry, to clarify I was referring to how the workaround figured out by @LucianBuzzo using the private _executeRequest method of Prisma to bypass the middleware doesn't work with the data proxy. This is unsurprising since after brief inspection of the prisma codebase I saw the data proxy requires additional internal parameters which are not set by the workaround.

I did explore briefly whether something additional could be done to make it work but with the internals of how Prisma processes requests there were too many unknowns.

Glad to file a separate issue or anything else that may be helpful - but given this is messing with the private / undocumented internals of Prisma I don't really consider it an issue requiring fixing per-se.

It would be wonderful to have the original issue posted here by @LucianBuzzo resolved so that transactions within client extensions don't run out of order when combined with middleware that uses await. Once that is resolved none of the other discussion above should be relevant. Thanks!

[andyjy]

I realised it's possible to "convert" middleware to a client extension by wrapping it:

function middlewareAsClientExtension(middleware: Prisma.Middleware) {
  return Prisma.defineExtension(
  (prisma) =>
    prisma.$extends({
      name: "middleware-as-client-extension",
      query: {
        $allModels: {
          async $allOperations({ args, model, operation, query }) {
            // wrap original query in middleware
            return await middleware(
              {
                action: operation as Prisma.PrismaAction, // TODO: not exactly equivalent
                args,
                model,
                runInTransaction: false, // TODO: fix when prisma exposes this to client extensions
                dataPath: [], // TODO: don't know what this is
              },
              (params) => query(params.args)
            );
          },
        },
      },
    }) as GenericPrismaClientExtended
  );
}

// before:
// prisma.$use(myMiddleware);
// const prismaClient = prisma;

// after:
const prismaClient = prisma.$extends(middlewareAsClientExtension(myMiddleware));

In my testing this provides a temporary workaround for the original issue in a "cleaner" way - i.e. without accessing Prisma internals - and also works with the Prisma Data Proxy.

[andyjy]

Update: I don't think this is middleware-specific - I hit this same issue (transactions running out of order) when extending a client that had previously been extended with a no-op $allModels.$allOperations:

  return Prisma.defineExtension({
    query: {
      $allModels: {
        $allOperations({ args, model, operation, query }) {
          console.log(`prisma $allOperations: ${model}.${operation}`);
          return query(args);
        },
      },
    },
  });

[andyjy]

Partial-diagnosis of what I suspect causes the original issue:

Batch transactions are implemented via _transactionWithArray(), which in turn calls waitForBatch() with the array of requests:

prisma/packages/client/src/runtime/getPrismaClient.ts

Line 741 in c91381c

return waitForBatch(requests)

waitForBatch() then iterates over each PrismaPromise value calling .then() - and in particular does so without awaiting each call to return before proceeding to call the next:

prisma/packages/client/src/runtime/utils/waitForBatch.ts

Lines 51 to 56 in c91381c

	for (let i = 0; i < promises.length; i++) {
	promises[i].then(
	(result) => {
	successfulResults[i] = result
	settleOnePromise()
	},

My assumption here is that under typical usage, this causes each request to be started in the correct order, as expected.

However - when execution of a particular request is drawn out (e.g. via middleware that calls await) to delay the eventual request to the database server to a subsequent tick of the event loop(?), the requests will reach the database in a different order vs. expected.

[LucianBuzzo]

@andyjy Yep that definitely looks like the culprit!

[jadamduff]

@andyjy I've hit this issue and it doesn't seem to be specific to interactive transactions. I have a non-interactive transaction that is also running out of order when one of the queries is extended.

[andyjy]

@jadamduff yes! The issue affects batch transactions, not interactive transactions.

(The references to interactive transactions above are attempts to find a workaround; most clearly illustrated in @LucianBuzzo's first comment under his original post. If I've written anything above that introduces confusion regarding interactive transactions please quote it and I'll try edit to clarify. Thanks!)

[LucianBuzzo]

Whoop! Awesome, thanks @SevInf

[SevInf]

Fix will be published with the next Prisma release.
If you you want to check out dev snapshot, you can use version 4.16.0-dev.21, however, we do not recommend you use dev snapshots in production.

[adamnyberg]

Did this issue pop up again? 🤔

I'm using an extended prisma client like below and my list of queries does not always run in order. Or is it that I'm using a transaction inside of my extended prisma client that is the problem?

prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ model, args, query }) {
          const organizationId = getClaimedOrgId();
          if (!organizationId) {
            throw new Error('Organization ID not found in context');
          }

          const [, result] = await prisma.$transaction([
            prisma.$executeRaw`SELECT set_config('app.orgId', ${`${organizationId}`}, TRUE)`,
            query(args),
          ]);
          return result;
        },
      },
    },
  })

[janpio]

Please open a new issue @adamnyberg, then we can properly react to this. Thanks.