How can a user implicitly end the current MySQL transaction?

The following is how to implicitly end the current MySQL transaction -

By running the DDL statement

The current MySQL transaction will be implicitly ended, And the changes will be committed by running any DDL statement (such as CREATE or DROP database, Create, ALTER or DROP table or stored routine). This 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)

Between transactions we run DDL statements, so the transaction will end implicitly. MySQL will save all changes and cannot roll 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 the client session

Disconnecting the client session will implicitly end the current session, in which case the changes will be reverted roll.

By terminating the client session

Terminating the client session will also implicitly end the current session, in which case the changes will also be rolled back.

Via the START TRANSACTION command

Currently if we run the START TRANSACTION command, the transaction will end implicitly. 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 commit the changes.

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)

The above is the detailed content of How can a user implicitly end the current MySQL transaction?. For more information, please follow other related articles on the PHP Chinese website!