SQL Transaction With Examples

SQL Transaction:

Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.

Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

Transactions are essential for maintaining data integrity, both for multiple related operations and when multiple users that update the database concurrently.

Properties of Transactions

  • Atomicity: ensures that all operations within the work unit are completed successfully.
  • Consistency: ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation: enables transactions to operate independently of and transparent to each other.
  • Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.

Transaction Control:

  • SET TRANSACTION
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

SET TRANSACTION:

SET TRANSACTION: The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write

Syntax:

  							 
SET TRANSACTION [ READ WRITE | READ ONLY ];

COMMIT: The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

Example:

Consider CUSTOMER Table:


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | PRAYAG   |  21 | RANCHI    |  2500    |
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  5 | MUKESH   |  25 | GUJRAT    |  4500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+


This is an example will delete those records from the table which have age = 25 and then COMMIT the changes in the database.

  SQL> DELETE FROM CUSTOMERS
  WHERE AGE = 25;
  SQL> COMMIT;

Therefore, one row from the above table will be deleted and the SELECT statement would produce the below result.


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | PRAYAG   |  21 | RANCHI    |  2500    |
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+



The ROLLBACK Command:

The process of reversing changes is called rollback. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax:

ROLLBACK;


Example

Let's assume we are having a table as bellow:


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | PRAYAG   |  21 | RANCHI    |  2500    |
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+


This is an example, which will delete those records from the table which have the age = 25 and then ROLLBACK the changes in the database.

  SQL> DELETE FROM CUSTOMERS
  WHERE AGE = 25;
  SQL> ROLLBACK;

Therefore, the delete operation would not impact the table and the SELECT statement would produce the following result.


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | PRAYAG   |  21 | RANCHI    |  2500    |
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  5 | MUKESH   |  25 | GUJRAT    |  4500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+


The SAVEPOINT Command

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

Syntax for a SAVEPOINT

SAVEPOINT SAVEPOINT_NAME;

This is an example where you plan to delete the three different records from the CUSTOMERS table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.

Example: the CUSTOMERS


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | PRAYAG   |  21 | RANCHI    |  2500    |
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  5 | MUKESH   |  25 | GUJRAT    |  4500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+

The bellow code block contains the series of operations.

SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.

Now that the three deletions have taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone
SQL> ROLLBACK TO SP2;
Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2.

SQL> SELECT * FROM CUSTOMERS;


+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | VIKASH   |  23 | Delhi     |  1200    |
|  3 | RAKESH   |  23 | CHENNAI   |  2300    |
|  4 | RAVI     |  20 | BHOPAL    |  3500    |
|  5 | MUKESH   |  25 | GUJRAT    |  4500    |
|  6 | PANKAJ   |  26 | GIRIDIH   |  4200    |
|  7 | RAHUL    |  19 | PUNJAB    | 90000    |
+----+----------+-----+-----------+----------+
6 rows selected