SQL transactions

SQL transactions

---

A transaction is a sequence of tasks performed in a logical order on a database, neither manually by the user nor automatically by some database program.

A transaction is actually one or more changes to the database. W hen you create an update or delete a record on a table, you're already using transactions. It is important for the database to control transactions to ensure data integrity and to handle database errors.

In practice, many SQL queries are typically grouped together and executed as part of a transaction.

The property of the transaction

---

Transactions have four standard attributes, typically represented by the acronym ACID:

• Atomicity: Guarantees that all operations in the task are completed;
• Consistency: If the transaction executes successfully, the state of the database is correctly transformed.
• Isolation: Ensures that different transactions perform independently and transparently with each other.
• Persistence: Even in the event of a system failure, the results of previously successful transactions persist.

Transaction control

---

There are four commands for controlling transactions:

• COMMIT: Submit changes;
• ROLLBACK: Roll back changes;
• SAVEPOINT: Create a series of restore points within the transaction that can ROLLBACK;
• SET TRANSACTION: Naming transactions;

COMMIT command

---

The COMMIT command is used to save changes made by a transaction to the database.

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

The syntax of the COMMIT command looks like this:

COMMIT;

Example

---

Consider the CUSTOMERS table, which shows the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following example will delete the record in the table age-25 and then commit the changes (COMMIT) to the database.

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

The above statement will remove two rows of records from the table, and then executing the SELECT statement will result in the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

ROLLBACK command

---

The ROLLBACK command is used to undo transactions that have not yet been saved to the database.

The ROLLBACK command can only revoke transactions since the last COMMIT command or ROLLBACK command was executed.

The syntax of the ROLLBACK command is as follows:

ROLLBACK;

Example:

---

Consider the CUSTOMERS table, which shows the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following example removes all records from the table that are age-25, and then rolls back the changes made to the database by ROLLBACK.

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

The result is that the deletion operation does not affect the database. Now, executing the SELECT statement will result in the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SAVEPOINT command

---

SAVEPOINT is a state point in a transaction that allows us to roll back the transaction to a specific point instead of undoing the entire transaction.

The record of the SAVEPOINT command looks like this:

SAVEPOINT SAVEPOINT_NAME;

The command can only create save points (SAVEPOINT) between transaction statements. RollBACK commands can be used to undo a series of transactions.

The syntax of rolling back to a save point looks like this:

ROLLBACK TO SAVEPOINT_NAME;

In the following example, you plan to delete three different records from the CUSTOMERS table and create a SavePOINT (SAVEPOINT) before each deletion, allowing you to roll back to any save point at any time to restore the data to its original state.

Example

---

Consider the CUSTOMERS table, which shows the following:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The sequence of actions is as follows:

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 effect, if you change your mind and decide to roll back to the save point with the name SP2, the last two deletions will be undone because SP2 was created after the first deletion.

SQL> ROLLBACK TO SP2;
Rollback complete.

Note that because you roll back the database to SP2, only the first deletion really worked:

SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.

RELEASE SAVEPOINT command

---

The RELEASE SAVEPOINT command is used to remove previously created save points.

The syntax of RELEASE SAVEPOINT is as follows:

RELEASE SAVEPOINT SAVEPOINT_NAME;

Once the save point is released, you will no longer be able to use the ROLLBACK command to revoke transactions after the save point.

SET TRANSACTION command

---

The SET TRANSACTION command can be used to initialize a database transaction and specify the characteristics of subsequent transactions.

For example, you can specify a transaction as read-only or read-write.

The syntax of the SET TRANSACTION command is as follows:

SET TRANSACTION [ READ WRITE | READ ONLY ];