Multistatement Transactions

By default, each SQL query runs in its own transaction. Consider Figures  and , which show two identical queries.  

        test=> INSERT INTO trans_test VALUES (1);
        INSERT 130057 1  

 

        test=> BEGIN WORK;
        BEGIN
        test=> INSERT INTO trans_test VALUES (1);
        INSERT 130058 1
        test=> COMMIT WORK;
        COMMIT  

Figure  is a typical INSERT query. Before POSTGRESQL starts the INSERT, it begins a transaction. It performs the INSERT, then commits the transaction. This step occurs automatically for any query with no explicit transaction. In Figure , the INSERT uses an explicit transaction. BEGIN WORK starts the transaction, and COMMIT WORK commits the transaction. The only difference between the two queries is that an implied BEGIN WORK...COMMIT WORK surrounds the first INSERT.

Even more valuable is the ability to bind multiple queries into a single transaction. In such a case, either all queries execute to completion or none has any effect. As an example, Figure  shows two INSERTs in a transaction.  

        test=> BEGIN WORK;
        BEGIN
        test=> INSERT INTO trans_test VALUES (1);
        INSERT 130059 1
        test=> INSERT INTO trans_test VALUES (2);
        INSERT 130060 1
        test=> COMMIT WORK;
        COMMIT  

PostgreSQL guarantees that either both INSERTs succeed or neither.

As a more complicated example, suppose you have a table of bank account balances, and you wish to transfer $100 from one account to another account. This operation is performed using two queries: an UPDATE to subtract $100 from one account, and an UPDATE to add $100 to another account. The UPDATEs should either both complete or have no effect. If the first UPDATE completes but not the second, the $100 would disappear from the bank records. It would have been subtracted from one account, but never added to the other account. Such errors are very hard to find. Multistatement transactions prevent them from happening. Figure  shows the two queries bound into a single transaction.  

        test=> BEGIN WORK;
        BEGIN
        test=> UPDATE bankacct SET balance = balance - 100 WHERE acctno = '82021';
        UPDATE 1
        test=> UPDATE bankacct SET balance = balance + 100 WHERE acctno = '96814';
        UPDATE 1
        test=> COMMIT WORK;
        COMMIT  

The transaction forces POSTGRESQL to perform the queries as a single operation.

  When you begin a transaction with BEGIN WORK, you do not have to commit it using COMMIT WORK. Instead, you can close the transaction with ROLLBACK WORK and the transaction will be discarded. The database is left as though the transaction had never been executed. In Figure , the current transaction is rolled back, causing the DELETE to have no effect.  

        test=> INSERT INTO rollback_test VALUES (1);
        INSERT 19369 1
        test=> BEGIN WORK;
        BEGIN
        test=> DELETE FROM rollback_test;
        DELETE 1
        test=> ROLLBACK WORK;
        ROLLBACK
        test=> SELECT * FROM rollback_test;
         x 
        ---
         1
        (1 row)  

Likewise, if any query inside a multistatement transaction cannot be executed due to an error, the entire transaction is automatically rolled back.