What are the TCL Commands in SQL?

Introduction

TCL (Transaction Control Language) commands are crucial in SQL overseeing changes enacted by DML (Data Manipulation Language) statements. These commands enable users and database administrators to manage transaction processes, maintaining data consistency and integrity. This article explores the main TCL commands, their functions, and their practical uses.

 Learning Objectives

• Understand the role and importance of TCL commands in SQL.
• Explore the common TCL commands: COMMIT, ROLLBACK, and SAVEPOINT.
• Learn how these commands help in managing transactions within a database.
• Examine practical examples to understand their applications better.

What are TCL Commands?

TCL (Transaction Control Language) commands are used to manage transactions in the database. The primary TCL commands are:

1. COMMIT
2. ROLLBACK
3. SAVEPOINT
4. ROLLBACK TO SAVEPOINT
5. SET TRANSACTION

Also Read: Introduction to SQL Commands and Sub Languages

 Applications of TCL Commands in SQL

Let’s first create a database and a table and start inserting the data.

CREATE DATABASE company_db;

USE company_db;

Note:

Ensure autocommit is disabled to run these commands:

SET autocommit = 0;

Now, start a transaction:

START TRANSACTION;

CREATE TABLE employees (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    email VARCHAR(100),

    hire_date DATE

);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (1, 'Elon', 'Dave', '[email protected]', '2023-01-15'),

       (2, 'James', 'Smith', '[email protected]', '2023-02-20');

This query creates a database and a table and then inserts 2 records.

COMMIT Command

The COMMIT command finalizes all transactions performed in the current session, ensuring it permanently records all changes in the database once executed.

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (3, 'Alice', 'Steve', '[email protected]', '2023-03-30');

COMMIT;

This command inserts a new record into the employee’s table and then uses COMMIT in the transaction to make the change permanent.

ROLLBACK Command

The ROLLBACK command reverses transactions that haven’t been committed (permanently saved) yet. This is handy for undoing changes if an error occurs during the transaction process.

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (4, 'Blue', 'Brown', '[email protected]', '2023-04-10');

ROLLBACK;

This command attempts to insert a new record into the `employees` table but then returns the transaction, undoing the insertion.

 SAVEPOINT Command

The SAVEPOINT command establishes a specific point in a transaction that can be rolled back to later. This feature allows you to undo parts of a transaction without reverting the entire operation.

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (5, 'Charlie', 'Davis', '[email protected]', '2023-05-15');

SAVEPOINT av1;

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date)

VALUES (6, 'Eve', 'White', '[email protected]', '2023-06-20');

ROLLBACK TO av1;

This TCL command inserts two new records into the employee’s table, creates a savepoint av, and then rolls back to the savepoint, undoing the second insertion while keeping the first insertion. If we use ROLLBACK, we revert the changes to the permanent ones saved using COMMIT.

Conclusion

TCL commands are vital in SQL for transaction management and data integrity. Users and administrators oversee transactions using COMMIT, ROLLBACK, and SAVEPOINT, ensuring they finalize or reverse changes as needed. These commands are key to providing consistent and reliable database operations.

Also Read: SQL: A Full Fledged Guide from Basics to Advance Level

Frequently Asked Questions

Mounish V

I'm a tech enthusiast, graduated from Vellore Institute of Technology. I'm working as a Data Science Trainee right now. I am very much interested in Deep Learning and Generative AI.