Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
What Happens to the Current Transaction If the Session Is Killed
What Happens to the Current Transaction If the Session Is Killed? - MySQL FAQs - Transaction Management: Commit or Rollback
✍: 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.
2007-05-11, 5270👍, 0💬
Popular Posts:
How To Increment Dates by 1? - MySQL FAQs - Introduction to SQL Date and Time Handling If you have a...
How Do I Run JUnit Tests from Command Window? To run JUnit tests from a command window, you need to ...
How To Compare Two Strings with strcmp()? - PHP Script Tips - PHP Built-in Functions for Strings PHP...
How To Enter Binary Numbers in SQL Statements? - MySQL FAQs - Introduction to SQL Basics If you want...
How To Control White Spaces between Table Cells? - XHTML 1.0 Tutorials - Understanding Tables and Ta...