`SELECT FOR UPDATE`

[brandonmpetty]

Problem

To avoid mid-air-collisions in REST there are two primary techniques: Last-Modified / If-Unmodified-Since, and ETags. Prisma makes it extremely difficult to support these techniques.

There is no concept of SELECT FOR UPDATE, which is required to ensure that nothing issues an update underneath me while I am verifying the @updatedAt value for If-Unmodified-Since, or the hash of the select results if I am using ETags.

Suggested solution

I would recommend a solution that uses a callback. This is just one idea. It would create a transaction and use a FOR UPDATE on the SELECT if it detects an 'update' or 'delete' property. My callback is placed in 'update.before', as to not pollute the root property list. If my callback throws, the transaction is broken and we are done. If it goes through, we issue the update... or delete.

It could look like this:

// My own custom 'middleware' allowing me to do what ever I'd like before the update.
const myCallback = result => {
    
    if (!result) {
        throw new HTTPException(204, 'No Content');
    }

    if (result.updatedAt > myLastModifiedDate) {
        throw new HTTPException(412, 'Record has been modified');
    }
};

prisma.user.findUnique({
    where: {
        id: id
    },
    select: {
        updatedAt: true
    },
    update: {
        before: myCallback,
        data: myUpdatedData
    } 
});

Alternatives

There may be a number of ways one could accomplish this.

Additional context

[swipeby-dev]

I just wanted to note that the above was just an example of what this type of solution could solve. For solving the specific problem above, mid-air-collision with updatedAt, it would be recommended to blindly apply the update WHERE updatedAt has not changed, and if it fails then select to see if it existed (if you need to know that). This is made more difficult with Prisma due to the lack of filtering by non-unique values even if the where clause is guaranteed to always be unique.

I also wanted to note that .Net's Entity framework does not appear to offer any type of select for update capability.

[ziimakc]

I would suggest to pass around a transaction object in "interactive transactions" as argument, like here: https://vincit.github.io/objection.js/guide/transactions.html#using-a-transaction

export class SmthModel{
  static async checkUniqueName(name, trx?: Transaction) {
    const isNameUsed = await this.client.smth.findFirst({ where: { name }, trx});

    if (isNameUsed) {
      throw new Error();
    }
  }
}

function handler() {
    return prisma.$transaction(async (trx) => {
      const smth = await this.client.smth.findUnique({
        where: { id: 1 },
        for: 'forUpdate' // or forShare an so on
        trx
      });

      await SmthModel.checkUniqueName(smth.name, trx);

     // update smth.name
    });
}

[GuiSim]

FOR UPDATE is a core tool for achieving atomic guarantees for distributed applications.

Here are some issues that have been closed but aren't totally resolved without better FOR UPDATE mechanism: https://github.com/prisma/prisma/issues/1918 https://github.com/prisma/prisma/issues/1986

And this issue would also be avoidable with FOR UPDATE by enabling developers to manually serialize operations https://github.com/prisma/prisma/issues/8668

[bodinsamuel]

Hey, I will respectfully +1 since it's not been udpated in a long time. Transactions and Nested Write are great and solve many issues, but locking for update (and all the others keywords https://www.postgresql.org/docs/current/explicit-locking.html) have very specific meaning that are not solved by the proposed solutions right now.

It would be great to have more updates on this since it's a core feature in many databases and makes the switch to Prisma quite harder.

[Thinkscape]

I'm having bad time trying to work around this mess with .executeRaw and that not working very well because of how Prisma does things.

Transactionality is the prime reason to use RDBMS in the first place, and Prisma gets in the way of that...

Related to #1918

[janpio]

Potentially related: https://github.com/prisma/prisma/issues/17136 https://github.com/prisma/prisma/issues/5983

[JasperSui]

I really hope this can be done with Prisma instead of using weird workarounds or executing a bunch of raw SQL queries, so here is my +1 for this feature 🙏

[rsimonton]

Lobbing another +1, this is potentially a deal breaker for us

[alexey-sh]

Almost three years, and still there. And this is basic functionality. Is there any ETA?

[dautushenka]

+1

[yourconst]

Hello, maintainers! It is basic functionality. Why you not implement it? 3 years awaiting is so bad. Add fast some flag to "select" queries (it may be platform depend flag as temporary solution).

Or give us ETA at least

[michaelperros]

I was able to implement SELECT FOR UPDATE in Prisma following the info in this comment: https://github.com/prisma/prisma/issues/17136#issuecomment-2272195564

It was pretty straightforward

[qisaw]

Would love to not have to write workarounds for this so here's another +1

[junajan]

it is a shame that Prisma has no support for this core feature +1

[romanticsoul]

If we take an example of a transaction from the Prisma documentation - example, where Alice and Bob have $ 100 each and quickly trigger a transaction twice, for example by simultaneously clicking on a button from two browser tabs, this will lead to Alice having -100$ and Bob having 300$

+1

[rivatove]

If we take an example of a transaction from the Prisma documentation - example, where Alice and Bob have $ 100 each and quickly trigger a transaction twice, for example by simultaneously clicking on a button from two browser tabs, this will lead to Alice having -100$ and Bob having 300$

+1

SELECT FOR UPDATE is crucial for atomic operations. It's a shame that a tool as mature as Prisma still hasn't implemented it. I'm certain it blocks many enterprise clients from using the library.

But the example from the docs should work because it uses an update query.

[rupin-rk]

@romanticsoul being person 1 and @rivatove being person 2; this is what chatgpt has to say:

The situation described in the Prisma documentation example has potential for race conditions, and person 1 is correct. Here's why:

Analysis of the Example

Transaction Logic Recap:

• Step 1: Decrement the sender's balance.
• Step 2: Check if the sender's balance went below zero. If yes, throw an error and roll back.
• Step 3: Increment the recipient's balance.

Potential Issue:

• Two transactions triggered in parallel (e.g., two clicks on the "Transfer" button from different browser tabs) can execute concurrently.
• If these transactions overlap, they may both decrement the sender's balance without seeing the updated balance from the other transaction. This can result in incorrect balances (e.g., Alice ends up with a negative balance).

Why does this happen?

• PostgreSQL's READ COMMITTED isolation level (the default) allows each query within a transaction to see the latest committed state. This means:
  • When Transaction A and Transaction B read Alice's balance simultaneously, they might both see the same starting balance of $100.
  • Each transaction then decrements the balance independently and checks if it's valid, leading to inconsistent results.

Why SELECT ... FOR UPDATE Would Help

Using SELECT ... FOR UPDATE ensures that:

• The row being updated (Alice's account) is locked by the first transaction.
• The second transaction is forced to wait until the first transaction completes before it can proceed.

This guarantees that only one transaction can decrement Alice's balance at a time, avoiding the race condition.

Person 2's Argument

Person 2 claims that "the example should work because it uses an update query." However:

• Incorrect Assumption: An UPDATE query in itself does not prevent race conditions unless combined with locking.

  • In the example, tx.account.update() is simply modifying the balance but does not ensure that the check (if sender.balance < 0) is performed atomically with the update.
  • Without row-level locks, two transactions can decrement the same balance simultaneously, leading to inconsistent states.

• Correct Point: Prisma does use a single SQL UPDATE statement for decrement operations. However, the atomicity of that statement does not extend to the subsequent logic in the transaction (e.g., checking the balance). This gap allows race conditions.

Solution: Adding SELECT ... FOR UPDATE

To make this example robust, you can:

1. Use SELECT ... FOR UPDATE to lock the row when reading the sender's balance.
2. Ensure that no other transaction can modify the row until the current transaction completes.

In Prisma, you would need to use a raw SQL query for this since Prisma currently does not natively support SELECT ... FOR UPDATE:

await prisma.$transaction(async (tx) => {
  // Lock the sender's row
  const sender = await tx.$queryRaw`SELECT id, balance FROM account WHERE email = ${from} FOR UPDATE`

  if (sender.balance < amount) {
    throw new Error(`${from} doesn't have enough to send ${amount}`)
  }

  // Decrement sender's balance
  await tx.account.update({
    data: {
      balance: {
        decrement: amount,
      },
    },
    where: {
      email: from,
    },
  })

  // Increment recipient's balance
  await tx.account.update({
    data: {
      balance: {
        increment: amount,
      },
    },
    where: {
      email: to,
    },
  })
})

Conclusion

• Person 1 is correct: The example as written in the documentation is susceptible to race conditions without proper row-level locking.
• Person 2 is incorrect: The fact that update queries are used does not inherently solve the race condition without additional safeguards like FOR UPDATE.

Prisma's lack of built-in support for SELECT ... FOR UPDATE is indeed a limitation, but workarounds using raw SQL can mitigate this issue.

[rupin-rk]

counter to chatGPT:

while this is true for read queries; prisma's example uses update query. if two transaction simultaneously try to update the same row; wouldn't second transaction wait for the first one to finish or rallback even if isolation level is read commited?

response:

You're absolutely correct that UPDATE operations in PostgreSQL inherently acquire row-level locks, even under the READ COMMITTED isolation level. This means the second transaction will wait for the first transaction to complete (commit or rollback) before proceeding. Let’s revisit the Prisma example in light of this.

How UPDATE Queries Handle Concurrent Transactions

In PostgreSQL:

• When a row is updated, a row-level exclusive lock (RowExclusiveLock) is acquired.
• This lock prevents other transactions from modifying the same row until the current transaction finishes.
• Concurrent UPDATE queries on the same row will result in the second transaction waiting for the first to complete.

In the Prisma Example

Here’s what happens when two transactions simultaneously execute the transfer function:

1. First Transaction:

• Executes the UPDATE query to decrement Alice's balance.
• Acquires an exclusive lock on Alice's row.
• Continues to step 2 to check Alice's balance.

2. Second Transaction:

• Attempts to execute the UPDATE query to decrement Alice's balance.
• Encounters the exclusive lock held by the first transaction and is forced to wait.

3. Resolution:

• If the first transaction commits successfully, the second transaction will see the updated balance after acquiring the lock and proceed accordingly.
• If the first transaction rolls back, the second transaction will proceed as if the first transaction never happened.

Why This Example Works

Because both transactions use UPDATE, PostgreSQL's locking mechanism prevents a race condition. The second transaction cannot modify the row until the first transaction releases its lock, ensuring consistency even at the READ COMMITTED isolation level.

Person 1's Concern

Person 1 argues that this example could allow Alice to end up with a negative balance. However, this can only happen if the balance check (if sender.balance < 0) is not coupled with the UPDATE. In the Prisma example:

• The UPDATE and subsequent balance check happen within the same transaction.
• The row lock ensures that the second transaction sees a consistent state (post-commit of the first transaction).

Thus, the example is safe as written because:

1. The UPDATE query locks the row.
2. Other transactions are blocked until the lock is released.

Person 2's Argument

Person 2 is correct in stating that the use of an UPDATE query ensures row-level locking. This inherent behavior of PostgreSQL makes the example work even without SELECT ... FOR UPDATE.

Summary

• Person 1 is incorrect: The example is not vulnerable to race conditions because the UPDATE query locks the row, ensuring serial execution of concurrent transfers.
• Person 2 is correct: The row lock from the UPDATE query makes FOR UPDATE unnecessary in this scenario.

Additional Notes

If the example were using SELECT to check the balance before updating (instead of directly decrementing it in the UPDATE), then FOR UPDATE would be required to prevent race conditions. This is likely the confusion in person 1's argument.

[rivatove]

@janpio Is there a way to bump the priority of this issue slightly with a bounty? Not talking about anything immediate but maybe to consider it the next time you prepare a roadmap for a quarter?

[shoxter]

It's hard to imagine something as critical to serious software as row level read locks is missing from this library. We weren't using it for a week before this was exposed.

If you aren't doing dead simple Todo List type apps, this isn't even optional to go without at any reasonable scale.

[coulter-fullgood]

+1