How can a user implicitly end current MySQL transaction?

Following are the ways with the help of which current MySQL transaction can be ended implicitly −

By Running DDL statement

The current MySQL transaction will end implicitly and changes will be committed by running any of the DDL statement such as CREATE or DROP databases, Create, ALTER or DROP tables or stored routines. It is because, in MySQL, these statements cannot be rolled back.

Example

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

mysql> INSERT INTO MARKS Values(3,'gaurav','Comp',69);
Query OK, 1 row affected (0.26 sec)

mysql> Create table student(id int, Name Varchar(10),);
Query OK, 0 rows affected (0.84 sec)

In between a transaction we run a DDL statement hence this transaction will end implicitly. MySQL will save all the changes and it cannot be rolled back. We can observe it with the help of the following result set −

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

mysql> select * from marks;
+------+---------+-----------+-------+
| Id | Name | Subject | Marks |
+------+---------+-----------+-------+
| 1 | Aarav | Maths | 50 |
| 1 | Harshit | Maths | 55 |
| 3 | Gaurav | Comp | 69 |
+------+---------+-----------+-------+
3 rows in set (0.00 sec)

By disconnecting a client session

Disconnecting a client session will implicitly end the current session and in this case, the changes will be rolled back.

By killing a client session

Killing a client session will also implicitly end the current session and in this case, the changes will be rolled back also.

By START TRANSACTION command

The current transaction will be ended implicitly if we will run START TRANSACTION 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(4, 'Rahul','History',40);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Marks Values(5, 'Yashraj','English',48);
Query OK, 1 row affected (0.00 sec)

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

In this example, the START TRANSACTION statement will implicitly end the transaction and changes will be committed.

mysql> select * from marks;
+------+---------+-----------+-------+
| Id | Name | Subject | Marks |
+------+---------+-----------+-------+
| 1 | Aarav | Maths | 50 |
| 1 | Harshit | Maths | 55 |
| 3 | Gaurav | Comp | 69 |
| 4 | Rahul | History | 40 |
| 5 | Yashraj | English | 48 |
+------+---------+-----------+-------+
5 rows in set (0.00 sec)