unique constraint failed on where column? how is possible?
In production, once in a while our customer gets an error on the backend """
Invalid prisma.trainerProposalXrefDbo.update() invocation:
Unique constraint failed on the fields: (id)"""
on the following code """ const promise1 = txClient.trainerProposalXrefDbo.update({
where: {
id: trainerPropXref.id,
proposal_id: trainerPropXref.proposal_id
},
data: {
stage: TrainerProposalStageEnum.CONFIRMED
}
});"""
How is this even possible? I could undertstand violation of a uniqueness column if in the data block but not the where block. I am not sure the stack trace helps
at qn.handleRequestError (/home/node/app/server/node_modules/@prisma/client/runtime/library.js:121:7315)
at qn.handleAndLogRequestError (/home/node/app/server/node_modules/@prisma/client/runtime/library.js:121:6640)
at qn.request (/home/node/app/server/node_modules/@prisma/client/runtime/library.js:121:6347)
at l (/home/node/app/server/node_modules/@prisma/client/runtime/library.js:130:9616)
at async Promise.all (index 0)
at /home/node/app/server/src/controllers/customer/customerController.ts:987:13
at Proxy._transactionWithCallback (/home/node/app/server/node_modules/@prisma/client/runtime/library.js:130:7983)
at CustomerController.runTransactionToAdd (/home/node/app/server/src/controllers/customer/customerController.ts:868:31)
at CustomerController.confirmBook (/home/node/app/server/src/controllers/customer/customerController.ts:824:25)
at /home/node/app/server/src/controllers/customer/customerApiRouting.ts:123:455 Replies
You've gone full bot mode! Let me fetch that AI answer for you ASAP.
The "Unique constraint failed on the fields: (
id)" error you're experiencing in your Prisma update operation is indeed puzzling since it's occurring in a where clause rather than in the data block.
Based on the knowledge sources, this issue can occur in several scenarios:
1. Race conditions in transactions: When multiple operations try to update the same record simultaneously, one might succeed while others fail with a unique constraint error. This is particularly common with upsert operations but can happen with updates too, especially in transactions.
As mentioned in this GitHub issue, race conditions can cause unique constraint failures when multiple operations happen concurrently.
2. Error handling in transactions: There's a known issue where errors in transactions aren't properly wrapped as PrismaClientKnownRequestError objects, which might explain the unusual error format you're seeing. This was documented in issue #7786 and issue #7326.
3. Underlying database operations: Sometimes Prisma's query engine performs operations differently than expected. For example, it might be attempting to create a new record instead of updating an existing one due to how it interprets your query conditions.
Since your code is running in a transaction (as indicated by txClient and the stack trace showing _transactionWithCallback), it's possible that:
- Another operation in the same transaction is causing a conflict
- The transaction is retrying operations in a way that causes conflicts
- There's an issue with how Prisma is handling the error within the transaction context
To troubleshoot this issue:
1. Enable Prisma query logging to see the actual SQL being executed:
2. Consider adding a retry mec
hanism with exponential backoff for this specific operation
3. Check if there are any other operations in the transaction that might be affecting the same record
4. Verify that your database schema matches what Prisma expects (run prisma db pull to check)
This appears to be an intermittent issue ("once in a while"), which further suggests a race condition or transaction-related problem rather than a fundamental issue with your code.Can you add
DEBUG=* env variable and check if you get any more logs?
Does this error happen randomly?it is random in production so turning on debug logs was not an option.
@Nurul
🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly.
In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
Okay!
Are you using latest prisma version? If not, can you upgrade to latest prisma version and check if you get the same error?