Bug #73114 trx_active_transactions counter in innodb_metrics goes negative after errors
Submitted: 25 Jun 2014 13:30
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.19 OS:Any
Assigned to: CPU Architecture:Any

[25 Jun 2014 13:30] Shane Bester 
Description:
the problem is the counter is increased once and decreased twice, 
here (mysql-trunk):

MONITOR_INC(MONITOR_TRX_ACTIVE) :
	mysqld-debug.exe!trx_start_low:1318
	mysqld-debug.exe!trx_start_if_not_started_xa_low:2845
	mysqld-debug.exe!row_insert_for_mysql_using_ins_graph:1505
	mysqld-debug.exe!row_insert_for_mysql:1650
	mysqld-debug.exe!ha_innobase::write_row:6626

MONITOR_DEC(MONITOR_TRX_ACTIVE):
	mysqld-debug.exe!trx_rollback_to_savepoint_low:135
	mysqld-debug.exe!trx_rollback_to_savepoint:164
	mysqld-debug.exe!row_mysql_handle_errors:730
	mysqld-debug.exe!row_insert_for_mysql_using_ins_graph:1541
	mysqld-debug.exe!row_insert_for_mysql:1650
	mysqld-debug.exe!ha_innobase::write_row:6626

MONITOR_DEC(MONITOR_TRX_ACTIVE):
	mysqld-debug.exe!trx_rollback_to_savepoint_low:135
	mysqld-debug.exe!trx_rollback_for_mysql_low:189
	mysqld-debug.exe!trx_rollback_for_mysql:220
	mysqld-debug.exe!innobase_rollback:3753
	mysqld-debug.exe!ha_rollback_low:1660
	mysqld-debug.exe!TC_LOG_DUMMY::rollback:106
	mysqld-debug.exe!ha_rollback_trans:1728
	mysqld-debug.exe!trans_rollback_stmt:412

How to repeat:
set global innodb_monitor_disable='%';
set global innodb_monitor_reset_all='%';
set global innodb_monitor_enable='trx_active_transactions';

drop table if exists t1;
create table t1(a int primary key)engine=innodb;
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
select name,subsystem,count from information_schema.innodb_metrics 
where name='trx_active_transactions';
[19 Feb 2015 13:19] MySQL Verification Team 
also : http://bugs.mysql.com/bug.php?id=75966
[19 Jun 2015 3:01] Davi Arnaut 
Decrement number of active transactions whenever a transaction is committed

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: active-transaction-counter-decrement-on-commit.patch (application/octet-stream, text), 1.84 KiB.
[19 Jun 2015 3:13] Davi Arnaut 
Contributed patch is a proof of concept, it still has some issues with resurrected transactions (crash recovery).
[8 Jul 2015 21:04] Davi Arnaut 
The remark about resurrected transactions it not important, the counter gets reset after start anyway.