Structured Query Language/COMMIT and ROLLBACK + SAVEPOINT

COMMIT and ROLLBACK:

DBMS offers a special service. We can undo a single or even multiple consecutive write and delete operations. To do so we use the command ROLLBACK. When modifying data, the DBMS writes in a first step all new, changed or deleted data to a temporary space. During this stage the modified data is not part of the 'regular' database. If we are sure the modifications shall apply, we use the COMMIT command. If we want to revert our changes, we use the ROLLBACK command. All changes up to the finally COMMIT or ROLLBACK are considered to be part of a so called transaction.

The syntax of COMMIT and ROLLBACK is very simple.

COMMIT WORK;     -- commits all previous INSERT, UPDATE and DELETE commands, which
                 -- occurred since last COMMIT or ROLLBACK
ROLLBACK WORK;   -- reverts all previous INSERT, UPDATE and DELETE commands, which
                 -- occurred since last COMMIT or ROLLBACK

The keyword 'WORK' is optional.

AUTOCOMMIT

The feature AUTOCOMMIT automatically performs a COMMIT after every write operation (INSERT, UPDATE or DELETE). This feature is not part of the SQL standard, but is implemented and activated by default in some implementations. If we want to use the ROLLBACK command, we must deactivate the AUTOCOMMIT. (After an - automatic or explicit - COMMIT command a ROLLBACK command is syntactically okay, but it does nothing as everything is already committed.) Often we can deactivate the AUTOCOMMIT with a separate command like 'SET autocommit = 0;' or 'SET autocommit off;' or by clicking an icon on a GUI.

To test the following statements it is necessary to work without AUTOCOMMIT.

COMMIT

Let us insert a new person into the database and test the COMMIT.

-- Store a new person with id 99.
INSERT INTO person (id, firstname, lastname, date_of_birth,    place_of_birth, ssn,           weight)
VALUES             (99, 'Harriet', 'Flint',  DATE'1970-10-19', 'Dallas',       '078-05-1120', 65);

-- Is the new person really in the database? The process which executes the write operation will see its results,
-- even if they are actually not committed. (One hit expected.)
SELECT *
FROM   person
WHERE  id = 99;

-- Try COMMIT command
COMMIT;

-- Is she still in the database? (One hit expected.)
SELECT *
FROM   person
WHERE  id = 99;

Now we remove the person from the database.

-- Remove the new person
DELETE
FROM   person
WHERE  id = 99;

-- Is the person really gone? Again, the process which performs the write operation will see the changes, even 
-- if they are actually not committed. (No hit expected.)
SELECT *
FROM   person
WHERE  id = 99;

-- Try COMMIT command
COMMIT;

-- Is the person still in the database? (No hit expected.)
SELECT *
FROM   person
WHERE  id = 99;

ROLLBACK

The exciting command is the ROLLBACK. It restores changes of previous INSERT, UPDATE or DELETE commands.

We delete and restore Mrs. Hamilton from our example database.

DELETE
FROM   person
WHERE  id = 3; -- Lisa Hamilton

-- no hit expected
SELECT *
FROM   person
WHERE  id = 3;

-- ROLLBACK restores the deletion
ROLLBACK;

-- ONE hit expected !!! Else: check AUTOCOMMIT
SELECT *
FROM   person
WHERE  id = 3;

The ROLLBACK is not restricted to one single row. It may affect several rows, several commands, different kind of commands and even several tables.

-- same as above
DELETE
FROM   person
WHERE  id = 3; 
-- destroy all e-mail addresses
UPDATE contact
SET    contact_value = 'unknown'
WHERE  contact_type = 'email';

-- verify modifications
SELECT * FROM person;
SELECT * FROM contact;

-- A single ROLLBACK command restores the deletion in one table and the modifications in another table
ROLLBACK;

-- verify ROLLBACK
SELECT * FROM person;
SELECT * FROM contact;

SAVEPOINT

As transactions can cover a lot of statements, it is likely that runtime errors or logical errors arise. In some of such cases applications want to rollback only parts of the actual transaction and commit the rest or resume the processing a second time. To do so, it is possible to define internal transaction boundaries which reflects all processing from the start of the transaction up to this point in time. Such intermediate boundaries are called savepoints. COMMIT and ROLLBACK statements terminate the complete transaction including its savepoints.

-- Begin the transaction with an explicit command
START TRANSACTION;
--
INSERT ... ;
-- Define a savepoint
SAVEPOINT step_1;
--
UPDATE ... ;
-- Discard only the UPDATE. The INSERT remains.
ROLLBACK TO SAVEPOINT step_1;
-- try again (or do any other action)
UPDATE ... ;
-- confirm INSERT and the second UPDATE
COMMIT;

During the lifetime of a transaction a savepoint can be released if it's no longer needed. (At the end of the transaction it's implicitly released.)

-- ...
-- ...
RELEASE SAVEPOINT <savepoint_name>;
-- This has no effect to the results of previous INSERT, UPDATE or DELETE commands. It only eliminates the
-- possiblity to ROLLBACK TO SAVEPOINT <savepoint_name>.