TCL (Transaction Control Language)
TCL commands are used to persist or discard the data after any transaction(DML Operation) is made on a database.
TCL provides the following commands:
Commit is used to save the data and roll back to discard it.
How it works internally?
Whenever any transaction is initiated on a database table, a temporary memory(called as rollback segment) is created in the server. All the tables involved in the transaction are copied to the rollback segment. This copy is called as snapshot. All transaction happens on the original table.
- When a COMMIT command is issued, the snapshot is deleted and the changes becomes permanent.
- When ROLLBACK command is issued, the snapshot is copied back to the original tables and the changes are lost.
Note: The above mechanism is for Oracle database. Also this is just a high level overview, there are more complex steps involved in the entire process.
INSERT INTO EMP ... INSERT INTO EMP ... COMMIT; UPDATE EMP ... DELETE FROM EMP ... ROLLBACK;
In the above example only the 1st two transactions are persisted in the database.
Note: In the following circumstances data is automatically rollbacked:
- When a user session is terminated abnormally.
- When the system crashes.
Savepoint is a logical pointer pointing to a transaction or a group of transaction, which is set by the user. Savepoints are made so that a rollback can be performed to that particular point.
Note: Rollback to a savepoint is excluding of that point.
Example: INSERT INTO EMP... SAVEPOINT A; INSERT INTO EMP... INSERT INTO EMP... SAVEPOINT B; INSERT INTO EMP... SAVEPOINT C; ROLLBACK TO B;
In the above example only the records in green are retained and other are discarded.