What will happen to MySQL current transaction, if in the middle of that transaction, the DDL statement is executed?

The current MySQL transaction will be committed and ended when any of the DDL statement such as CREATE or DROP databases, Create, ALTER or DROP tables or stored routines is executed in the middle of the current transaction. All the database changes made in the current transaction will be made 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 example above, a DDL statement has been executed in the middle of a transaction 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    |
| 4    | Rahul   | History   | 40    |
| 5    | Yashraj | English   | 48    |
| 6    | Manak   | History   | 70    |
+------+---------+---------+---------+
6 rows in set (0.00 sec)

Chandu yadav

Published on 22-Feb-2018 06:25:27

• Related Questions & Answers
• What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
• What happens to the current MySQL transaction if the session is ended in the middle of a transaction?
• What happens to the current MySQL transaction if the session is killed by DBA?
• What is transaction processing? Explain the properties of the transaction(DBMS)
• What are the different ways the transaction can be executed(DBMS)?
• Which statement, other than START TRANSACTION, is used for starting a transaction?
• How can we check the current MySQL transaction isolation level?
• How can we find out the current transaction mode in MySQL?
• How MySQL manage the behavior of a transaction?
• What are the states of transaction in DBMS?
• How can a user explicitly end current MySQL transaction?
• How can a user implicitly end current MySQL transaction?
• What will happen if the MySQL AUTO_INCREMENT column reaches the upper limit of the data type?
• What happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
• Is it true that IRCTC will not take transaction charges on online ticket bookings?