Hi. Although I could not attend the live transmission, I watched the recent Prisma Chat - All about Transactions that took place last week. This left me perplexed and with a few questions. I am trying to decide the architecture of a proposed system. It needs to scale, as it will have an estimated user population of 700k, and a potential transaction rate of >100k per hour peak. The integrity of the data will be paramount, as the system will handle financial transactions amongst its mix. I want therefore to use the built in transaction integrity features of the underlying database as far as possible, and recognise that I may need to choose a database provider that supports my requirements, such as MySQL. I want to be able to Begin a Transaction and Commit a transaction, and I want to know that at the end of the transaction my data is safely stored in the database, and if the application falls over or the database server collapses, that I can rely upon the database recovery procedures to rebuild the database and restart with my committed data intact. At the same time, if something fails after my transaction has started, but before it commits the changes, then I want the database to ensure that the data changes made during the incomplete transaction are rolled back, as though the transaction had never started. So my question to the Prisma team is how is Prisma managing the transactions: Is it using the underlying Databases transaction capabilities, if available, or does it manage the transaction itself? Are the transaction capabilities dependent upon the underlying provider? Does the 'lowest common denominator' issue mean that Prisma must manage the transaction integrity. As a relational database programmer and administrator for many years, I trust in the DBMS to manage the transactions. I understand the implications for locking and the strategies for reducing contention. While I very much like syntax provided by Prisma to query and manipulate the data, I am concerned by any possibility that it will put a layer between the developers code and the database transaction support. Thanks in advance

@maxweld as far as I'm aware, the underlying transaction capabilities of the database are used I'm currently using Prisma with Postgres, and in places where I've used

prisma.$transaction

, I can see

BEGIN

and

COMMIT

statements in the transaction logs, surrounding the statements that I specified

Thanks @Ben Ezard that is encouraging. I will try out MySQL to see if I get some similar results.

Hey David, Thanks for sharing your use case. We indeed rely on the database to manage the transactions. A common reason for using transactions is writing multiple rows to the database atomically. The Prisma Client supports this with: 1. Nested Writes: write related data together at once inside a transaction. 2. $transaction API: write unrelated data together at once inside a transaction. 3. Batch APIs: create, update or delete many records at once. Each of these techniques use database transactions under the hood. What we don't allow is opening transactions for unbound periods of time. A situation like the following is _not permitted_: 1. Begin the transaction 2. Read data from database 3. Fetch data from third party API (could be slow or fail) 4. Merge the data from the database and third party API 5. Write the data to the database 6. Commit the transaction These long-running transactions (LRT) kill performance for larger applications. This Read-Modify-Write pattern is common in real-world applications, so we need strategies to accomplish the same result without falling back to LRT. The Prisma Client provides two techniques for solving this pattern, depending on the situation: 1. Idempotent operations: write your code so the same input has the same output 2. Optimistic Concurrency Control: versioning to check when the row has changed This response just scratches the surface. There's a lot more detail about each of these patterns in The Transaction Guide.

You can also find a bit more context about LRTs in this GitHub issue: https://github.com/prisma/prisma/issues/1844