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)

Ankith Reddy

Published on 22-Feb-2018 06:13:00

Previous Page Print Page