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)

George John

Published on 22-Feb-2018 06:15:12

• Related Questions & Answers
• 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?
• What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current 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?
• End User Database
• 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?
• How MySQL manage the behavior of a transaction?
• What will happen to MySQL current transaction, if in the middle of that transaction, the DDL statement is executed?
• How can we change MySQL user password by using the ALTER USER statement?