Tough life, niubility parameter innodb_kill_idle_transaction

Roidba always thinks highly of reading official documents and doing experimental tests on his own. Only in this way can he become a qualified DBA.

Exercise your hands-on ability.

Kill Idle Transactions (supported by mariadb & percona server)

Solve the problem of developers writing SQL without commit

This feature limits the age of idle transactions, for all transactional storage engines. If a transaction is idle for more seconds than the threshold specified, it will be killed. This prevents users from blocking InnoDB purge by mistake.

In Percona Server 5.7.17-11 this feature has been re-implemented by setting a connection socket read timeout value instead of periodically scanning the internal InnoDB transaction list.

Version Specific Information

5.7.10-1:

Feature ported from Percona Server 5.6

5.7.17-11:

Feature re-implemented using socket timeouts

System Variables

Variable innodb_kill_idle_transaction

Version Info:

5.7.17-11-Variable is now an alias of kill_idle_transaction

Scope:

GLOBAL

Config:

YES

Dynamic:

YES

Variable Type:

INTEGER

Default Value:

0 (disabled)

Units:

Seconds

To enable this feature, set this variable to the desired seconds wait until the transaction is killed. NOTE: This variable has been deprecated and it will be removed in a future major release.

Variable kill_idle_transaction

Version Info:

5.7.17-11-Variable implemented

Scope:

GLOBAL

Config:

YES

Dynamic:

YES

Variable Type:

INTEGER

Default Value:

0 (disabled)

Units:

Seconds

If non-zero, any idle transaction will be killed after being idle for this many seconds.

Official website link

Https://www.percona.com/doc/percona-server/LATEST/management/innodb_kill_idle_trx.html