Scribd
Upload
6 views2 pages

Understanding TCL Commands in SQL

TCL (Transaction Control Language) commands manage database transactions, allowing for operations to be committed or rolled back as a single unit. Key commands include COMMIT to save changes, ROLLBACK to undo changes, and SAVEPOINT to create markers for partial rollbacks. Examples illustrate how these commands function within transactions, demonstrating the effects of committing, rolling back, and using savepoints.

Uploaded by

bbuzz2606
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
6 views2 pages

Understanding TCL Commands in SQL

TCL (Transaction Control Language) commands manage database transactions, allowing for operations to be committed or rolled back as a single unit. Key commands include COMMIT to save changes, ROLLBACK to undo changes, and SAVEPOINT to create markers for partial rollbacks. Examples illustrate how these commands function within transactions, demonstrating the effects of committing, rolling back, and using savepoints.

Uploaded by

bbuzz2606
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

TCL:

TCL commands are used to manage transactions in a database. A transaction is a set


of SQL statements that are executed as a single unit.
If all operations succeed, we commit them permanently; if something fails, we
rollback to undo.

TCL Commands
Command Description
COMMIT Saves all changes made in the current transaction
ROLLBACK Undo changes since last COMMIT or to a SAVEPOINT
SAVEPOINT Creates a marker inside a transaction to rollback part of it

Transaction Flow Example


START TRANSACTION; --

Example Table
CREATE TABLE accounts (
acc_no INT PRIMARY KEY,
acc_name VARCHAR(50),
balance INT
);

INSERT INTO accounts VALUES


(101, 'Rahul', 5000),
(102, 'Priya', 6000),
(103, 'Arun', 7000);

Example 1 – COMMIT
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 102;

COMMIT;

✔️ The transfer is saved permanently.

Example 2 – ROLLBACK
UPDATE accounts SET balance = balance - 2000 WHERE acc_no = 103;
UPDATE accounts SET balance = balance + 2000 WHERE acc_no = 101;

ROLLBACK;

❌ Both updates are undone, data returns to previous state.

Example 3 – SAVEPOINT & ROLLBACK TO


START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE acc_no = 102;


SAVEPOINT SP1;

UPDATE accounts SET balance = balance + 500 WHERE acc_no = 103;


SAVEPOINT SP2;

ROLLBACK TO SP1;
COMMIT;

Output
Changes after SP1 rollback will be undone

First update is saved, second update is cancelled

-------------------------------------
Sql Query:

start transaction;
select * from student;
delete from student where afid=16;
savepoint sp6;
delete from student where afid=12;
savepoint sp7;
delete from student where afid=15;
savepoint sp8;
delete from student where afid=16;
rollback to savepoint sp7;

-- Roll back last 2 transaction(sp7 and sp8)

You might also like