TCL (Transaction Control Language) in SQL

Introduction

After learning how to define database structure (DDL), manipulate data (DML), and control access (DCL), the next important concept is managing transactions.

This is where Transaction Control Language (TCL) comes in.

TCL includes SQL commands used to manage transactions in a database, ensuring that data remains consistent and reliable even when multiple operations are performed.

What is a Transaction

A transaction is a group of one or more SQL operations executed as a single unit.

For example:

  • Transferring money from one bank account to another

  • Updating multiple related records

All operations in a transaction must either:

  • Complete successfully, or

  • Fail (rollback)

This ensures data integrity.

What is TCL

Transaction Control Language (TCL) is a set of SQL commands used to control and manage transactions in a database.

TCL ensures that database operations follow important rules called ACID properties, which maintain data reliability.

Common TCL Commands

The most commonly used TCL commands are:

  • COMMIT

  • ROLLBACK

  • SAVEPOINT

COMMIT Command

The COMMIT command is used to save all changes made during a transaction.

Once committed, the changes become permanent.

Example:

COMMIT;

After COMMIT, the data cannot be undone.

ROLLBACK Command

The ROLLBACK command is used to undo changes made during a transaction.

It restores the database to its previous state.

Example:

ROLLBACK;

This is useful when an error occurs during a transaction.

SAVEPOINT Command

The SAVEPOINT command creates a checkpoint within a transaction.

You can roll back to that specific point instead of undoing the entire transaction.

Example:

SAVEPOINT sp1;

To roll back to a savepoint:

ROLLBACK TO sp1;

Example Transaction

START TRANSACTION; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; UPDATE accounts SET balance = balance + 1000 WHERE id = 2; COMMIT;

If something goes wrong:

ROLLBACK;

This ensures that either both updates happen or neither happens.

ACID Properties in Transactions

TCL helps maintain the ACID properties:

  • Atomicity → All operations succeed or none

  • Consistency → Data remains valid

  • Isolation → Transactions do not interfere

  • Durability → Changes are permanent after commit

These properties ensure reliable database operations.

Characteristics of TCL

  • Used to manage transactions

  • Works with DML operations

  • Ensures data consistency and integrity

  • Allows undoing and saving changes

Example Scenario

In a banking system:

  • Money is deducted from one account

  • Money is added to another account

If one step fails, the entire transaction must be rolled back.

TCL ensures that partial updates do not occur.

Key Points to Remember

  • TCL stands for Transaction Control Language

  • It manages database transactions

  • Main commands are COMMIT, ROLLBACK, and SAVEPOINT

  • Ensures data consistency and reliability

  • Works closely with DML operations