# How Prisma Supports Database Transactions

URL: https://www.prisma.io/blog/how-prisma-supports-transactions-x45s1d5l0ww1
Published: 2020-09-08
Authors: Nikolas Burk, Søren Bramer Schmidt
Description: Learn how Prisma supports database transactions, the tradeoffs of different transaction patterns, and how Prisma's transaction capabilities have evolved.

> Blog posts describe Prisma as it was on the date shown and are not kept current. Before implementing from a post, check the documentation index at https://www.prisma.io/docs/llms.txt and the changelog at https://www.prisma.io/changelog.md.

> For an index of every published post, fetch https://www.prisma.io/blog/llms.txt.

Database transactions often are a major source of complexity in server-side applications. In this article, we'll examine how Prisma supports different types of database transactions *today* as well as our plans for expanding transaction support *in the future*.

> **Update (July 1st, 2022)**: Since this article has been published, we have released [interactive transactions](https://www.prisma.io/docs/orm/fundamentals/transactions#interactive-transactions-in-preview) which enables developers to use flexible, long-running transactions in Prisma Client.

***

## Contents [#contents]

* [What are database transactions?](#what-are-database-transactions)
* [Common problems with database transactions](#common-problems-with-database-transactions)
* [How Prisma supports database transactions today](#how-prisma-supports-database-transactions-today)
* [Transaction patterns and better alternatives](#transaction-patterns-and-better-alternatives)
* [Share your thoughts, feedback and use cases](#share-your-thoughts-feedback-and-use-cases)

***

## What are database transactions? [#what-are-database-transactions]

### Transactions prevent reading partially updated data [#transactions-prevent-reading-partially-updated-data]

Most databases support a mechanism called [*transactions*](https://en.wikipedia.org/wiki/Database_transaction). 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](https://dataintensive.net/), [Martin Kleppmann](https://twitter.com/martinkl)

### A transaction either entirely succeeds or fails [#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 [#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 [#common-problems-with-database-transactions]

### Architectural constraints [#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](https://aws.amazon.com/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](https://aws.amazon.com/rds/proxy/) described in the [Avoiding Pinning](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy.html#rds-proxy-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 [#misleading-guarantees]

Chapter seven of the [Designing Data-Intensive Applications](https://dataintensive.net/) 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](https://dataintensive.net/), [Martin Kleppmann](https://twitter.com/martinkl)

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 [#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](https://www.prisma.io/docs/orm/v7/more/releases#prisma-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](https://github.com/prisma/orm/issues/1844), 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](https://www.prisma.io/docs/orm/v6/overview/introduction/why-prisma#application-developers-should-care-about-data--not-sql) 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 [#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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#nested-writes). Here is an example for this kind of operation:

```ts
const order = await prisma.order.create({
  data: {
    price: price,
    quantity: quantity,
    invoice: {
      create: { total: price*quantity }
    }
  },
})
```

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 [#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:

```ts
const write1 = prisma.user.create()
const write2 = prisma.post.create()
const write3 = prisma.profile.create()
await prisma.$transaction([write1, write2, write3])
```

### Expand to view how you can enable the `$transaction` API

Note that this API is currently in *preview* and needs to be explicitly enabled by specifying the `transactionApi` feature flag in your Prisma Client `generator` block:

```prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["transactionApi"]
}
```

After this has been configured, you can run `prisma generate` and use `$transaction` as a top-level method on your `PrismaClient` instance then.

## Transaction patterns and better alternatives [#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 [#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](https://github.com/prisma/orm/releases/tag/2.6.0) release.

### Application-level optimistic concurrency control (OCC) [#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](https://github.com/prisma/prisma-client-js/issues/2), 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 [#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 [#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 [#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](https://github.com/prisma/orm/issues/new/choose) 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](https://www.prisma.io/docs/orm/fundamentals/transactions#interactive-transactions-in-preview) which enables developers to use flexible, long-running transactions in Prisma Client.

For a SQL walkthrough of related business writes on hosted Postgres, see [keeping customer and order updates consistent](https://www.prisma.io/postgres/customer-order-transactions), including commit, rollback and retry decisions.

For a small-business example, see [hosting a multi-user inventory app](https://www.prisma.io/compute/multi-user-inventory-app). It combines a conditional stock update with guidance on transaction boundaries and repeated requests.