Collections:
Transaction Roll Back when Session Killed in MySQL
What Happens to the Current Transaction If the Session Is Killed in MySQL?
✍: FYIcenter.com
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
2017-08-03, 1875🔥, 0💬
Popular Posts:
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...
What is dba.FYIcenter.com Website about? dba.FYIcenter.com is a Website for DBAs (database administr...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
What is test testing area for? The testing area is provided to allow visitors to post testing commen...