TRUNCATE

Structured Query Language COMMIT and ROLLBACK

Create Table

DBMS offers a special service. We canundo 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-calledtransaction.

The syntax of COMMIT and ROLLBACK is very simple.

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

The keyword 'WORK' is optional.

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's necessary to work without AUTOCOMMIT.

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

-- Store a new person with id 99.INSERTINTOperson(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*FROMpersonWHEREid=99;-- Try COMMIT commandCOMMIT;-- Is she still in the database? (One hit expected.)SELECT*FROMpersonWHEREid=99;

Now we remove the person from the database.

-- Remove the new personDELETEFROMpersonWHEREid=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*FROMpersonWHEREid=99;-- Try COMMIT commandCOMMIT;-- Is the person still in the database? (No hit expected.)SELECT*FROMpersonWHEREid=99;

So far, so boring.

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.

DELETEFROMpersonWHEREid=3;-- Lisa Hamilton-- no hit expectedSELECT*FROMpersonWHEREid=3;-- ROLLBACK restores the deletionROLLBACK;-- ONE hit expected !!! Else: check AUTOCOMMITSELECT*FROMpersonWHEREid=3;

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

-- same as aboveDELETEFROMpersonWHEREid=3;-- destroy all e-mail addressesUPDATEcontactSETcontact_value='unknown'WHEREcontact_type='email';-- verify modificationsSELECT*FROMperson;SELECT*FROMcontact;-- A single ROLLBACK command restores the deletion in one table and the modifications in another tableROLLBACK;-- verify ROLLBACKSELECT*FROMperson;SELECT*FROMcontact;

Supose thehobby table contains 9 rows and theperson table 10 rows. We execute the following operations:
add 3 hobbies
add 4 persons
commit
add 5 hobbies
add 6 persons
rollback

12

14

TRUNCATE

Structured Query Language COMMIT and ROLLBACK

Create Table

• Book:Structured Query Language