Transaction with bad input should rollback (when using middleware)

[Sytten]

Bug description

If one query in the transaction fails the input validation, the transaction doesn't rollback and the other queries proceed normally.

Error:

Error: Argument city for data.address.create.city is missing.

    at Document.validate (/app/node_modules/.prisma/client/runtime/index.js:32462:19)
    at NewPrismaClient._executeRequest (/app/node_modules/.prisma/client/runtime/index.js:34399:17)
    at NewPrismaClient._requestWithMiddlewares (/app/node_modules/.prisma/client/runtime/index.js:34350:19)
    at /app/node_modules/.prisma/client/runtime/index.js:34343:53
    at /app/src/common/prisma/retry.js:35:38
    at NewPrismaClient._requestWithMiddlewares (/app/node_modules/.prisma/client/runtime/index.js:34343:16)
    at /app/node_modules/.prisma/client/runtime/index.js:34332:54
    at AsyncResource.runInAsyncScope (async_hooks.js:197:9)
    at NewPrismaClient._request (/app/node_modules/.prisma/client/runtime/index.js:34332:27)
    at Object.requestTransaction (/app/node_modules/.prisma/client/runtime/index.js:34469:39)

How to reproduce

Consider the following schema:

model User {
  id   Int    @id @default(autoincrement())
  name String
}

Run the following code:

const prisma = new PrismaClient();

prisma.$transaction([
  prisma.user.create({ data: { name: 'emile' } })
  prisma.user.create({ data: {} })
])

There should be a user in the database as the transaction is not rollback.

Expected behavior

The transaction should be rollback.

Environment & setup

• OS: Linux
• Database: PostgreSQL
• Node.js version: 14
• Prisma version: 2.21.2

[pantharshit00]

Hmm, I wasn't able to reproduce this. I don't get the user in the database.

I also enabled postgres logging and checked the database logs and those queries didn't even hit the database:

Maybe the faulty details went missing when you abstracted your query for a reproduction?

[Sytten]

Yeah most likely, just put an example on top of my head but I will try to reproduce. I think it my be due to a children create.

[Sytten]

Ok I dig out the issue, it happens when there is a middleware present.
Here is a simple repro: https://github.com/efugulin/prisma-repro-transaction
Logs:

Example app listening at http://localhost:3000
prisma:query SELECT `main`.`User`.`id`, `main`.`User`.`name`, `main`.`User`.`noteId` FROM `main`.`User` WHERE 1=1 LIMIT ? OFFSET ?
{"id":1,"name":"test","noteId":1}
{"id":2,"name":"test","noteId":2}
PrismaClientValidationError:
Invalid `prisma.user.create()` invocation:

{
  data: {
    name: 'test',
    note: {
      create: {
+       content: String
      }
    }
  }
}

Argument content for data.note.create.content is missing.

Note: Lines with + are required

    at Document.validate (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:32462:19)
    at NewPrismaClient._executeRequest (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34399:17)
    at NewPrismaClient._requestWithMiddlewares (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34350:19)
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34343:53
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/src/prisma.ts:9:26
    at NewPrismaClient._requestWithMiddlewares (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34343:16)
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34332:54
    at AsyncResource.runInAsyncScope (async_hooks.js:173:16)
    at NewPrismaClient._request (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34332:27)
    at Object.requestTransaction (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34469:39)
PrismaClientValidationError:
Invalid `prisma.user.create()` invocation:

{
  data: {
    name: 'test',
    note: {
      create: {
+       content: String
      }
    }
  }
}

Argument content for data.note.create.content is missing.

Note: Lines with + are required

    at Document.validate (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:32462:19)
    at NewPrismaClient._executeRequest (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34399:17)
    at NewPrismaClient._requestWithMiddlewares (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34350:19)
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34343:53
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/src/prisma.ts:9:26
    at NewPrismaClient._requestWithMiddlewares (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34343:16)
    at /Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34332:54
    at AsyncResource.runInAsyncScope (async_hooks.js:173:16)
    at NewPrismaClient._request (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34332:27)
    at Object.requestTransaction (/Users/Sytten/Projects/prisma/prisma-repro-transaction/node_modules/@prisma/client/runtime/index.js:34469:39)
prisma:query BEGIN
prisma:query INSERT INTO `main`.`Note` (`content`) VALUES (?)
prisma:query SELECT `main`.`Note`.`id`, `main`.`Note`.`content` FROM `main`.`Note` WHERE `main`.`Note`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

This is my middleware:

prisma.$use(async (params, next) => {
  try {
    const result = await next(params);
    return result;
  } catch (err) {
    console.log(err);
    throw err;
  }
});

[millsp]

Note: The rollback is ensured by controlling a race condition. In _executeRequest , there is a first block that validates. Then there's that return of this._fetcher.request that is another promise. When running transactions without middlewares, _executeRequest is called for each member of the transaction, thus running the validation. No await is yet called, just execution. Then an exception might have thrown in the validation, program stops. If it has not stopped, it continues to loop over the results of the previous execution, but this time with await. This causes to have a 2-in-1 function that validates then yields. However, with middlewares, since we do await next(params), it hijacks this 2-in-1 system, and renders it useless. Worth mentioning that this works because transactions are not executed immediately but stored in an anonymous fn that comes out of this._fetcher.request. Regular requests don't work like that and are executed immediately. When using middlewares, transactions are marked as regular requests => the reason why they don't rollback. However, adding the transactionId that identifies it as a real transaction fixed the rollback issues but caused this issue you linked.

[millsp]

So in link with what I mentioned above, the current transaction implementation relies on failure immediateness to rollback. For this reason, since middlewares are asynchronous by design and under the hood, we cannot rollback. Doing await next(params) triggers the query to be executed. This is a problem because we expect a result, but at the same time we want previous queries to rollback if needed. Because of this, we cannot rely on immediate failure to rollback, we are awaiting results - one at a time.

The obvious solution now is that we implement #1844

[Sytten]

I mean in my case one of the query fails the validation and is not even sent through to the query engine. Could we at least get that working?

[millsp]

If we "fix" things so that it can fail/stop on Document.validate, we'll end up with a mediocre solution that just stops on the first error. Rollback won't happen and you'll have a partial "transaction" applied. Transactions are sequential, before you move to the next one you wait that the middleware has finished (and that's the query exec). So no rollback like asked in the OP. (this is again because async makes the failure non-immediate).

[matthewmueller]

Depends on #1844