Understanding and using transactions in PostgreSQL

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 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 by providing atomicity (actions in a transaction are either all committed or all rolled back) and isolation (outside of the transaction, nothing changes until the commit while inside, the statements have consequences). These together help the database maintain 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.

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), 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.

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:

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:

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):

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:

 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:

 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:

 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:

 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:

BEGIN;

UPDATE accounts
   SET balance = balance - 6000
 WHERE id = 1;

UPDATE accounts
   SET balance = balance + 6000
 WHERE id = 2;

COMMIT;

In psql, this prints:

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).

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:

   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:

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):

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:

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:

    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.

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;

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

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:

BEGIN ISOLATION LEVEL <isolation_level>;

statements

COMMIT;

The <isolation_level> 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:

    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.