DBMS Transactional Control

Transactional Control

"A transaction is a set of database operations that performs a particular task".
Transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.
Transaction can be closed by using COMMIT or ROLLABCK command.When a transaction is closed,all the table locks acquired during that transaction,are released.
There are following commands used to control transactions:
  • COMMIT: to save the changes.
  • ROLLBACK:to rollback the changes.
  • SAVEPOINT: creates points within groups of transactions in which to ROLLBACK.

COMMIT  :

The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
A transaction can be commited either 1)Explicitly , or 2) Implicitly.

1) Explicit Commit:

Syntax :

                             COMMIT;
2) Implicit Commit:

There are some operations which forces a COMMIT to occur automatically.
1. QUIT Commands
2. EXIT Commands
3. DDL Commands

Example:

Run SQL Command Line
SQL>Select * from student;

roll_no               name
-----------     -----------------
5468             parimal

SQL>insert into student values(7855,'preet');
1 row created.

SQL>COMMIT;

Commit complete.

ROLLBACK :

The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.

Syntax:

                             ROLLBACK;
                        OR
                          ROLLBACK TO SAVEPOINT_NAME;

Example:

Run SQL Command Line
SQL>delete from student;

2 rows deleted.

SQL>ROLLBACK;

Rollback compelete.

SQL>select * from student;

Roll_no        Name
----------  --------------
5468         parimal
7855         preet

SAVEPOINT :

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Syntax :

                          SAVEPOINT savepoint_name;
When a Rollback is used with savepoint ,part of the transaction is cancelled.

Example:

Run SQL Command Line
SQL>savepoint s1;

Savepoint created.

SQL>delete from student;

2 rows deleted.

SQL>ROLLBACK to s1;

Rollback compelete.

SQL>select * from student;

Roll_no        Name
----------  --------------
5468         parimal
7855         preet
It is also possible to create more than one savepoint.

Share This Page on:


Ask Question

Advanced SQL