What are TCL Commands?

TCL stands for Transaction Control Language. It is a category of SQL commands used to manage transactions in a database.

In simple words, TCL commands are used to control whether the changes made by SQL statements should be saved permanently or cancelled.

 What is a Transaction?

A transaction is a group of one or more SQL operations that are treated as a single unit of work.

This means either all operations should be completed successfully, or none of them should be applied. This helps maintain accuracy and consistency in the database.

 Real-Life Analogy

 Why Do We Need TCL Commands?

TCL commands are important because database operations may sometimes fail due to errors, power failure, wrong queries, network problems, or application issues. If changes are saved incorrectly, data can become inconsistent.

TCL commands help us decide whether to confirm the changes or cancel them before they become permanent.

 Prerequisites Before Learning TCL Commands

Before learning TCL commands, you should understand some basic SQL concepts because TCL works closely with DML commands.

 ACID Properties of Transactions

Database transactions follow the ACID properties. These properties help make transactions reliable and safe.

 Main TCL Commands in MySQL

MySQL provides several transaction-related commands. The most commonly used TCL commands are COMMIT, ROLLBACK, and SAVEPOINT.

 Important MySQL Note: Autocommit

In MySQL, autocommit mode is enabled by default. This means each SQL statement is automatically saved unless you explicitly start a transaction.

To manually control a transaction, you can use START TRANSACTION. After that, you can use COMMIT to save changes or ROLLBACK to cancel changes.

 Check Autocommit Status

SELECT @@autocommit;

 Disable Autocommit

SET autocommit = 0;

 Enable Autocommit

SET autocommit = 1;

 Storage Engine Requirement

In MySQL, transaction control works properly with transactional storage engines such as InnoDB. If a table uses a non-transactional engine, rollback behavior may not work as expected.

 Example Table with InnoDB

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(10,2)
) ENGINE = InnoDB;

 START TRANSACTION Command

The START TRANSACTION command is used to begin a new transaction manually. After this command, MySQL waits until you use COMMIT or ROLLBACK.

 Syntax

START TRANSACTION;

 Alternative Syntax

BEGIN;

 COMMIT Command

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

 Syntax

COMMIT;

 Example: Insert and Commit

START TRANSACTION;

INSERT INTO accounts (account_id, account_name, balance)
VALUES (1, 'Amit Sharma', 5000.00);

COMMIT;

 Example: Update and Commit

START TRANSACTION;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 1;

COMMIT;

 ROLLBACK Command

The ROLLBACK command is used to undo changes made during the current transaction. It returns the database to the state it had before the transaction started.

 Syntax

ROLLBACK;

 Example: Delete and Rollback

START TRANSACTION;

DELETE FROM accounts
WHERE account_id = 1;

ROLLBACK;

 Example: Update and Rollback

START TRANSACTION;

UPDATE accounts
SET balance = balance - 2000
WHERE account_id = 1;

ROLLBACK;

 SAVEPOINT Command

The SAVEPOINT command is used to create a checkpoint inside a transaction. It allows you to roll back only part of a transaction instead of cancelling the whole transaction.

 Syntax

SAVEPOINT savepoint_name;

 Example

START TRANSACTION;

INSERT INTO accounts (account_id, account_name, balance)
VALUES (2, 'Neha Verma', 7000.00);

SAVEPOINT sp_first_insert;

INSERT INTO accounts (account_id, account_name, balance)
VALUES (3, 'Rahul Khan', 9000.00);

 ROLLBACK TO SAVEPOINT Command

The ROLLBACK TO SAVEPOINT command is used to undo changes made after a specific savepoint. It does not cancel the entire transaction.

 Syntax

ROLLBACK TO SAVEPOINT savepoint_name;

 Example

START TRANSACTION;

INSERT INTO accounts (account_id, account_name, balance)
VALUES (2, 'Neha Verma', 7000.00);

SAVEPOINT sp_first_insert;

INSERT INTO accounts (account_id, account_name, balance)
VALUES (3, 'Rahul Khan', 9000.00);

ROLLBACK TO SAVEPOINT sp_first_insert;

COMMIT;

 RELEASE SAVEPOINT Command

The RELEASE SAVEPOINT command is used to remove a savepoint from the current transaction. After releasing a savepoint, you cannot roll back to that savepoint anymore.

 Syntax

RELEASE SAVEPOINT savepoint_name;

 Example

START TRANSACTION;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 1;

SAVEPOINT sp_update_done;

RELEASE SAVEPOINT sp_update_done;

COMMIT;

 Complete Practical Example of TCL Commands

Let us understand TCL commands with a complete bank account example.

 Step 1: Create Database

CREATE DATABASE bank_db;

 Step 2: Use Database

USE bank_db;

 Step 3: Create Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100),
    balance DECIMAL(10,2)
) ENGINE = InnoDB;

 Step 4: Insert Sample Data

INSERT INTO accounts VALUES
(101, 'Amit Sharma', 10000.00),
(102, 'Neha Verma', 8000.00);

 Step 5: Transfer Money Using Transaction

START TRANSACTION;

UPDATE accounts
SET balance = balance - 2000
WHERE account_id = 101;

UPDATE accounts
SET balance = balance + 2000
WHERE account_id = 102;

COMMIT;

 Step 6: Cancel Transaction Using ROLLBACK

START TRANSACTION;

UPDATE accounts
SET balance = balance - 5000
WHERE account_id = 101;

ROLLBACK;

 Practical Example Using SAVEPOINT

SAVEPOINT is useful when a transaction has multiple steps and you want to undo only some steps.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 101;

SAVEPOINT after_deduction;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 102;

SAVEPOINT after_deposit;

UPDATE accounts
SET balance = balance - 300
WHERE account_id = 101;

ROLLBACK TO SAVEPOINT after_deposit;

COMMIT;

 Difference Between COMMIT, ROLLBACK, and SAVEPOINT

 Relationship Between TCL and DML

TCL commands do not directly insert, update, or delete data. Instead, they control the effect of DML commands.

 Good and Bad Transaction Handling

 Common Mistakes in TCL Commands

 Important Notes About TCL Commands

 Quick Revision Table

 Interview Questions on TCL Commands

 Important Exam Points

 Summary

TCL commands are used to manage transactions in SQL. They help control whether changes made by DML commands should be saved permanently or undone.

In MySQL, transactions are commonly controlled using START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT, and SET autocommit.

TCL commands are extremely important in real-world applications such as banking systems, e-commerce orders, inventory management, booking systems, and any system where data accuracy is critical.