Skip to main content

TCL (Transaction Control Language)

What is TCL?​

It is a subset of the SQL language used to control transactions in a database.

A transaction is a set of SQL operations that are executed as a single unit. Either all of them complete, or none of them are applied (this ensures data integrity).

Why use TCL?​

  • Ensure the atomicity of operations
  • Maintain data consistency
  • Control errors
  • Improve reliability
  • Allows dividing operations into controlled steps

Common uses of TCL​

  • Commit changes to the database (with COMMIT).
  • Revert changes if something fails (with ROLLBACK).
  • Define intermediate points in a transaction to partially undo (with SAVEPOINT).
  • Configure transaction properties (with SET TRANSACTION).

How to use TCL​

Start the transaction

BEGIN TRANSACTION;

Execute SQL operations

UPDATE Account SET balance = balance - 100 WHERE id = 1;
UPDATE Account SET balance = balance + 100 WHERE id = 2;

Commit the transaction with COMMIT

COMMIT;

Revert the transaction with ROLLBACK

ROLLBACK;

Use SAVEPOINT for intermediate points

SAVEPOINT point1;

-- some operation that might fail

ROLLBACK TO point1;

Example​

  • Bank transfer with TCL

Create simplified accounts table

CREATE TABLE Account (
id INT PRIMARY KEY,
name VARCHAR(100),
balance DECIMAL(10, 2)
);

INSERT INTO Account (id, name, balance) VALUES
(1, 'Ana', 1000.00),
(2, 'Luis', 500.00);

Transfer with transaction control

BEGIN TRANSACTION;

-- Subtract 200 from Ana
UPDATE Account SET balance = balance - 200 WHERE id = 1;

-- Add 200 to Luis
UPDATE Account SET balance = balance + 200 WHERE id = 2;

-- Commit changes
COMMIT;

If something fails, do rollback

BEGIN TRANSACTION;

UPDATE Account SET balance = balance - 200 WHERE id = 1;

-- We verify balance after subtraction
SELECT balance FROM Account WHERE id = 1;

-- If balance < 0, we revert
-- Suppose with logic in your application you detect the error and execute:
ROLLBACK;

-- If everything is fine, you commit with:
-- COMMIT;