- 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
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)
- Related Articles
- How can a user explicitly end current MySQL transaction?
- How can a user start new MySQL transaction?
- How can we check the current MySQL transaction isolation level?
- How can we find out the current transaction mode in MySQL?
- How can we implement a MySQL transaction?
- How changes, made in the current transaction, can be permanently recorded in MySQL database?
- How changes, made in the current transaction, can be permanently eliminated from MySQL database?
- 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?
- How can we grant privileges to a MySQL user?
- How can we revoke privileges from a MySQL user?
- End User Database
- How can we change MySQL user password by using the ALTER USER statement?
- What will happen to MySQL current transaction, if in the middle of that transaction, the DDL statement is executed?
- How MySQL manage the behavior of a transaction?
