What Happens to the Current Transaction If the Session Is Killed in MySQL?

If a session is killed by the DBA, the current transaction in that session will be rolled back and ended. All the database changes made in the current transaction will be removed. This is called an implicit rollback when session is killed. The following tutorial exercise shows you that the KILL command forces the current transaction to be rolled back with all the changes:

>\mysql\bin\mysql -u dev -piyf fyi

mysql> DELETE FROM fyi_links where id = 112;
Query OK, 1 row affected (0.07 sec)

mysql> DELETE FROM fyi_links where id = 113;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM fyi_links;
+-----+---------------+-------+--------+--------------------
| id  | url           | notes | counts | created           
+-----+---------------+-------+--------+--------------------
| 101 | fyicenter.com | Good  |    999 | 2006-07-01 20:34:10
| 110 | centerfyi.com | Wrong |      0 | 2006-07-01 20:34:12
+-----+---------------+-------+--------+--------------------
2 rows in set (0.01 sec)

Keep the "dev" mysql window as is, and open another window to run another instance of mysql:

>\mysql\bin\mysql -u root -pretneciyf

mysql> SHOW PROCESSLIST;
+----+------+----------------+------+---------+------+------
| Id | User | Host           | db   | Command | Time | State
+----+------+----------------+------+---------+------+------
|  3 | dev  | localhost:4723 | fyi  | Sleep   |  171 |     
|  5 | root | localhost:4728 | fyi  | Query   |    0 | NULL
+----+------+----------------+------+---------+------+------
2 rows in set (0.08 sec)

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

Go back to the "dev" mysql window:

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

mysql> SELECT * FROM fyi_links;
+-----+---------------+-------+--------+--------------------
| id  | url           | notes | counts | created           
+-----+---------------+-------+--------+--------------------
| 101 | fyicenter.com | Good  |    999 | 2006-07-01 20:34:10
| 110 | centerfyi.com | Wrong |      0 | 2006-07-01 20:34:12
| 112 | oracle.com    | NULL  |   NULL | 2006-07-01 20:41:12
| 113 | mysql.com     | NULL  |   NULL | 2006-07-01 20:41:21
+-----+---------------+-------+--------+--------------------
4 rows in set (0.00 sec)

As you can see, two deleted records were rolled back as the session got killed by the DBA.

⇒ Read Consistency Support in MySQL in MySQL

⇐ Transaction Roll Back when Session Ended in MySQL

⇑ Transaction Management: Commit or Rollback in MySQL

⇑⇑ MySQL Database Tutorials