innodb_kill_idle_transaction works only when table in question is completely idle

[Muhammad Irfan]

To me, It look likes innodb_kill_idle_transaction feature kills transaction which are not committed longer than innodb_kill_idle_transaction threshold but It only kills when table is completely Idle. I would expect more clear documentation with intended behavior along with example to make it clear.

mysql [localhost] {msandbox} (world) > show session variables like '%version%';
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.21-rel70.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-70.1 |
| version_comment | Percona Server (GPL), Release 70.1, Revision 698 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+

mysql [localhost] {msandbox} (world) > show variables like 'innodb_kill%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_kill_idle_transaction | 20 |
+------------------------------+-------+
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,CountryCode,District,Population) VALUES ('Karachi','PK','abcd','1000000');
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
*************************** 1. row ***************************
  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`.`City_backup` trx id 183150 lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Transaction is still ACTIVE after 76 seconds and It obeys innodb_kill_idle_transaction threshold when no activity performed on City_backup table.

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)

[Valerii Kravchuk (valerii-kravchuk)]

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);

                                if (trx->last_stmt_start != start_time) {
                                        trx->idle_start = now;
                                        trx->last_stmt_start = start_time;
                                } else if (difftime(now, trx->idle_start)
                                           > srv_kill_idle_transaction) {
                                        /* kill the session */
                                        mutex_exit(&kernel_mutex);
                                        innobase_thd_kill(thd_id);
                                        goto rescan_idle;
                                }
                        }
...

and then in storage/innobase/handler/ha_innodb.cc:

extern "C"
ib_int64_t
innobase_thd_get_start_time(
/*========================*/
        const void* thd) /*!< in: thread handle (THD*) */
{
#ifdef EXTENDED_FOR_KILLIDLE
        return((ib_int64_t)thd_start_time((const THD*) thd));
#else
        return(0); /*dummy value*/
#endif
}

[Laurynas Biveinis (laurynas-biveinis)]

I am not sure what is meant by a "completely idle table" here. Can you show a similar example where the transaction is killed as expected for you? In any case this might be code, not doc bug: the MTR testcase is similar to the tested scenario:

...
SET GLOBAL innodb_kill_idle_transaction=1;
...
BEGIN;
INSERT INTO t1 VALUES (4),(5),(6);
sleep 3;

--enable_reconnect
--source include/wait_until_disconnected.inc

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1607