PL/SQL Transactions

A PL/SQL transaction is a collection of operations or instructions which is executed as a whole atomic unit. A transaction can access and manipulate various data items. In a multi-user environment, every user is working with his own transaction independence, keeping the database in an inconsistent format.

There are few properties which every stable database should consist of. These are listed down:

1. Atomicity: This property ensures that either all the changes occur or nothing is changed at the end of the transaction.


2. Consistency: This property ensures that if the database was in a consistent format before the transaction then it will be in a consistent after the next Transaction as well.

3. Isolation: This property implies that the action taken or performed by one transaction is hidden from outside transaction or other transactions. Each of it is unaware of other active transaction until a particular transaction terminates.

4. Durability: This property ensures that if a transaction is made and a commit command is executed, then everything gets saved in the database even if the database closes down abnormally.

A successfully accomplished PL/SQL statement and a transaction which is issued a commit command are not the same. Unless a commit command is not followed by a transaction, it can be rolled back and all changes made by the statement(s) can be undone.

A PL/SQL transaction has a beginning and an ending. It usually begins when you hit the very first SQL statement may be to log in to the system. It also starts when you type any PL/SQL statement. The transaction probably ends if a rollback or a commit statement is entered into the database. However, a commit statement is not necessary in case you issue create table statement as it automatically performs a commit statement after completion.

Whenever you type Exit and terminate the SQL Prompt, a commit is automatically executed. Whenever a user disconnects the database or logs off, a commit is automatically executed. In case the SQL Prompt closes down abnormally, in order to save the state of the database, a rollback command is executed automatically.

Commit a Transaction

In case you need to save the current PL/SQL transaction into the database, it is mandatory to issue a commit command after completing the transaction. The other users of the same database would then be able  to see the newly saved transaction. A commit command is used to permanently save the transactions in a database.

Example

insert into Admin values(1,'Tushar Soni',30000);
Commit;

For every update, delete or insert query, a commit needs to be issued by the User to save the transaction. In order to get a transaction automatically committed by the SQL engine, you will have to execute an auto commit command.

This is done as follows:


To SET the AUTO COMMIT ON

Set Autocommit On;

To SET the AUTO COMMIT OFF

Set Autocommit Off;

Rollback a Transaction

In case you have made a transaction and you want to delete it or go to the previously saved state , a rollback command needs to be executed. However, this will only work when you haven’t issued the commit command otherwise giving rollback command wouldn’t be of any use. This command is basically used to undo any changes made to the database.

Example

Rollback;

Savepoint

Savepoint are used to mark the current position during processing a transaction. It helps to split a lengthy transaction into smaller sub-units by issuing some checkpoints at various locations within the program. In case you need to rollback a part of a transaction you can refer to the checkpoints and rollback accordingly. Hence, savepoints lets you rollback a particular part of a transaction than the whole transaction.

Syntax

Savepoint <savepoint name>;

Example

Savepoint pointA;

Set Transaction (Read Only OR Read Write) Name Transaction_Name;

Set Transaction Read Name 'TransactionA';