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:
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:
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:
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.
Ask Question