Using BEGIN, COMMIT, and ROLLBACK

PostgreSQL executes every SQL statement inside a transaction.

For example, the following statement runs inside an implicit transaction:

If the statement succeeds, PostgreSQL commits the change. If the statement fails, PostgreSQL doesn't keep any of its changes.

We don't have to explicitly start or end the transaction. PostgreSQL handles that for us.

When multiple SQL statements need to be treated as a single unit of work, we can use an explicit transaction.

For example:

Here, we explicitly tell PostgreSQL where the transaction starts and when its changes should be saved.

We control an explicit transaction using BEGIN, COMMIT, and ROLLBACK.

BEGIN starts an explicit transaction:

Any statements we execute after BEGIN become part of that transaction until we end it with COMMIT or ROLLBACK.

COMMIT ends the transaction and saves all the changes made during it:

ROLLBACK ends the transaction and discards all the changes made during it:

Remember the two database operations we ran in the previous chapter?

1. Create an order in the orders table.

2. Add the purchased items to the order_items table.

We previously ran those statements separately. Let's now run them inside an explicit transaction and see what happens when both statements succeed and when one of them fails.

First, let's see what happens when both statements succeed.

Start a transaction:

You should see BEGIN, which means PostgreSQL has started the transaction.

Now create an order for customer 2:

You should see INSERT 0 1. The final 1 means PostgreSQL successfully inserted one row into the orders table.

The statement succeeded, but the transaction is still open. We haven't committed its changes yet.

Now add a purchased item to the order_items table:

You should again see INSERT 0 1.

Both statements have succeeded, so we can now save the changes by committing the transaction:

You should see COMMIT. The transaction is now complete and its changes have been saved.

Let's verify that the order and its order item are both in the database:

You should see the order item associated with txn_demo_002.

Our transaction looked like this:

Because both operations succeeded, we used COMMIT to save all the changes made during the transaction.

Now let's see what happens when one of the statements fails.

Start another transaction:

You should see BEGIN.

Now create an order for customer 3:

You should see INSERT 0 1.

The order has been inserted, but the transaction is still open. We haven't committed the change.

Now we'll deliberately make the next statement fail:

PostgreSQL rejects the statement because unit_price is a numeric column and 'not-a-price' isn't a valid numeric value.

When a statement fails inside an explicit transaction, PostgreSQL marks the transaction as failed. We can't continue using the transaction normally until we roll it back.

The order and its order item were supposed to be created together, but the order item couldn't be created. We therefore don't want to keep the order created by the first statement.

Run:

You should see ROLLBACK. PostgreSQL ends the transaction and discards all the changes made during it.

Now check whether the order still exists:

The query should return no rows.

The INSERT into orders succeeded earlier, but it happened inside a transaction that we never committed. When we ran ROLLBACK, PostgreSQL discarded that change.

Our transaction looked like this:

The two SQL statements now share one outcome. If both succeed, we can use COMMIT to save the changes. If one fails, we can use ROLLBACK to discard the changes made during the transaction.