Working with Transactions

The transaction means a logical unit of work which would make sense only on the completion of all the operations as an overall. These means either all the operations in the unit are completed fully or none of them are completed. In the Oracle language, operations can be any Data Manipulation Language DML statements. SQL provides two commands to simulate real-life transactions in the database. The commands are as follows:

•    COMMIT
•    ROLLBACK

COMMIT completes the transaction done through making modifications permanently to the database and initiates a new transaction. ROLLBACK reverts back the modify made and completes the transaction. The following instance describes this in a better way:

INSERT INTO <tablename> Values(……);
INSERT INTO <tablename> Values(……); UPDATE <tablename> SET …… COMMIT;
Displays
Commit complete.

Whole the changes are made permanently in the database. The Update statement and two Insert statements effect the changes and the commit completes the transaction.

Now, let consider the following:

DELETE FROM <tablename>;

When this statement is issued, the records are deleted from the table but if this is done accidentally, records can be retrieved instantly after the DELETE statement through issuing the statement:

ROLLBACK;

This statement reverts back the modifications made to the table and the records are not deleted.

Note: Whole DDL, DCL statements perform an Implicit Commit.

Temporary Markers

Now, assume which after the DELETE statement is one INSERT statement is issued as provided below:
DELETE FROM <tablename> WHERE ….;
INSERT INTO <tablename> VALUES (……);
ROLLBACK;

In the syntax, both the INSERT and the DELETE operations are un-done. In order to prevent this kind of conditions SAVEPOINTs are used. SAVEPOINTS are temporary markers which are used to separate a lengthy transaction into smaller ones. They are used with ROLLBACK.

Syntax:

SAVEPOINT <savepoint id>;
where the savepoint_id is the name of the savepoint. Multiple savepoints can be created within a transaction. Re-phrasing the past example,
DELETE FROM <tablename> WHERE <condition>;
SAVEPOINT s1;
INSERT INTO <tablename> VALUES (….);
ROLLBACK TO s1;
…..
In case only the deleted statements are rolled back as savepoint has been used after the Delete statements.

Note: the SAVEPOINTS can be used within transactions. After a transaction is finsihed, the similar savepoints cannot be used in the next transaction.
The DCL Statements are dealt in the chapter on database objects.