TCL - Transaction Control Language

TCL (Transaction Control Language) is a subset of SQL used to manage transactions in a database. It controls how changes are committed or rolled back to maintain data integrity and consistency.

• Manages database transactions
• Commits changes using COMMIT
• Undoes changes using ROLLBACK
• Supports partial rollback with SAVEPOINT

1. BEGIN TRANSACTION Command

Starts a new transaction. All SQL commands after this are treated as part of the same transaction until COMMIT or ROLLBACK is used.

Syntax:

BEGIN TRANSACTION transaction_name ;

Example of SQL Transaction with a Bank Transfer Scenario

Let’s look at an example of a bank transfer between two accounts. This example demonstrates the usage of multiple queries in a single transaction.

BEGIN TRANSACTION; 

 -- Deduct $150 from Account A 
UPDATE Accounts SET Balance = Balance - 150
WHERE AccountID = 'A'; 

 -- Add $150 to Account B 
UPDATE Accounts 
SET Balance = Balance + 150
WHERE AccountID = 'B'; 

 -- Commit the transaction if both operations succeed 
COMMIT;

• Both UPDATE queries are executed as part of a single transaction.
• If all updates succeed, COMMIT permanently saves the changes.
• If any update fails, ROLLBACK cancels all changes to maintain consistency.

ROLLBACK;

2. COMMIT Command

The COMMIT command is used to save all changes made during the current transaction to the database. Once a transaction is committed, the changes are permanent. 

Syntax:

COMMIT;

Example: The Student table contains basic details like ID, Name, and Age, and will be used to demonstrate transaction commands such as SAVEPOINT, ROLLBACK, and RELEASE.

Following is an example which would delete those records from the table which have age = 20 and then COMMIT the changes in the database. 

Query:

DELETE FROM Student WHERE AGE = 20;COMMIT;

Output:

• Rows with AGE = 20 are deleted.
• The deletion is now permanent and cannot be undone.

3. ROLLBACK Command

The ROLLBACK command undoes all changes in the current transaction, useful if something goes wrong or you want to cancel the changes. The database will revert to the state it was in before the BEGIN TRANSACTION was executed.

Syntax:

ROLLBACK;

Query:

DELETE FROM Student WHERE AGE = 20;ROLLBACK;

Output:

• Rows with AGE = 20 are deleted temporarily.
• ROLLBACK restores the table to its original state before the delete.

4. SAVEPOINT Command

A SAVEPOINT is like a marker inside a transaction. It lets you go back to a specific point without canceling the whole transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Query:

SAVEPOINT SP1;DELETE FROM Student WHERE AGE = 20;SAVEPOINT SP2;

Output:

• Create SP1 before deleting.
• Delete students with age 20.
• Create SP2 after deleting.

5. ROLLBACK TO SAVEPOINT

The ROLLBACK TO SAVEPOINT command allows us to roll back the transaction to a specific savepoint, effectively undoing changes made after that point.

Syntax:

ROLLBACK TO SAVEPOINT SAVEPOINT_NAME;

Query:

ROLLBACK TO SP1;

Output:

• Undo the delete, so students with age 20 come back.
• Table goes back to how it was at SP1.

6. RELEASE SAVEPOINT Command

RELEASE SAVEPOINT removes a savepoint so you can’t rollback to it. It helps manage transactions and their changes. It is used to initiate a database transaction and used to specify characteristics of the transaction that follows. 

Syntax: 

RELEASE SAVEPOINT SAVEPOINT_NAME;

Query:

RELEASE SAVEPOINT SP2; -- Release the second savepoint.

Output:

Savepoint released

• Now SP1 is gone.
• You cannot do ROLLBACK TO SP1 anymore.
• Table does not change, it stays as it was after the last rollback.

For Example: In banking system transactions ensure money transfers are safe by making sure either all steps succeed or all fail, keeping data consistent.

Uses of TCL Commands

• COMMIT: Permanently saves changes.
• ROLLBACK: Cancels changes when errors occur.
• SAVEPOINT: Allows partial undo within a transaction.
• BEGIN TRANSACTION: Starts a controlled set of operations.
• RELEASE SAVEPOINT: Manages transaction checkpoints.