- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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 are the different ways the transaction can be executed(DBMS)?
- What is transaction processing? Explain the properties of the transaction(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 can a user explicitly end current MySQL transaction?
- How can a user implicitly end current MySQL transaction?
- What happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
- How changes, made in the current transaction, can be permanently recorded in MySQL database?
- How MySQL manage the behavior of a transaction?
- How changes, made in the current transaction, can be permanently eliminated from MySQL database?
- What is a shielded transaction?

Advertisements