Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

COMMIT and ROLLBACK to Manage Changes

Author: Sophia

what's covered
This tutorial explores using COMMIT and ROLLBACK in two parts:
  1. Introduction
  2. Example Transaction

1. Introduction

Up to this point, we have looked at transactions as single units that start with a BEGIN and end with the COMMIT statement, with multiple SQL commands in between that are executed at once. Remember as well that without the BEGIN command, each individual SQL statement is viewed as a transaction with an implicit BEGIN and COMMIT command executed. However, you can split this up to execute the commands one at a time and control the results as if they were in a transaction to keep the ACID properties.

2. Example Transaction

To start a transaction in the PostgreSQL command line, you can start with either:


BEGIN;

or


BEGIN TRANSACTION;

This will start the transaction until the next COMMIT or ROLLBACK command is encountered. However, if there is an error in any of the statements after the BEGIN statement, the changes will automatically be rolled back.

The COMMIT command is used to save changes from a transaction to the database. This COMMIT command will save all of the SQL statements to the database that followed the BEGIN command. The syntax for the COMMIT command looks like this:


COMMIT;

or


END TRANSACTION;

The ROLLBACK command is used to undo or revert SQL statements that have not already been saved to the database. The ROLLBACK command can only be used to undo SQL statements that follow the BEGIN command. The syntax looks like this:


ROLLBACK;

Let us take a look at an example of a series of statements:


SELECT * 
FROM customer 
ORDER BY customer_id;

ROLLBACK Command Example

Let us start our transaction:


BEGIN;

BEGIN Transaction Result

Let us go ahead to update the CUSTOMER table to set the customer_id equal to 1 to have the first name set to Bob:


UPDATE customer 
SET first_name = 'Bob';

Oops, we accidentally updated all of the names to Bob, as we forgot the WHERE clause.


SELECT * 
FROM customer;

Undo ROLLBACK Example

We can use the ROLLBACK statement to undo the changes:


ROLLBACK;

Undo ROLLBACK Example 2

So although the database did not throw any errors, we can revert the changes from the last BEGIN statement with the use of the ROLLBACK statement.


summary
The COMMIT statement will save results to the database ,while the ROLLBACK statement will undo results from the start of a transaction.

Source: Authored by Vincent Tran