What are TCL commands in SQL?

Introduction

Transaction Control Language (TCL) commands are essential for SQL to monitor changes implemented by Data Manipulation Language (DML) statements. These commands allow 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 function and importance of TCL commands in SQL.
• Explore common TCL commands: COMMIT, ROLLBACK, and SAVEPOINT.
• Learn how these commands help manage transactions within a database.
• Explore practical examples to better understand their applications.

What are TCL commands?

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

1. COMMIT
2. BACK
3. SAVE POINT
4. RETURN TO SAVE POINT
5. SET UP TRANSACTION

Read also: Introduction to SQL commands and sublanguages

Applications of TCL commands in SQL

First, let's create a database and a table and start inserting data.

CREATE DATABASE company_db;

USE company_db;

Note:

Make sure auto-commit 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 ends all transactions performed in the current session, ensuring that all changes to the database are permanently recorded 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 table and then uses COMMIT in the transaction to make the change permanent.

ROLLBACK Command

The ROLLBACK command rolls back transactions that have not yet been committed (saved permanently). This is useful 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 insert.

SAVEPOINT Command

The SAVEPOINT command sets a specific point in a transaction to which you can return later. This function allows you to undo parts of a transaction without reversing 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 employees table, creates a savepoint av, and then rolls back to the savepoint, undoing the second insert and preserving the first. If we use ROLLBACK, we revert the changes to the permanent ones saved with COMMIT.

Conclusion

TCL commands are fundamental in SQL for transaction management and data integrity. Users and administrators monitor transactions using COMMIT, ROLLBACK, and SAVEPOINT, ensuring that they complete or roll back changes as needed. These commands are critical to providing consistent and reliable database operations.

Also Read: SQL: A Complete Guide from Basics to Advanced Level

Frequent questions