Last modified: November 30, 2024

This article is written in: 🇺🇸

Transaction Control Language (TCL)

In the world of databases, maintaining data integrity and consistency is crucial, especially when multiple operations are involved. Imagine you're at a bank's ATM, transferring money from your savings to your checking account. You wouldn't want the system to deduct the amount from your savings without adding it to your checking due to some error, right? This is where Transaction Control Language (TCL) comes into play, ensuring that all related operations either complete successfully together or fail without affecting the database's consistency.

Understanding Transactions

A transaction is a sequence of one or more SQL statements that are executed as a single unit of work. The primary goal is to ensure that either all operations within the transaction are completed successfully or none are, preserving the database's integrity.

The ACID Properties

Transactions adhere to the ACID properties:

Key TCL Commands

TCL provides several commands to manage transactions effectively:

Let's delve into each of these commands with examples to understand how they work.

BEGIN TRANSACTION

Starting a transaction is like saying to the database, "I'm about to perform several operations that should be treated as a single, indivisible unit."

BEGIN TRANSACTION;

After this command, all subsequent operations are part of the transaction until it's either committed or rolled back.

COMMIT

The COMMIT command saves all changes made during the transaction to the database permanently.

Example Scenario:

Suppose we have an employees table and want to increase the salary of all employees in department 1 by 10%.

Employees Table Before:

employee_id department_id salary
1 1 1000
2 1 1200
3 2 1500

SQL Commands:

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

COMMIT;

Employees Table After:

employee_id department_id salary
1 1 1100
2 1 1320
3 2 1500

Interpretation:

ROLLBACK

If something goes wrong during a transaction, you can undo all changes made within it using ROLLBACK.

Example Scenario:

We attempt the same salary update but realize there's a mistake before committing.

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

-- Oops! Realized we should only increase by 5%
ROLLBACK;

Employees Table After Rollback:

employee_id department_id salary
1 1 1000
2 1 1200
3 2 1500

Interpretation:

SAVEPOINT

A savepoint allows you to set a point within a transaction to which you can later roll back, without affecting the entire transaction.

Example Scenario:

We decide to update salaries in two departments but want the option to undo the second update without losing the first.

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

SAVEPOINT dept1_updated;

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 2;

Employees Table After Updates:

employee_id department_id salary
1 1 1100
2 1 1320
3 2 1575

Interpretation:

ROLLBACK TO SAVEPOINT

If we decide to undo the changes made after a savepoint, we can roll back to it.

ROLLBACK TO dept1_updated;

COMMIT;

Employees Table After Rollback to Savepoint and Commit:

employee_id department_id salary
1 1 1100
2 1 1320
3 2 1500

Interpretation:

Full Transaction Flow

Here's the entire process in one go:

BEGIN TRANSACTION;

UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 1;

SAVEPOINT dept1_updated;

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 2;

-- Decide to undo the last update
ROLLBACK TO dept1_updated;

COMMIT;

Transactions in Real Life

Transactions are essential in scenarios where multiple operations need to be treated atomically.

Banking Example

Imagine transferring $500 from Account A to Account B.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 'A';

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 'B';

COMMIT;

If any part of this transaction fails (e.g., insufficient funds in Account A), a ROLLBACK ensures neither account balance is changed, maintaining financial integrity.

Rollback Capabilities Across Databases

Different databases handle transactions in slightly different ways. Here's a comparison:

Feature PostgreSQL MySQL Oracle SQL Server
Transactions Yes Yes Yes Yes
Rollback Support Yes Yes Yes Yes
Savepoints Yes Yes Yes Yes
DML Rollback Yes Yes Yes Yes
DDL Rollback Limited Limited No Limited
Autocommit Default Off On Off On
Isolation Levels Multiple Multiple Multiple Multiple

Key Points:

Best Practices for Using Transactions

Table of Contents

    Transaction Control Language (TCL)
    1. Understanding Transactions
      1. The ACID Properties
    2. Key TCL Commands
      1. BEGIN TRANSACTION
      2. COMMIT
      3. ROLLBACK
      4. SAVEPOINT
      5. ROLLBACK TO SAVEPOINT
      6. Full Transaction Flow
    3. Transactions in Real Life
      1. Banking Example
    4. Rollback Capabilities Across Databases
    5. Best Practices for Using Transactions