InnoDB_trx_id not logged in slow query log for BEGIN/COMMIT

Bug #897715 reported by Aurimas Mikalauskas on 2011-11-29
18
This bug affects 3 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Won't Fix
Medium
Unassigned
5.6
Fix Released
Medium
Nickolay Ihalainen
5.7
Fix Released
Medium
Nickolay Ihalainen

Bug Description

When using InnoDB_trx_id to filter out statements issued by a certain transaction, it is not easy to get the time transaction started and finished exactly, espcially if connections are reused by many transactions. Had InnoDB_trx_id been logged for BEGIN/COMMIT statement, that would help me save a great deal of time in this process.

AFAIK, there's no InnoDB_trx_id when BEGIN statement is run since transaction is only started when first statement is run against InnoDB table, however it should definitely be known at the time of COMMIT.

Here's a sample slow query log entry for BEGIN/COMMIT statement:

# Time: 111129 13:35:25.0000307027
# User@Host: s[s] @ a [172.16.0.126]
# Thread_id: 3683352 Schema: s Last_errno: 0 Killed: 0
# Query_time: 0.000017 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1322573725;
BEGIN;

# Time: 111129 13:35:25.0000305323
# User@Host: s[s] @ a [172.16.0.126]
# Thread_id: 3683352 Schema: s Last_errno: 0 Killed: 0
# Query_time: 0.000565 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Rows_read: 1
# Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1322573725;
COMMIT;

Changed in percona-server:
assignee: nobody → Valentine Gostev (longbow)
Changed in percona-server:
status: New → Confirmed
Valentine Gostev (longbow) wrote :

Since we cannot retrieve InnoDB_trx_id for BEGIN statement, as MySQL does not know wether we will touch InnoDB tables or not, InnoDB_trx_id could not be used to identify transaction ID for BEGIN statement. At the same it is possible to find InnoDB_trx_id if transaction contained queries related to InnoDB.

But it make sense to use the same transaction identity both for BEGIN and COMMIT.

What should be done:
1. Check the available list of identities, which could be used for BEGIN and COMMIT (and possibly all other queries)
2. Implement

Marking bug as a Feature Request. Passing to Oleg

Changed in percona-server:
status: Confirmed → Triaged
importance: Undecided → Wishlist
assignee: Valentine Gostev (longbow) → Oleg Tsarev (tsarev)
Stewart Smith (stewart) on 2012-03-27
Changed in percona-server:
assignee: Oleg Tsarev (tsarev) → nobody
Stewart Smith (stewart) on 2012-12-12
tags: added: slow-extended

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers