What happens to the current MySQL transaction if the session is ended in the middle of a transaction?

Suppose if a session is ended in the middle of a transaction then that current MySQL transaction will be rolled back by MySQL and ended. It means that all the database changes made in the current transaction will be removed. It is called n implicit rollback when the session is ended.

Example

Suppose we have the following values in the table ‘marks’

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)

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

mysql> UPDATE marks SET Name = ‘Yash’ Where id = 5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> quit;
Bye

In the example above, after updating the value in the table, the session is ended by running quit statement. When we check the table after starting the session again, the updated value has been rolled back by MySQL because the session is ended in the middle of a transaction.

Moumita

Published on 22-Feb-2018 06:23:28

• Related Questions & Answers
• What happens to the current MySQL transaction if the session is killed by DBA?
• What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
• What will happen to MySQL current transaction, if in the middle of that transaction, the DDL statement is executed?
• What happens to MySQL temporary tables if MySQL session is ended?
• What is transaction processing? Explain the properties of the transaction(DBMS)
• What happens when we use COMMIT in MySQL stored procedure and one of the transaction, under START transaction, fails?
• How MySQL manage the behavior of a transaction?
• How can we check the current MySQL transaction isolation level?
• How can we find out the current transaction mode in MySQL?
• 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?
• 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 is dirty read in a transaction(DBMS)?