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.