What happens to the current MySQL transaction if the session is killed by DBA?

Suppose if a session is killed 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 killed.

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)

Now we start a new transaction and delete a row from table ‘marks’

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

mysql> Delete from marks where id = 4;
Query OK, 1 row affected (0.00 sec)

Before COMMIT or ROLLBACK, open the other window to run another instance of MySQL and run the SHOW PROCESSLIST command as follows −

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost:49303
     db: query
Command: Sleep
   Time: 22
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: root
   Host: localhost:49350
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

Now, kill the current transaction by running KILL command as follows −

mysql> KILL 2;
Query OK, 0 rows affected (0.00 sec)

Now, when we return back to the current transaction and COMMIT command is executed as follws −

mysql> COMMIT;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: query
Query OK, 0 rows affected (1.01 sec)

The above KILL command, which killed the current session, forces MySQL to roll back the changes made in the current transaction. It can be observed from the following query that no row has been deleted from 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)