DBMS – Transaction

A transaction can be defined as a group of tasks and the propagation of one or more changes to the database. A single task that is the minimum processing unit which cannot be divided further.

Example -: Suppose a bank employee transfers Rs 500 from A's account to B's account. This very easy and small transaction involves several low-level tasks.

Properties of Transactions

A transaction in a database system have to maintain Atomicity, Consistency, Isolation, and Durability this is commonly known as ACID properties

Atomicity -: This ensures that all operations within the work unit are completed successfully and the transaction must be treated as an atomic unit, otherwise, the transaction is aborted at the point of failure and all the previous operations are rolled back to their former state.

Consistency -: This ensures that the database properly changes states upon a successfully committed transaction. If the database was in a consistent state before the execution of a transaction, this must remain consistent after the execution of the transaction as well.

Isolation -: The property of isolation states that all the transactions will be carried out and executed as if it’s the only transaction in the system. No transaction will affect the existence of any other transaction. It enables transactions to operate independently of and transparent to each other.

Durability -: This ensures that the result or impact of a committed transaction persists in case of a system failure.

Transaction Control Commands

The following commands are used to control transactions.

• COMMIT –It is use to save the changes.

Syntax

COMMIT;

Example

Consider the CUSTOMERS table having the subsequent records −

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ram     |  32 | noida      |  2000.00 |

|  2 | Tammy |  26 | Delhi     |  1500.00 |

|  3 | komal   |  23 | Kosi       |  2000.00 |

|  4 | Chetan  |  26 | Goa       |  6500.00 |

Following is an example which would delete those records from the table which have age = 26 and then COMMIT the changes in the database.

SQL> DELETE FROM CUSTOMERS

   WHERE AGE = 26;

SQL> COMMIT;

• ROLLBACK − It is use to roll back the changes.

Syntax

ROLLBACK;

Example-:

Following is an example, which would delete those records from the table which have the age = 26 and then ROLLBACK the changes in the database.

SQL> DELETE FROM CUSTOMERS

   WHERE AGE = 26;

SQL> ROLLBACK;

• SAVEPOINT − It is use creates points within the groups of transactions in which to ROLLBACK.

Syntax

SAVEPOINT SAVEPOINT_NAME;                                                            

Example-:

SQL> SAVEPOINT SP1;

Savepoint created.

SQL> DELETE FROM CUSTOMERS WHERE ID=1;

1 row deleted.

• SET TRANSACTION − It is use Places a name on a transaction.

Syntax

SET TRANSACTION [ READ WRITE | READ ONLY ];