| Bug #67906 | MySQL should kill idle transactions | ||
|---|---|---|---|
| Submitted: | 14 Dec 2012 10:09 | Modified: | 14 Dec 2012 10:21 |
| Reporter: | Lixun Peng (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S4 (Feature request) |
| Version: | 5.* | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Kill Idle Transactions, Long Transactions | ||
[14 Dec 2012 10:10]
Lixun Peng
this patch based on 5.1.61, and we used it on our production environment
Attachment: 5.1.61_server_kill_idle_trx.diff (application/octet-stream, text), 12.90 KiB.
[15 Dec 2012 8:38]
MySQL Verification Team
this effectively deals with the old problem i reported :) http://bugs.mysql.com/bug.php?id=54455 (innodb needs a way to limit consistent read snapshot age)
Description: In MySQL, if we start a transaction and not commit, this transaction maybe hold locks! So I add 3 parameters for MySQL: TRX_CHANGES_IDLE_TIMEOUT: how long a transaction that changed data can keep living TRX_READONLY_IDLE_TIMEOUT: how long a transaction that readonly can keep living TRX_IDLE_TIMEOUT: how long a transaction can keep living (include readonly and not-readonly) For example: SET TRX_CHANGES_IDLE_TIMEOUT= 10; SET AUTOCOMMIT = 0; UPDATE table_a SET col_a=100; (after 10s, no other operations) killed this session, rollback transaction. How to repeat: For example: After 5.5, Session 1 SESSION 2 SET AUTOCOMMIT = 0; SELECT * FROM table_a LIMIT 10; ALTER TABLE table_a ADD col_b int; (table_a will do "copying to tmp table" until wait for rename, then it will wait for metalock) if Session1 doesn't commit, Session2 can't finished. Suggested fix: I will kill the idle transactions, it means a transaction has not continue to do other things from last operation.