23 - Transactions in SQL

In SQL a transaction is the change made to a database. It can be either Update, Insert or delete statements. Transactions are a number of statements that affect the database and have been accomplished in manual way or by some programming language. Normally transactions are used in programming language to update or make changes to different tables.

Like consider an example of online portal. One user has just made a purchase of a product, hence an INSERT statement would create a new row in Purchase_details table. And if other user cancelled or deleted one order, then the record would be DELETE from the Purchase_details table. All these are examples of Transactions to the Purchase_details table.

During the transactions it is important to maintain the data integrity and handle database errors. In a multiuser system, database corruption or incorrect data values are possible. Consider an example, Jeff the Sales manager reads from a table to know the sales per saleperson, while at the same time some sales person David is updating his sales data on same table. Now Jeff,  based on the data that he accessed writes a value back in the field unaware that David has just made the changes.

Interactions between two or more users on the same table at same time can cause serious problems, hence it is important to restrict changes so that they occur only within transactions.

For data protection, database should pass the ACID test. Here  ACID or (atomicity, consistency, Isolation and Durability) are the standard properties or characteristics that are required in a Transaction:

  1. Atomicity: Transactions are required to be atomic, that is the transaction is executed in entirety and can be restored to the state it would have been in if the transaction would not have been executed.
  2. Consistency: Ensuring that database properly updates data after a successful transaction.
  3. Isolation: The transactions should not be affected by the other transactions executing at same time.
  4. Durability : The database should be in proper state after a successful (commit) or unsuccessful (rollback) transaction. The database should  have uncorrupted and up-to date data.

Transactions can be controlled using below Data control languages (DCL):

1. COMMIT

COMMIT command is used to save changes of all transactions since last commit or ROLLBACK command.

Syntax:

              COMMIT;

Example:

Consider below table. Delete the rows with SALARY < 13000 and then COMMIT the changes.

+----------+----------------+-----------------+---------------------------+-----------+
| emp_id | emp_name | address     | emailid                 | salary |
+----------+----------------+-----------------+---------------------------+-----------+
|      1 | John     | CA          | john@abc.com            |  12000 |
|      2 | Michel   | CA          | michel@abc.com          |  13000 |
|      3 | Linda    | CA          | linda@abc.com           |  12500 |
|      4 | Michael  | CA          | michael@abc.com         |  20000 |
|      5 | Max      | CA          | max@abc.com             |  30000 |
+----------+----------------+-----------------+---------------------------+-----------+

 

mysql> DELETE FROM EMP

    -> WHERE SALARY < 13000;

Query OK, 2 rows affected (0.20 sec)

mysql> COMMIT;

Query OK, 0 rows affected (0.00 sec)

Now the table would look like below:

+----------+----------------+-----------------+---------------------------+-----------+
| emp_id | emp_name | address     | emailid                 | salary |
+----------+----------------+-----------------+---------------------------+-----------+
|      2 | Michel   | CA          | michel@abc.com          |  13000 |
|      4 | Michael  | CA          | michael@abc.com         |  20000 |
|      5 | Max      | CA          | max@abc.com             |  30000 |
+----------+----------------+-----------------+---------------------------+-----------+

2. ROLLBACK

ROLLBACK command as name suggest rolls back the changes done to database that have yet been saved or committed. The ROLLBACK can be used to undo transactions since last COMMIT or ROLLBACK command was issued. 

   ROLLBACK;

Example:

Consider below table. Delete the rows with SALARY < 13000 and then ROLLBACK the changes.

+----------+----------------+-----------------+---------------------------+-----------+
| emp_id | emp_name | address     | emailid             | salary |
+----------+----------------+-----------------+---------------------------+-----------+
|      1 | John     | CA          | john@abc.com        |  12000 |
|      2 | Michel   | CA          | michel@abc.com      |  13000 |
|      3 | Linda    | CA          | linda@abc.com       |  12500 |
|      4 | Michael  | CA          | michael@abc.com     |  20000 |
|      5 | Max      | CA          | max@abc.com         |  30000 |
+----------+----------------+-----------------+---------------------------+-----------+
 

mysql> DELETE FROM EMP

    -> WHERE SALARY < 13000;

Query OK, 2 rows affected (0.20 sec)

 

mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

No changes would be done to the table as the changes were rolled back.

Normally in any application the transactions are given as below:

 

Transaction-1

Various sql statements

COMMIT or ROLLBACK

Transaction -2

Various sql statements

COMMIT or ROLLBACK

Query OK, 2 rows affect

 3. SAVEPOINT

SAVEPOINT is a point in the transaction till which you can roll back instead  of rolling back entire transaction. There may be cases when in a transaction you are updating 2 or more tables. Suppose 1 table needs to be changed irrespective of where table 2 is changed or not, then we can put a savepoint after update of table 1, so that even if there is an error in update of table2 or table3 we can roll back the changes till the savepoint . 

SAVEPOINT savepoint_name;

 

Syntax for Rolling back :

ROLLBACK  TO savepoint_name;

Consider the same table:

+----------+----------------+-----------------+---------------------------+-----------+
| emp_id | emp_name | address     | emailid             | salary |
+----------+----------------+-----------------+---------------------------+-----------+
|      1 | John     | CA          | john@abc.com        |  12000 |
|      2 | Michel   | CA          | michel@abc.com      |  13000 |
|      3 | Linda    | CA          | linda@abc.com       |  12500 |
|      4 | Michael  | CA          | michael@abc.com     |  20000 |
|      5 | Max      | CA          | max@abc.com         |  30000 |
+----------+----------------+-----------------+---------------------------+-----------+
 

Mysql>SAVEPOINT SAVE1;

Mysql> DELETE FROM EMP WHERE SALARY = 12000;

Mysql>SAVEPOINT SAVE2;

Mysql>DELETE FROM EMP WHERE SALARY = 13000;

Mysql>SAVEPOINT SAVE3;

Mysql>DELETE FROM EMP WHERE SALARY = 12500;

Mysql>ROLLBACK TO SAVE2;

The output would be:

+----------+----------------+-----------------+---------------------------+-----------+
| emp_id | emp_name | address     | emailid             | salary |
+----------+----------------+-----------------+---------------------------+-----------+
|      2 | Michel   | CA          | michel@abc.com      |  13000 |
|      3 | Linda    | CA          | linda@abc.com       |  12500 |
|      4 | Michael  | CA          | michael@abc.com     |  20000 |
|      5 | Max      | CA          | max@abc.com         |  30000 |
+----------+----------------+-----------------+---------------------------+-----------+

Please note, all SAVEPOINTs for current transaction are deleted if you give execute COMMIT or ROLLBACK that does not have a SAVEPOINT name.

4. RELEASE SAVEPOINT

RELEASE SAVEPOINT would remove the SAVEPOINT that have been created.

Syntax:

   RELEASE SAVEPOINT savepoint_name;

 Once the SAVEPOINT has been released, you cannot use rollback to the undo the transaction since the SAVEPOINT.

5. SET TRANSACTION

SET TRANSACTION is used to initiate a transaction and the characteristics of the transaction.

   SET TRANSACTION [READ-ONLY | READ-WRITE];

Like us on Facebook