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, 5090👍, 0💬
Popular Posts:
What are the two fundamental objects in ADO.NET ? Datareader and Dataset are the two fundamental obj...
How To Assign Debug Privileges to a User? - Oracle DBA FAQ - Introduction to Oracle SQL Developer In...
Can Java object be locked down for exclusive use by a given thread? Yes. You can lock an object by p...
What Is the Data Pump Import Utility? - Oracle DBA FAQ - Loading and Exporting Data Oracle Data Pump...
How did you implement UML in your project ? PART II Implementation phase / Coding phase (Class diagr...