How Prisma Supports Database Transactions

Update (July 1st, 2022): Since this article has been published, we have released interactive transactions which enables developers to use flexible, long-running transactions in Prisma Client.

---

Contents

• What are database transactions?
• Common problems with database transactions
• How Prisma supports database transactions today
• Transaction patterns and better alternatives
• Share your thoughts, feedback and use cases

---

What are database transactions?

Transactions prevent reading partially updated data

Most databases support a mechanism called transactions. Transactions are a "magic trick" that allow developers to pretend like there is only one user interacting with the database system at a given time. This allows the developers to ignore a full class of errors that could otherwise occur with concurrent database access.

For example, if a query is reading multiple rows in order to produce a result, it is possible for other queries to update these rows while the first query is in the middle of reading. Transactions make sure that the first query will never encounter partially updated data.

"Transactions are an abstraction layer that allows an application to pretend that certain concurrency problems and certain kinds of hardware and software faults don’t exist. A large class of errors is reduced down to a simple transaction abort, and the application just needs to try again." Designing Data-Intensive Applications, Martin Kleppmann

A transaction either entirely succeeds or fails

In general, a transaction allows developers to group a set of read- and/or write-operations into a single operation which is guaranteed to succeed ("the transaction is committed") or fail ("the transaction is aborted and rolled back") as a whole.

Whenever transactions are being discussed, you'll very likely come across the ACID acronym. ACID describes a set of safety guarantees provided by the database:

• Atomic: Ensures that either all or none operations of the transactions succeed. The transaction is either committed successfully or aborted and rolled back.
• Consistent: Ensures that the states of the database before and after the transaction are valid (i.e. any existing invariants about the data are maintained).
• Isolated: Ensures that concurrently running transactions have the same effect as if they were running in serial.
• Durability: Ensures that after the transaction succeeded, any writes are being stored persistently.

While there's a lot of ambiguity and nuance to each of these properties (e.g. consistency could actually be considered an application-level responsibility rather than a database property and isolation is typically guaranteed in terms of stronger and weaker isolation-levels), overall they serve as a good high-level guideline for expectations developers have when thinking about database access.

Long- and short-running database transactions

A simple query might read data from one row and update another row. If issued as a single query from the application this is a short-running transaction.

Sometimes it is convenient or necessary for an application to first read some data, then perform some manipulation on that data in application code and then issue a second query to write data to the database. This multi-step interaction with the database is reasonable and often required for various use cases.

It is important to think about what should happen if another user updated the initial value after it was read, but before the manipulated data is written back to the database. Maybe it is acceptable, maybe the multi-step interaction should be aborted and restarted, or maybe other parts of the system guarantees that this can not happen.

As most relational databases have a stateful connection mechanism, it is possible to have a transaction span multiple queries. As this interaction spans multiple network requests we call it a long-running transaction. It is tempting to lean on long-running transactions as a way to handle these multi-step interactions with the database.

The rest of this article explores why Prisma does not support long-running transactions, and why we believe you will be better off using other strategies to deal with the sort of situations described before.

Common problems with database transactions

Architectural constraints

Long-running transactions require holding a stateful connection open between two components for an extended period of time. This is not how modern scalable systems are built, and imposes constraints on performance and scalability of state-of-the-art system design.

This is exemplified in the challenges developers have who wish to build a high-scale application on AWS Lambda that connects to a relational database such as PostgreSQL or MySQL. These developers find that they must introduce another component, a database proxy, to break apart the stateful connection between application and database, losing the ability to run long-running transactions or introducing complex performance tuning as is the case with AWS RDS Proxy described in the Avoiding Pinning section.

Prisma is designed for a future of stateless, highly scalable services connecting to stateful data stores. To optimise for that future, we want to be careful to not be architecturally constrained by design decisions of the past.

Misleading guarantees

Chapter seven of the Designing Data-Intensive Applications book very well describes the ambiguity of the individual ACID properties, and how it contributes to misconceptions about the guarantees a transaction can actually provide.

"However, in practice, one database’s implementation of ACID does not equal another’s implementation. For example, as we shall see, there is a lot of ambiguity around the meaning of isolation. The high-level idea is sound, but the devil is in the details. Today, when a system claims to be “ACID compliant,” it’s unclear what guarantees you can actually expect. ACID has unfortunately become mostly a marketing term." Designing Data-Intensive Applications, Martin Kleppmann

These misconceptions can easily lead to performance pitfalls. Furthermore, database transactions require a stateful application environment which makes it hard to use them in the context of scalable, and serverless applications.

Later in this article, we will explore situations an alternative approach can provide better guarantees than using long-running transactions.

---

How Prisma supports database transactions today

Prisma is built with the support of modern deployment environments in mind. We chose an architecture separating the core engines from the JavaScript/TypeScript client, enabling us to consider more complex deployment configurations in the future.

This is why Prisma currently doesn't support the "traditional" database transaction mechanism, where an arbitrary set of queries is grouped in a transaction and either succeeds or fails as a whole. Instead, we are trying to identify various patterns and use cases for database transactions which we can solve in a better, more efficient manner than through long-running database transactions.

The following nested writes and transaction API are examples for certain use cases where developers would traditionally resort to long-running database transactions but where Prisma offers a more targeted and tailored API to accomplish a certain goal.

Providing these dedicated APIs is part of Prisma's philosophy of setting healthy constraints that ensure developers don't accidentally shoot themselves in the foot when using low-level SQL.

Grouping write operations of related records in nested writes

One of the most common use cases for database transactions is when you need to update multiple rows that are related via foreign keys. For example, you might want to create a new "order" along with a related "invoice" in the database. Prisma lets you achieve this use case via nested writes. Here is an example for this kind of operation:

When sending this query with Prisma Client, a new order record will be created, along with a new invoice record (which points back to the order via a foreign key).

While there's no need to specify this operation as a transaction on a Prisma Client level, under the hood Prisma will make sure this query is executed as a database transaction and can therefore guarantee that either both the order and the invoice records or neither of the two have been created.

Preview: Group unrelated write operations in a single transaction

Nested writes help you create, update and delete records that are related via foreign keys. However, they don't provide much help when you want to group write operations for records that are not related with each other.

For that use case, Prisma provides a dedicated transaction API which lets you group multiple write operations and ensure these are getting executed in order and are guaranteed to either succeed or fail as a whole.

Here's an example for using these kind of transactions:

Transaction patterns and better alternatives

We believe that there are better ways than long-running transactions to solve a vast majority of use cases. that for the vast majority of use cases, there are better ways to achieve a goal than a long-running transaction. Similar to nested writes and the $transaction API, we'll now introduce a number of tools that can be used as alternatives to traditional transactions.

Atomic operators

Sometimes a multi-step interaction with the database can be expressed more efficiently as an atomic operation.

For example, if you want to read a value, increment by one and then write it back, it would be better to instead use the atomic operator increment to perform both steps in a single transactional query. Atomic number operators are available as a preview feature since the v2.6.0 release.

Application-level optimistic concurrency control (OCC)

If a value being written to the database was calculated from a value previously read from the database, you can make the write conditional on the previously read data to not have changed. Prisma does not support OCC yet, but please join the discussion on GitHub to share your thoughts and feedback for our ideas of implementing it in the Prisma Client API!

Enforcing guarantees on application- rather than database-level

As an alternative to traditional transactions on the database-level, a common approach for achieving guarantees and enforcing constraints in your application data is the implementation on the application-level.

Banks are often used as an example of applications that require strong transactional guarantees and therefore are perceived as heavy users of traditional database transactions. This is a misconception. For banks, reconciling transactions is their entire business, so they handle this in their application domain rather than "outsourcing" it to the database.

As a concrete example, many bank customers are able to withdraw more money from their accounts than their overdraft allows. This is possible because the ATM does not hold an open transaction on a central database while dispensing cash. If you were to clone your credit card and enlist your friends to withdraw $100 from 10000 ATMs across the country, you would end up with a lot of cash, a huge overdraft and an angry call from your bank.

Serialising operations

An often overlooked but sometimes very effective strategy is to intentionally reduce concurrency to 1. This can be achieved by scheduling all operations on a queue to be processed by a single worker. By eliminating the MVCC overhead in the database, it is possible to scale this single worker approach to tens of thousands of transactions per second, and not having to worry about concurrency can greatly simplify the application logic.

---

Share your thoughts, feedback and use cases

While we believe that the vast majority of use cases for database transactions can be resolved with better, safer and more efficient alternatives, we'd love to hear your feedback on this approach! Also, if you feel like you have use cases in your application that are not covered by any of the suggested alternatives, please make sure to open a GitHub issue so that we can address this use case as well.

Update (July 1st, 2022): Since this article has been published, we have released interactive transactions which enables developers to use flexible, long-running transactions in Prisma Client.