How can we implement a MySQL transaction?

As we know that in a transaction, statements are executed as a unit. If any operation within the transaction fails, the entire transaction will fail and should be rolled back; otherwise, any changes made by the statements are saved to the database. For implementing a transaction MySQL provides the following statements −

START TRANSACTION

As the name suggests, the transaction begins with this statement. Basically, it notifies MySQL that the statements that follow should be treated as a single work unit until the transaction has been ended.

COMMIT

COMMIT Statement commits changes to the database. In other words, when a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect.

ROLLBACK

ROLLBACK command undoes any changes made by the statement and returns the database to the previous state i.e. in the state it was in when the transaction began.

Example

Following is an example to exhibit the implementation of a MySQL transaction −

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Aarav','Maths',50);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Marks Values(2, 'Harshit','Maths',55);
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

In this example, the transaction is initiated by the START TRANSACTION statement. Two INSERT statements are then executed followed by a COMMIT statement. COMMIT statement will save the changes to the database which can be observed from the following result set which shows that the values have been inserted into the table −

mysql> SELECT * FROM Marks;
 +------+---------+---------+-------+
| Id   | Name    | Subject | Marks |
+------+---------+---------+-------+
| 1    | Aarav   | Maths   | 50    |
| 2    | Harshit | Maths   | 55    |
+------+---------+---------+-------+
2 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Aarav','History',40);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Marks Values(1, 'Harshit','History',48);
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)

In this example, the transaction is initiated by the START TRANSACTION statement. Two INSERT statements are then executed followed by a ROLLBACK statement. ROLLBACK statement will undo the changes made to the database which can be observed from the following result set which shows that no new values have been inserted into the table −

mysql> SELECT * FROM Marks;
+------+---------+---------+-------+
| Id   | Name    | Subject | Marks |
+------+---------+---------+-------+
| 1    | Aarav   | Maths   | 50    |
| 1    | Harshit | Maths   | 55    |
+------+---------+---------+-------+
2 rows in set (0.00 sec)