Concurrent request (Prisma)
Hello,
I have a problem with Prisma on PostgreSQL. I don't know how to fix it
Context
I have a questionnaire to answer. When the person click on the questionnaire, I create entry in the database. The backend sends the generated line ID (UUID) to the frontend. Then, each time the user's answers on the questionnaire are modified, the frontend sends the data to the backend, which updates them using this UUID
Problem: in rare cases, if I send the query twice in a row, it may create two rows in the database
Constraint: I can't have a unique constraint in the answers table, as a questionnaire can sometimes (not always) have several answers
Any advice on how to create a lock with Prisma / PostgreSQL to avoid having 2 inserts at the same time for the same user?
Thanks!4 Replies
Hi @Mathias
Have you seen the guide on Optimistic Concurrency Control ? This would involve adding a version field to each entry that increments with every update. With this setup, you can implement checks in your update logic, ensuring that only a single version is active.
Transactions and batch queries (Reference) | Prisma Documentation
This page explains the transactions API of Prisma Client.
Hello @RaphaelEtim
Thank you!
The problem is that my concurrent request is an
insert.
I have a findUnique only to check if the answer exists or not.
I don't think the version field can solve the problem.
If I use an INSERT in a transaction, with Prisma.TransactionIsolationLevel.Serializable, can this solve the problem?While not fool proof, nothing is fool or Sailor proof, disabling the submit button until the request finishes nixes a bunch of these
@Mathias You could look at wrapping your insert operation in a transaction with the
Serializable isolation level. This ensures that concurrent operations are executed as if they were run serially, reducing the chance of duplicate inserts.
This section of the documentation might help
https://www.prisma.io/docs/orm/prisma-client/queries/transactions#transaction-timing-issuesTransactions and batch queries (Reference) | Prisma Documentation
This page explains the transactions API of Prisma Client.