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

• Related Questions & Answers
• How can a user implicitly end current MySQL transaction?
• How can a user start new MySQL transaction?
• How can we check the current MySQL transaction isolation level?
• How can we find out the current transaction mode in MySQL?
• How can we implement a MySQL transaction?
• What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
• How changes, made in the current transaction, can be permanently recorded in MySQL database?
• How changes, made in the current transaction, can be permanently eliminated from MySQL database?
• End User Database
• What happens to the current MySQL transaction if the session is ended in the middle of a transaction?
• How can we grant privileges to a MySQL user?
• How can we revoke privileges from a MySQL user?
• What MySQL would return if we refer a user variable which is not assigned any value explicitly?
• Can we explicitly define datatype in a Python Function?
• How MySQL manage the behavior of a transaction?