SQL TCL

← Previous Next →

TCL (Transaction Control Language) in SQL is a set of commands used to manage transactions in a database. A transaction is a group of SQL operations that are executed together as a single unit. TCL ensures data integrity and consistency.

Main TCL Commands
1. COMMIT
      • Purpose: Permanently saves all changes made during the current transaction.
      • After a commit, the changes cannot be undone.
2. ROLLBACK
      • Purpose: Undoes changes made in the current transaction.
      • Used when an error occurs or you want to cancel the operation.
3. SAVEPOINT
      • Purpose: Creates a point inside a transaction to which you can later roll back.
4. SET TRANSACTION (less commonly used)
      • Purpose: Specifies transaction properties (like read-only or isolation level).

Example: COMMIT

INSERT INTO students VALUES (101, 'Rahul');
COMMIT;

Explanation:
The inserted record becomes permanent in the database.

Example: ROLLBACK

DELETE FROM students WHERE id = 101;
ROLLBACK;

Explanation:
The delete operation is cancelled, and the data returns to its previous state.

Example: SAVEPOINT

SAVEPOINT sp1;

UPDATE students
SET marks = 90
WHERE id = 101;

ROLLBACK TO sp1;

Explanation:
The database returns to the savepoint (sp1) instead of undoing the entire transaction.

Example: SET TRANSACTION

SET TRANSACTION READ ONLY;

Simple Example of a Transaction

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Explanation:
Money is transferred safely between two accounts. If any error occurs, ROLLBACK can cancel the entire transaction.


Summary:
Command
1. COMMIT - Save changes permanently
2. ROLLBACK - Undo changes
3. SAVEPOINT - Create a rollback point
4. SET TRANSACTION - Set transaction properties

← Previous Next →
Topics