What is meant by Transaction not started in SHOW ENGINE INNODB STATUS?

Question

Here are the few lines of response of 'SHOW ENGINE INNODB STATUS;'

---

TRANSACTIONS
------------
Trx id counter 58EC54C6
Purge done for trx's n:o < 58EC54C3 undo n:o < 0
History list length 2420
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 58EC51E6, not started
MySQL thread id 520131, OS thread handle 0x7f0db930e700, query id 24011015 108.89.56.87 xyz
---TRANSACTION 58EC527E, not started
MySQL thread id 520061, OS thread handle 0x7f0dbb596700, query id 24011370 108.89.56.87 xyz
---TRANSACTION 58EC53AC, not started
MySQL thread id 520065, OS thread handle 0x7f0dbb28a700, query id 24012094 108.89.56.87 xyz
---TRANSACTION 58EC50CE, not started
MySQL thread id 520109, OS thread handle 0x7f0dbba69700, query id 24010431 108.89.56.87 xyz    ---TRANSACTION 58EC51E8, not started
MySQL thread id 520123, OS thread handle 0x7f0dbb4d3700, query id 24011016 108.89.56.87 xyz    ---TRANSACTION 58EC51D0, not started
MySQL thread id 520072, OS thread handle 0x7f0db865c700, query id 24010944 108.89.56.87 xyz
---TRANSACTION 58EC5184, not started
MySQL thread id 520058, OS thread handle 0x7f0db8c74700, query id 24010807 108.89.56.87 xyz

I want to know what this 'Transaction transId, not started' means? Does any query failed to execute?

I am using innodb. also I am not manually starting transaction. It is considering automatically transaction because I am issuing update query on a table having bulk data. and ya there are getting hundreads of mysql update query on this single table in 5-10 mins.

Please let me know what exactly 'Transaction not started' means? Is it showing failed query or just a normal log?

Thanks in Advance!

Answer 1

The first time a thread (~connection) needs to lock some data during a transaction, a transaction "object" (~context) is created. The transaction "object" is not destroyed at the end of the transaction, it would be reused in case a new transaction is started by the same thread.

Each transaction showing as not started is a transaction "object" owned by a thread which is not currently inside a transaction (more precisely, which does not need to maintain a separate context for a transaction).

The thread id is the one that shows in the Idcolumn when you issue a SHOW PROCESSLIST.

This is nothing to worry about, actually this is normal behaviour.

Answer 2

I found this on Internet:

When does Innodb Start Transaction ?

January 11, 2011 By Peter Zaitsev

9 Comments inShare1

When does Innodb Start Transaction ? The answer looks obvious – when you issue “BEGIN” command. This is however wrong answer from engine point of you. Run “SHOW INNODB STATUS” and you will see “not started” status in transaction list.

from here

For each of connections for MySQL there will be ether not started state if there is no active Innodb transaction for this connection, or ACTIVE if transaction is active. Note transaction can be active even if connection is in "Sleep" stage - if it is multiple statement transaction. Innodb also will print OS thread_id and process id which may be helpful if you would like to use gdb to connect to running mysqld for troubleshooting purposes and similar things. Also transaction status is reported which is basically what transaction is doing it can be "fetching rows", "updating" and couple of other values.

the source