TL;DR: If all calls to the prisma client are synch...
# prisma-clientj
Jacob Coker-Dukowitz
08/19/2022, 11:14 PMTL;DR: If all calls to the prisma client are synchronous, are queries sent to a single instance of the Prisma client guaranteed to be in the same connection/session when using postgres?
Background:
I’d like to set a transactional advisory lock where i need some processing between queries.
• I can’t use the Sequential Operations feature because i need processing between queries.
• I can’t use the Interactive transactions feature because this is for production.
• I’d prefer to use a transactional and not a general advisory lock on the db, because then we need to worry about cleaning up orphaned advisory locks in the unlikely but real scenario of a node dying without ‘unlocking’.
Why can’t I just use this? Are queries sent to a single instance of the Prisma client not guaranteed to be in the same connection? Running in my repl, it appears to be the same connection.
Copy code
prisma.$queryRaw`BEGIN`
prisma.$queryRaw`pg_advisory_xact_lock(5)`
// code, processing
prisma.$queryRaw`pg_advisory_xact_unlock(5)`
prisma.$queryRaw`COMMIT`✅ 1
Jacob Coker-Dukowitz
08/24/2022, 6:58 PMFrom the docs, I see that
• Prisma’s Query Engine creates a Connection and adds it to the Connection Pool upon the first query the Prisma Client instance receives.
• Prisma reserves a Connection for each subsequent query from the Connection Pool.
This suggests that if there is has been no asynchronous processes for the duration of the Prisma Client instance, then there is only one Connection, and it’s likely that Prisma will use the same Connection. But is this guaranteed?
n
Nurul
08/25/2022, 7:07 AMHi Jacob 👋
There is no guarantee that those will be in the same connection.
With low load the queries are on the same connection as only 1 is needed and it does not do anything else.
But as soon as there is traffic, that guarantee goes away - and transactions are the correct way to avoid that.
The only other way I could think of using same connection without using transactions is to allow only one connection by using connection_limit parameter and setting it to 1.
j
Jacob Coker-Dukowitz
08/25/2022, 6:59 PMThanks Nurul, thats very helpful.
Unfortunately I’d rather not use the connection_limit in this case since it would apply to all requests. However, we might be able to send this specific code to an async job on another pod.
Actually, this is worse than I thought. I have to set lock_timeout for our entire database in order to use advisory locks, since otherwise they will occur at the level of a connection. Ouch.
Whats is the timeline for GA of Interactive Transactions API on the roadmap?
Jacob Coker-Dukowitz
08/26/2022, 12:00 AMFor others reading this in the future, you can set the lock timeout and set an advisory lock by using the GA transactions api. Ex:
Copy code
ogPrisma.$transactions([ogPrisma.$executeRaw`SET SESSION lock_timeout = 10000`, `SELECT pg_advisory_lock(${applyChangesAdvisoryLockNumber(id)})`)👍 1
8 Views