innodb_kill_idle_transaction works only when table in question is completely idle
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Triaged
|
Medium
|
Unassigned |
Bug Description
To me, It look likes innodb_
mysql [localhost] {msandbox} (world) > show session variables like '%version%';
+------
| Variable_name | Value |
+------
| innodb_version | 5.6.21-rel70.1 |
| protocol_version | 10 |
| slave_type_
| version | 5.6.21-70.1 |
| version_comment | Percona Server (GPL), Release 70.1, Revision 698 |
| version_
| version_compile_os | Linux |
+------
mysql [localhost] {msandbox} (world) > show variables like 'innodb_kill%';
+------
| Variable_name | Value |
+------
| innodb_
+------
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > show variables like 'autocommit';
+------
| Variable_name | Value |
+------
| autocommit | OFF |
+------
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > START TRANSACTION; SELECT NOW(); INSERT INTO City_backup (Name,CountryCo
Query OK, 0 rows affected (0.00 sec)
+------
| NOW() |
+------
| 2015-02-03 03:36:22 |
+------
1 row in set (0.00 sec)
Query OK, 1 row affected (0.01 sec)
mysql [localhost] {msandbox} (world) > SELECT NOW(); SELECT * FROM City_backup;
+------
| NOW() |
+------
| 2015-02-03 03:36:29 |
+------
1 row in set (0.00 sec)
+----+-
| ID | Name | CountryCode | District | Population |
+----+-
| 7 | Karachi | PK | abcd | 1000000 |
+----+-
1 row in set (0.00 sec)
.
.
mysql [localhost] {msandbox} (world) > SELECT NOW(); SELECT * FROM City_backup;
+------
| NOW() |
+------
| 2015-02-03 03:37:32 |
+------
1 row in set (0.00 sec)
+----+-
| ID | Name | CountryCode | District | Population |
+----+-
| 7 | Karachi | PK | abcd | 1000000 |
+----+-
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (world) > show engine innodb status\G
*******
Type: InnoDB
Name:
Status:
------------
TRANSACTIONS
------------
Trx id counter 183151
Purge done for trx's n:o < 183148 undo n:o < 0 state: running but idle
History list length 25
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 183150, ACTIVE 76 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 71, OS thread handle 0x7f862c938700, query id 310450 localhost msandbox init
show engine innodb status
Trx read view will not see trx with id >= 183151, sees < 183151
TABLE LOCK table `world`
-------
END OF INNODB MONITOR OUTPUT
=======
Transaction is still ACTIVE after 76 seconds and It obeys innodb_
mysql [localhost] {msandbox} (world) > SELECT NOW(); SELECT * FROM City_backup;
+------
| NOW() |
+------
| 2015-02-03 03:42:02 |
+------
1 row in set (0.01 sec)
Empty set (0.00 sec)
Based on code review, transaction is initialized like this:
...
trx->idle_ start = 0;
trx->last_ stmt_start = 0;
...
and then (storage/ innobase/ srv/srv0srv. c):
...
if (trx->state == TRX_ACTIVE
&& trx->mysql_thd
&& innobase_ thd_is_ idle(trx- >mysql_ thd)) {
ib_ int64_t start_time = innobase_ thd_get_ start_time( trx->mysql_ thd);
ulong thd_id = innobase_ thd_get_ thread_ id(trx- >mysql_ thd);
...
and then in storage/ innobase/ handler/ ha_innodb. cc:
extern "C" thd_get_ start_time( ======= ======= =====*/ FOR_KILLIDLE
return( (ib_int64_ t)thd_start_ time((const THD*) thd));
ib_int64_t
innobase_
/*=====
const void* thd) /*!< in: thread handle (THD*) */
{
#ifdef EXTENDED_
#else
return(0); /*dummy value*/
#endif
}