Row locking support in find*() queries (via FOR UPDATE)

[thetminko]

Problem

I just migrated from Sequelize and was quite a fan of Prisma for being type safe. Would Prisma be able to support SELECT FOR UPDATE row locking like Sequelize supports? Understand that Optimistic locking is preferred by Prisma but it would be great if we have the row locking mechanism too.
My problem: When multiple requests call this function concurrently, while first transaction is still processing, the subsequent transaction will see that the user is still active and do the processing again. If we could avoid it by locking the row, that would be great.

function deactivate() {
  const user = await prisma.$transaction(async tx => {
   const user = await tx.user.findUnique({ where: { id: user.id } });
   if (user.status !== 'ACTIVE') {
     throw new Error('User not active ...');
  }
  // some business processing (not long transaction)
  return tx.user.update({ where: { id: user.id } }, data: {  status: 'DELETED' });
 });

   await doAfterCommitProcessing(user);
}

Suggested solution

const user = await tx.user.findUnique({ where: { id: user.id }, lock: FOR_UPDATE });
So, the user record with specific id is locked till the transaction is completed.

Alternatives

Additional context

• https://www.cockroachlabs.com/blog/select-for-update/
• https://www.cockroachlabs.com/docs/stable/select-for-update.html
• https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

[gomain]

Yes, I too have a use case where I want racing reads to block.

[luxaritas]

Is this a duplicate of #5983?

[janpio]

Yep, sounds similar but a bit ore specific maybe. Will clean up when we look at the issue in depth.

[mweel1]

Same here.

  await prisma.$transaction(
      async (tx) => {
        let user = await tx.user.findFirst({
          where: {
            id: req.auth.userId,
          },
        });

        // update the user
        if (user) {
          const userWorkspace = await tx.userWorkspace.findFirstOrThrow({
            where: {
              userId: req.auth.userId,
              id: user.defaultWorkspaceId,
            },
          });

          workspaceId = userWorkspace.workspaceId;

          var updateUser = await tx.user.update({
            where: {
              id: req.auth.userId,
            },
            data: {
              fullName: req.body.fullName,
              email: req.body.primaryEmailAddress.emailAddress,
              auth_json: JSON.stringify(req.body),
            },
          });
        } else {
          workspaceId = "Personal";
          var userWorkspace = await tx.userWorkspace.create({
            data: {
              userId: req.auth.userId,
              label: "Personal",
              workspaceId: "Personal",
            },
          });

          let newUser = await tx.user.create({
            data: {
              id: req.auth.userId,
              fullName: req.body.fullName,
              email: req.body.primaryEmailAddress.emailAddress,
              auth_json: JSON.stringify(req.body),
              defaultWorkspaceId: userWorkspace.id,
            },
          });
        }
      },
      {
        isolationLevel: Prisma.Prisma.TransactionIsolationLevel.Serializable,
      }

Getting this error when two threads are inserting the same user, the read should to see if the user exists if another transaction is working on it.

[gomain]

The current work-around is to either use $rawQuery to place FOR UPDATE. Or artificially place a mutex field (typically a boolean) and select via updating the field to true (lock it) where it is false (not locked).

This is important if the activities before updating the row involves other side effects that we definitely don't want to happen if the transaction would fail.

[KarolScibior]

Any news / plans regarding this feature?

[AlejandroFrias]

Another friendly ping. I have that exact user create error. But also am trying to avoid accruing interest on a loan multiple times in the same day on a background job that might retry randomly.

[esmaeilzadeh]

Is there any plan to add "lock for update" feature to Prisma transactions?

[devSajan]

yes, I have the same requirement, when deleting a record another request can access it which should not happen.

[janpio]

Potentially related:
#8580
#5983

[easdkr]

Is there any plan to add "lock" (shared, exclusive, update... etc lock) to findXXX method?