Sunday 24 February 2013

Transaction Control Language (TCL) :



A Transaction Control Language (TCL)  is a used to control transactional processing in a database.

COMMIT :
To apply the transaction by saving the database changes. We cannot roll back a transaction after a commit transaction statement is issued because the data modifications have been made a permanent part of the database. COMMIT statement releases all row and table locks, and makes the changes to visible for other users.
Syntax :
COMMIT [TRAN | TRANSACTION] [TRANSACTION_NAME | TRANSACTION_VARIABLE_NAME]

ROLLBACK :
To undo all changes of a transaction. Rollback command is used for restore database to original since last commit. Rollback transaction erases all data modifications made from the start of the transaction or to a savepoint. The ROLLBACK statement in SQL cancels the proposed changes in a pending database transaction. The transaction can be rolled back completely by specifying the transaction name in the ROLLBACK statement.
Syntax :
ROLLBACK [TRAN | TRANSACTION ] [SAVEPOINT_NAME | SAVEPOINT_VARRIABLE ]


SAVEPOINT : 
To divide the transaction into smaller sections. It defines breakpoints for a transaction to allow partial rollbacks. Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update. Updates and rollbacks are expensive operations, so savepoints are effective only if the probability of encountering the error is low and the cost of checking the validity of an update beforehand is relatively high.
Syntax :
SAVE [TRAN | TRANSACTION ] [SQVEPOINT_NAME | SAVEPOINT_VARIABLE_NAME]