How can a user explicitly end current MySQL transaction?
Following are the ways with the help of which current MySQL transaction can be ended explicitly −
By COMMIT command
The current transaction will be ended explicitly if we will run the COMMIT command. In this case, the changes will be committed.
Example
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 COMMIT statement will explicitly end the transaction and changes will be saved.
mysql> SELECT * FROM Marks;
+------+---------+---------+-------+
| Id | Name | Subject | Marks |
+------+---------+---------+-------+
| 1 | Aarav | Maths | 50 |
| 2 | Harshit | Maths | 55 |
+------+---------+---------+-------+
2 rows in set (0.00 sec)
By ROLLBACK command
The current transaction will be ended explicitly if we will run the ROLLBACK command. In this case, the changes will be rolled back.
Example
Suppose we have the following data in table ‘marks’ and we applied the transaction and ROLLBACK command as follows −
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(3, 'Rahul','History',40);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Marks Values(4, 'Yashraj','English',48);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
In this example, the ROLLBACK statement will explicitly end the transaction and changes will be rolled back.
mysql> SELECT * FROM Marks;
+------+---------+---------+-------+
| Id | Name | Subject | Marks |
+------+---------+---------+-------+
| 1 | Aarav | Maths | 50 |
| 2 | Harshit | Maths | 55 |
+------+---------+---------+-------+
2 rows in set (0.00 sec)
Published on 22-Feb-2018 06:13:00
