# Understanding and using transactions in PostgreSQL > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Transactions help provide the atomicity, consistency, isolation, and durability promises of ACID-compliant databases. In this guide, we'll look at what transactions are, the various levels of isolation possible, and how to use them in PostgreSQL. Canonical URL: https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/using-transactions Authors: Justin Ellingwood Content revision: 9607eba7863fd2457b443a020cc46fc14e4dd6ad8d0c5b91f0175c4e26f009dc Source revision: 966174084131f9cfdc77eefcb4db38af72c1a29d Last updated: 2026-09-29 ## Introduction Transactions are a mechanism that encapsulates multiple statements into a single operation for the database to process. Instead of feeding in individual statements, the database is able to interpret and act on the group of commands as a cohesive unit. This helps ensure the consistency of the dataset over the course of many closely related statements. In this guide, we'll start by discussing what transactions are and why they are beneficial. Afterwards, we'll take a look at how PostgreSQL implements transactions and the various options you have when using them. ## What are transactions? [Transactions](https://www.prisma.io/dataguide/intro/database-glossary#transaction) are a way to group together and isolate multiple statements for processing as a single operation. Each statement in a transaction still runs as soon as it is sent to the server, but its changes stay inside the transaction: they only become permanent, and visible to other sessions, when the whole transaction is committed. Isolation is an important part of transactions. Within a transaction, the executed statements can only affect the environment within the transaction itself. From inside the transaction, statements can modify data and the results are immediately visible. From the outside, no changes are made until the transaction is committed, at which time all of the actions within the transaction become visible at once. These features help databases achieve [ACID compliance](https://www.prisma.io/dataguide/intro/database-glossary#acid) by providing [atomicity](https://www.prisma.io/dataguide/intro/database-glossary#atomicity) (actions in a transaction are either all committed or all rolled back) and [isolation](https://www.prisma.io/dataguide/intro/database-glossary#isolation) (outside of the transaction, nothing changes until the commit while inside, the statements have consequences). These together help the database maintain [consistency](https://www.prisma.io/dataguide/intro/database-glossary#consistency) (by guaranteeing that partial data transformations cannot occur). Furthermore, changes in transactions are not returned as successful until they are committed to non-volatile storage, which provides [durability](https://www.prisma.io/dataguide/intro/database-glossary#durability). To achieve these goals, transactions employ a number of different strategies and different database systems use different methods. PostgreSQL uses a system called [Multiversion Concurrency Control (MVCC)](https://www.prisma.io/dataguide/intro/database-glossary#multiversion-concurrency-control), which allows the database to perform these actions without unnecessary locking using data snapshots. All together, these systems comprise one of the fundamental building blocks of modern relational databases, allowing them to safely process complex data in a crash-resistant manner. ## Types of consistency failures One reason people use transactions is to gain certain guarantees about the consistency of their data and the environment in which it is processed. Consistency can be broken in many different ways, which affects how databases attempt to prevent them. There are four primary ways that inconsistency can arise depending on the transaction implementation. Your tolerance for scenarios where these scenarios may arise will affect how you use transactions in your applications. ### Dirty reads [Dirty reads](https://www.prisma.io/dataguide/intro/database-glossary#dirty-read) occur when the statements within a transaction are able to read data written by other in-progress transactions. This means that even though the statements of a transaction have _not_ been committed yet, they can be read and thus influence other transactions. This is often considered a severe breach of consistency, as transactions are not properly isolated form one another. Statements that may never be committed to the database can affect the execution of other transactions, modifying their behavior. Transactions that allow dirty reads cannot make any reasonable claims about the consistency of the resulting data. ### Non-repeatable reads [Nonrepeatable reads](https://www.prisma.io/dataguide/intro/database-glossary#nonrepeatable-read) occur when a commit outside of the transaction alters the data seen within the transaction. You can recognize this type of problem if, within a transaction, the same data is read twice but different values are retrieved in each instance. As with dirty reads, transactions that allow non-repeatable reads don't offer full isolation between transactions. The difference is that with non-repeatable reads, the statements affecting the transaction have actually been committed outside of the transaction. ### Phantom read A [phantom read](https://www.prisma.io/dataguide/intro/database-glossary#phantom-read) is a specific type of non-repeatable read that occurs when the rows returned by a query are different the second time it is executed within a transaction. For instance, if a query within the transaction returns four rows the first time it is executed, but five rows the second time, this is a phantom read. Phantom reads are caused by commits outside of the transaction altering the number of rows that satisfy the query. ### Serialization anomalies [Serialization anomalies](https://www.prisma.io/dataguide/intro/database-glossary#serialization-anomaly) occur when the results of multiple transactions committed concurrently will result in different outcomes than if they were committed one after another. This can occur any time that a transaction allows two commits to occur that each modify the same table or data without resolving conflicts. Serialization anomalies are a special type of problem that early types of transactions had no understanding of. This is because early transactions were implemented with locking, where one could not continue if another transaction was reading from or altering the same piece of data. ## Transaction isolation levels Transactions are not a "one size fits all" solution. Different scenarios require different trade-offs between performance and protection. Fortunately, PostgreSQL allows you to specify the type of transaction isolation you need. The isolation levels offered by most database systems include the following: ### Read uncommitted [**Read uncommitted**](https://www.prisma.io/dataguide/intro/database-glossary#read-uncommitted-isolation-level) is the isolation level that offers the fewest guarantees about maintaining data consistency and isolation. While transactions using `read uncommitted` have certain features frequently associated with transactions, like the ability to commit multiple statements at once or to roll back statements if a mistake occurs, they _do_ allow numerous situations where consistency can be broken. Transactions configured with the `read uncommitted` isolation level allow: - dirty reads - non-repeatable reads - phantom reads - serialization anomalies This level of isolation is actually not implemented in PostgreSQL. Although PostgreSQL recognizes the isolation level name, internally, it is not actually supported and "read committed" (described below) will be used instead. ### Read committed [**Read committed**](https://www.prisma.io/dataguide/intro/database-glossary#read-committed-isolation-level) is an isolation level that specifically protects against dirty reads. When transactions use the `read committed` level of consistency, uncommitted data can never affect the internal context of a transaction. This provides a basic level of consistency by ensuring that uncommitted data never influences a transaction. Although `read committed` offers greater protection than `read uncommitted`, it does not protect against all types of inconsistency. These problems can still arise: - non-repeatable reads - phantom reads - serialization anomalies PostgreSQL will use the `read committed` level by default if no other isolation level is specified. ### Repeatable read The [**repeatable read**](https://www.prisma.io/dataguide/intro/database-glossary#repeatable-read-isolation-level) isolation level builds off of the guarantee provided by `read committed`. It avoids dirty reads as before, but prevents non-repeatable reads as well. This means that no changes committed outside of the transaction will ever impact the data read within the transaction. A query executed at the start of a transaction will never have a different result at the end of the transaction unless directly caused by statements within the transaction. While the standard definition of the `repeatable read` isolation level requires only that dirty and non-repeatable reads are prevented, PostgreSQL also prevents phantom reads at this level. This means that commits outside of the transaction cannot alter the number of rows that satisfy a query. Since the state of the data seen within the transaction can deviate from the up-to-date data in the database, a transaction fails with a serialization error if it tries to modify or lock a row that another transaction changed and committed after it started (`ERROR: could not serialize access due to concurrent update`). Because of this, one drawback of this isolation level is that you may have to retry transactions that fail this way. PostgreSQL's `repeatable read` isolation level blocks most types of consistency issues but serialization anomalies can still occur. ### Serializable The [**serializable**](https://www.prisma.io/dataguide/intro/database-glossary#serializable-isolation-level) isolation level offers the highest level of isolation and consistency. It prevents all of the scenarios that the `repeatable read` level does while also removing the possibility of serialization anomalies. Serializable isolation guarantees that concurrent transactions are committed as if they were executed one after another. If a scenario occurs where a serialization anomaly could be introduced, one of the transactions will have a serialization failure instead of introducing inconsistency to the data set. ## Defining a transaction Now that we've covered the different isolation levels that PostgreSQL can use in transactions, let's demonstrate how to define transactions. In PostgreSQL, every statement _outside_ of an explicitly marked transaction is actually executed in its own, single-statement transaction. To explicitly start a transaction block, you can use either the `BEGIN` or `START TRANSACTION` commands (they are synonymous). To commit a transaction, issue the `COMMIT` command. The basic syntax of a transaction therefore looks like this: ```sql BEGIN; statements COMMIT; ``` As a more concrete example, imagine that we are attempting to transfer $1000 from one account to another. We want to ensure that the money will always be in one of the two accounts but never in both of them. The examples in this guide use a simple `accounts` table. If you want to follow along, you can create it like this (the `CHECK` constraint prevents balances from going below zero): ```sql CREATE TABLE accounts ( id integer PRIMARY KEY, balance numeric(12, 2) NOT NULL CHECK (balance >= 0) ); INSERT INTO accounts (id, balance) VALUES (1, 5000), (2, 5000), (3, 5000), (4, 5000); ``` We can wrap the two statements that together encapsulate this transfer in a transaction that looks like this: ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT; ``` Here, the $1000 will not be taken out of the account with `id = 1` without also putting $1000 into the account with `id = 2`. This is what atomicity means: the two changes succeed or fail as a unit. It doesn't mean that the statements run at the same time. Each `UPDATE` runs as soon as you send it, and the session running the transaction sees its own changes right away. For instance, after the first `UPDATE`, running `SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id;` inside the transaction returns: ```text id | balance ----+--------- 1 | 4000.00 2 | 5000.00 (2 rows) ``` If a second session runs the same query at that moment, it still sees the last committed state of both accounts: ```text id | balance ----+--------- 1 | 5000.00 2 | 5000.00 (2 rows) ``` Once the first session runs the second `UPDATE` and `COMMIT`, the second session sees both changes at once: ```text id | balance ----+--------- 1 | 4000.00 2 | 6000.00 (2 rows) ``` ## Rolling back transactions Within a transaction, either all or none of the statements will be committed to the database. Abandoning the statements and modifications made within a transaction instead of applying them to the database is known as "rolling back" the transaction. Transactions can be rolled back either automatically or manually. If a session disconnects before committing its transaction, PostgreSQL rolls the transaction back automatically. Errors work differently. If a statement inside a transaction block fails, including with a serialization failure under the stricter isolation levels, PostgreSQL doesn't quietly undo the transaction and carry on. Instead, it marks the transaction as _aborted_ and rejects every further command until you end the transaction block. For example, this transfer tries to take more money out of account `1` than it holds, which violates the table's `CHECK` constraint: ```sql BEGIN; UPDATE accounts SET balance = balance - 6000 WHERE id = 1; UPDATE accounts SET balance = balance + 6000 WHERE id = 2; COMMIT; ``` In `psql`, this prints: ```text BEGIN ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check" DETAIL: Failing row contains (1, -1000.00). ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK ``` The second `UPDATE` is rejected even though it would have succeeded on its own, and `psql`'s default prompt shows a `!` while the transaction is in this state (instead of the `*` it shows during a normal transaction). An aborted transaction can't be committed: `COMMIT` rolls it back instead, which is why the response to `COMMIT` above is `ROLLBACK`. None of the changes are saved. To end an aborted transaction, issue `ROLLBACK` (or, to keep the work done before the error, roll back to a save point, as described [below](https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/using-transactions#recovering-from-errors-with-save-points)). To manually roll back statements that have been given during the current transaction, you can use the `ROLLBACK` command. This will cancel all of the statements within the transaction and end it, in essence turning back the clock to before the transaction started. For instance, supposing we're using the same bank accounts example we were using before, if we find out after issuing the `UPDATE` statements that we accidentally transferred the wrong amount or used the wrong accounts, we could rollback the changes instead of committing them: ```sql BEGIN; UPDATE accounts SET balance = balance - 1500 WHERE id = 1; UPDATE accounts SET balance = balance + 1500 WHERE id = 3; -- Wrong account number here! Must rollback /* Gets us back to where we were before the transaction started */ ROLLBACK; ``` Once we `ROLLBACK`, the $1500 will still be in the account with `id = 1`. ## Using save points when rolling back The `ROLLBACK` command discards everything done since the `BEGIN` or `START TRANSACTION` command and ends the transaction. But what if we only want to revert some of the statements within the transaction? While you cannot specify arbitrary places to roll back to when issuing `ROLLBACK` command, you _can_ roll back to any "save points" that you've set throughout the transaction. You can mark places in your transaction ahead of time with the `SAVEPOINT` command and then reference those specific locations when you need to roll back. These save points allow you to create an intermediate roll back point. You can then optionally revert any statements made between where you are currently and the save point and then continue working on your transaction. To specify a save point, issue the `SAVEPOINT` command followed by a name for the save point: ```sql SAVEPOINT save_1; ``` To roll back to that save point, use the `ROLLBACK TO SAVEPOINT` command (the `SAVEPOINT` keyword is optional, so `ROLLBACK TO save_1` works too): ```sql ROLLBACK TO SAVEPOINT save_1; ``` Rolling back to a save point undoes the changes made after it but keeps the save point, so you can roll back to it again if you need to. When you no longer need a save point, you can remove it with `RELEASE SAVEPOINT`: ```sql RELEASE SAVEPOINT save_1; ``` Releasing a save point keeps the changes made after it. Like everything else in the transaction, they're only made permanent when the transaction commits. Let's continue the account-focused example we've been using: ```sql BEGIN; UPDATE accounts SET balance = balance - 1500 WHERE id = 1; /* Set a save point that we can return to */ SAVEPOINT save_1; UPDATE accounts SET balance = balance + 1500 WHERE id = 3; -- Wrong account number here! We can rollback to the save point though! /* Gets us back to the state of the transaction at `save_1` */ ROLLBACK TO SAVEPOINT save_1; /* Continue the transaction with the correct account number */ UPDATE accounts SET balance = balance + 1500 WHERE id = 4; /* We no longer need the save point */ RELEASE SAVEPOINT save_1; COMMIT; ``` Here, we're able to recover from a mistake we made without losing all of the work we've done in the transaction so far. After rolling back, we continue with the transaction as planned using the correct statements. ### Recovering from errors with save points Save points are also how you recover from an error without losing the whole transaction. As described [above](https://www.prisma.io/dataguide/postgresql/inserting-and-modifying-data/using-transactions#rolling-back-transactions), a failed statement puts the transaction into an aborted state. Rolling back to a save point that was set before the failure discards the failed statement and makes the transaction usable again: ```sql BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; SAVEPOINT second_transfer; UPDATE accounts SET balance = balance - 6000 WHERE id = 3; ROLLBACK TO SAVEPOINT second_transfer; COMMIT; ``` In `psql`, this prints: ```text BEGIN UPDATE 1 UPDATE 1 SAVEPOINT ERROR: new row for relation "accounts" violates check constraint "accounts_balance_check" DETAIL: Failing row contains (3, -1000.00). ROLLBACK COMMIT ``` The `ROLLBACK` line is the response to `ROLLBACK TO SAVEPOINT`, and this time `COMMIT` really commits: the transfer from account `1` to account `2` is saved, while account `3` is unchanged. If an application wraps a statement that might fail in a save point like this, it can decide what to do about the error without starting the whole transaction over. ## Setting the isolation level of transactions To set the level of isolation you'd like for a transaction, you can add an `ISOLATION LEVEL` clause to your `START TRANSACTION` or `BEGIN` command. The basic syntax looks like this: ```sql BEGIN ISOLATION LEVEL ; statements COMMIT; ``` The `` can be any of these (described in detail earlier): - `READ UNCOMMITTED` (will result in `READ COMMITTED` since this level isn't implemented in PostgreSQL) - `READ COMMITTED` - `REPEATABLE READ` - `SERIALIZABLE` The `SET TRANSACTION` command can also be used to set the isolation level after a transaction is started. However, you can only use `SET TRANSACTION` before any queries or data modifying commands are executed, so it doesn't allow for increased flexibility. ## Chaining transactions If you have multiple transactions that should be executed sequentially, you can optionally chain them together using the `COMMIT AND CHAIN` command. The `COMMIT AND CHAIN` command completes the current transaction by committing the statements within. After the commit has been processed, it immediately opens a new transaction. This allows you to group another set of statements together in a transaction. The statement works like `COMMIT` followed by `BEGIN`, except that the new transaction keeps the characteristics of the one that just ended, such as its isolation level: ```sql BEGIN; UPDATE accounts SET balance = balance - 1500 WHERE id = 1; UPDATE accounts SET balance = balance + 1500 WHERE id = 2; /* Commit the data and start a new transaction that will take into account the committed from the last transaction */ COMMIT AND CHAIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 2; UPDATE accounts SET balance = balance + 1000 WHERE id = 3; COMMIT; ``` Chaining transactions doesn't offer much in terms of new functionality, but it can be helpful for committing data at natural boundaries while continuing to focus on the same type of operations. ## Conclusion Transactions are not a silver bullet. There are a lot of trade offs that come with various isolation levels and understanding what types of consistency you need to protect can take thought and planning. This is especially true with long running transactions where the underlying data may change significantly and the possibility of conflict with other concurrent transactions increases. That being said, the transaction mechanic offers a lot of flexibility and power. It goes a long way towards ensuring ACID guarantees are maintained even while performing interrelated, concurrent operations. Knowing when and how to properly use transactions to perform complex, safe operations is invaluable.