SQL TCL Commands

A transaction is a logical unit of work that contains one or more SQL statements. TCL commands control when changes made by those statements become permanent or can be undone.

COMMIT makes all changes in the current transaction permanent. After a commit, the changes are visible to other sessions and cannot be rolled back (unless the DBMS supports advanced recovery features).

COMMIT;

ROLLBACK undoes changes made in the current transaction and returns the database to the last committed state (or to a specified savepoint).

ROLLBACK;

You can also rollback to a previously set savepoint (see SAVEPOINT below):

ROLLBACK TO savepoint_name;

SAVEPOINT creates a checkpoint inside a transaction. You can rollback to that checkpoint without rolling back the entire transaction.

SAVEPOINT savepoint_name;

Example (combined usage)

-- Start: (depends on client; many DBs start a transaction automatically)
INSERT INTO student (name, marks) VALUES ('Alice', 85);

SAVEPOINT sp1;

UPDATE student SET marks = marks + 5 WHERE name = 'Alice';

-- Something went wrong; revert to the savepoint
ROLLBACK TO sp1;

-- Make remaining changes permanent
COMMIT;

Notes & best practices:

• Group related DML statements in a single transaction and COMMIT only when all operations succeed.
• Use SAVEPOINTs to allow partial undo inside large transactions.
• Avoid long-running transactions — they hold locks and can harm concurrency and performance.
• Transaction behavior (autocommit, rollback behavior) varies across RDBMSs — check your database documentation.