Manuel Gentile

Back to SQL Course - Back to Command Types

Transaction Control Language

It’s used for handling the transaction, its commands are COMMIT and ROLLBACK.

What is a transaction in SQL?

A SQL transaction is a set of one or more SQL statements that interact with a database and are essential for maintaining database integrity.

A transaction can be committed or rollbacked (becomes undone) to a database as a single logical unit.

Transactions are used to preserve integrity when multiple operations are executed concurrently or when various users interact concurrently with a database.

Commit

It’s used for saving data permanently on database.

Once a transaction has been committed, it’s not possible to restore its previous state.

It only works on DML commands.

COMMIT;

Commit Example

Let’s assume table employee is empty.

We are going to insert a single row, then we persist data (commit) and close the session.

Another user opens a new one and selects employee.

The result will be the single row.

SELECT * FROM employee;
-- no records found

INSERT INTO employee VALUES (1,'Manuel', ‘Gentile');

COMMIT;
-- this user closes the session

-- another user opens the session
SELECT * FROM employee;
-- 1 record found (1,'Manuel', ‘Gentile')

Rollback

It’s used for reverting data not persisted on database by a transaction.

Once a transaction has been rollbacked, it’s not possible to restore its previous state.

It only works on DML commands.

ROLLBACK;

Rollback Example

Let’s assume table employee is empty.

We are going to insert a single row, then we rollback the transaction data and close the session.

Another user opens a new one and selects employee.

The result will be no records.

SELECT * FROM employee;
-- no records found

INSERT INTO employee VALUES (1,'Manuel', 'Gentile');

ROLLBACK;
-- this user closes the session

-- another user opens the session
SELECT * FROM employee;
-- no records found

Back to SQL Course - Back to Command Types

---

Let’s connect

If you want to learn more about the topic, connect or send me a DM.