Introduction to Transaction Control Language (TCL) in SQL

Overview of SQL Sublanguages

1. Data Definition Language (DDL)
2. Data Manipulation Language (DML)
3. Data Control Language (DCL)
4. Transaction Control Language (TCL)

In this session, we focus on the transaction control language (TCL).

TCL: An Analogy

• Checkpoints or save points allow us to avoid starting over in a journey.
• Similarly, in databases, TCL provides mechanisms to manage transactions effectively, allowing us to backtrack or save progress.

Why TCL is Needed

• Deals with the logical transactions in database operations.
• Necessary for managing operations that involve multiple steps, such as a money transfer between accounts.
• Helps to reverse changes at certain points, like save points in a journey.

TCL Commands

1. Commit

   • Saves a transaction permanently in the database.
   • Syntax: COMMIT;
   • Example: If Rahul adds 100 rows to a database containing 500 rows and then issues a commit, the database will have 600 rows.

2. Save Point

   • Saves transactions temporarily, allowing rollback to that point.
   • Syntax: SAVEPOINT save_point_name;
   • Allows rolling back only some transactions rather than all.

3. Roll Back

   • Restores the database to the last committed state or to a specific save point.
   • Syntax: ROLLBACK TO save_point_name;
   • Example: ROLLBACK TO test_purpose; restores the database to the state at the save point named "test_purpose."

4. Set Transaction

   • Configures the transaction mode as either read and write or read-only.
   • Syntax: SET TRANSACTION READ ONLY;
   • Example: Establishes that operations in the current transaction will be read-only.

Summary

• TCL is essential for managing database transactions, offering commands to commit, create save points, roll back, and set transaction properties.
• Each command has its specific role in ensuring data integrity and efficient transaction management.