What happens if a DDL statement is executed in the middle of the current MySQL transaction?

When any DDL statement (such as CREATE or DROP database, CREATE, ALTER or DROP table or stored procedure) is executed in the current transaction, the current MySQL transaction will be committed and ended . All database changes made within the current transaction become permanent and cannot be rolled back.

Example

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

mysql> INSERT INTO MARKS Values(6,'Manak','History',70);
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)

As we can see in the above example, a DDL statement is executed in the middle of a transaction, so this transaction will end implicitly. MySQL will save all changes and cannot roll back. We can observe this through 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    |
| 4    | Rahul   | History   | 40    |
| 5    | Yashraj | English   | 48    |
| 6    | Manak   | History   | 70    |
+------+---------+---------+---------+
6 rows in set (0.00 sec)

The above is the detailed content of What happens if a DDL statement is executed in the middle of the current MySQL transaction?. For more information, please follow other related articles on the PHP Chinese website!