Tuesday 24 March 2015
These are Transaction Control Language (TCL) statements used to manage the changes made by DML statements.
Rollback:
This is used for undoing the work done in the current transaction. It will restore database to original state since the last COMMIT.This command also releases the locks if any hold by the current transaction.
The command used in SQL for this is simply:
ROLLBACK;
All the transactions will be undone once we rollback.
Savepoint:
This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it.
The command used in SQL for this is simply:
SAVEPOINT savepointname;
For example:
UPDATE…..
DELETE….
SAVEPOINT e1;
INSERT….
UPDATE….
SAVEPOINT e2; …
It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part of a transaction. Say for instance in the above
ROLLBACK TO SAVEPOINT e2;
This results in the rollback of all statements after savepoint e2
Commit:
This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction.
This command used to make the database changes permanent(i.e.save work done).
we can't perform Roll Back once we commit the transaction.
The command used in SQL for this is simply:
COMMIT;
Rollback:
This is used for undoing the work done in the current transaction. It will restore database to original state since the last COMMIT.This command also releases the locks if any hold by the current transaction.
The command used in SQL for this is simply:
ROLLBACK;
All the transactions will be undone once we rollback.
Savepoint:
This is used for identifying a point in the transaction to which a programmer can later roll back. That is it is possible for the programmer to divide a big transaction into subsections each having a savepoint defined in it.
The command used in SQL for this is simply:
SAVEPOINT savepointname;
For example:
UPDATE…..
DELETE….
SAVEPOINT e1;
INSERT….
UPDATE….
SAVEPOINT e2; …
It is also possible to define savepoint and rollback together so that programmer can achieve rollback of part of a transaction. Say for instance in the above
ROLLBACK TO SAVEPOINT e2;
This results in the rollback of all statements after savepoint e2
Commit:
This is used to end the transaction and make the changes permanent. When commit is performed all save points are erased and transaction locks are released. In other words commit ends a transaction and marks the beginning of a new transaction.
This command used to make the database changes permanent(i.e.save work done).
we can't perform Roll Back once we commit the transaction.
The command used in SQL for this is simply:
COMMIT;
