Home » SQL

Transactions (Properties and Control Commands) in SQL

In this article, we are going to learn about SQL transactions, its properties and its commands, which are known as Transaction control commands.
Submitted by Shubham Singh Rajawat, on January 04, 2018

A transaction is a unit of work performed against a database. It is a sequence work done in a logical order. It follows the principle of all or none i.e. either all the work is successful and transaction committed or none of it will be completed i.e. transaction failed so it will roll back to a previous save point.

For example: Suppose Rs 5000 is transferred from account A to B. Then the transaction is done by using two small operations

1. Deduct Rs 5000 from account A
2. Add Rs 5000 in account B

If either of the two steps failed the transaction will be referred as failed and the whole transaction must be done again.

Properties of transaction

Transaction have four properties referred by the acronym ACID

1. Atomicity - It ensures that all the operations in a unit are completed successfully and the transaction is committed, if a problem occurs in any of the single operation it will halt and rollback to previous save point.
2. Consistency - ensures that the database will change on a successful committed transaction.
3. Isolation - ensures that the modification done by each transaction must be independent of each other.
4. Durability - Data modifications occur due to a successful transaction are kept permanently.

Transaction Control Commands - Commands to control a transaction

1) COMMIT – commit command is used to save changes done by a transaction on the database.

Syntax:

commit;

2) ROLLBACK - rollback command is used to undo a transaction that is not committed/saved in the database.

Syntax:

rollback;

3) SAVEPOINT - a savepoint in a transaction is used to rollback to that point so that we do not have to undo the whole transaction.

Syntax:

savepoint savepoint_name;

To rollback to a savepoint of name savepoint_name: rollback to savepoint_name

4) RELEASE SAVEPOINT - This command is used to remove a savepoint.

Syntax:

release savepoint savepoint_name;