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. Instead of executing each command individually as it is sent to the server, in a transaction, commands are bundled together and executed in a separate context than other requests.

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.

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. While these two statements are executed sequentially within the transaction, they will be committed, and thus be executed on the underlying data set, simultaneously.

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. PostgreSQL automatically rolls back transactions if one of the statements within results in an error. It also rolls back the transaction if serialization error would occur if the chosen isolation level does not allow them.

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, in essence turning back the clock to the start of the transaction.

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

By default, the ROLLBACK command resets the transaction to where it was when the BEGIN or START TRANSACTION commands were first called. 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 command:

ROLLBACK TO save_1;

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 save_1;

/* Continue the transaction with the correct account number */
   UPDATE accounts
      SET balance = balance + 1500
    WHERE id = 4;

   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.

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 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 exactly as if you'd issued COMMIT; BEGIN:

    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.