Online College Courses for Credit

+
COMMIT and ROLLBACK to Manage Changes

COMMIT and ROLLBACK to Manage Changes

Rating:
(0)
Author: Sophia Tutorial
Description:

In a scenario where SQL statements and transactions are not automatically committed, you will be able to use the COMMIT or ROLLBACK statements to save or undo changes to the tables.

(more)
See More
Tutorial

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 since the BEGIN command. The syntax for the COMMIT command looks like this:


COMMIT;

or


END TRANSACTION;

The ROLLBACK command is one that is used to undo or revert SQL Statements that have not been already saved to the database. The ROLLBACK command can only be used to undo SQL statements since 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 through 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