0

I have very Long Running Transactions in MySQL (RDS Aurora)

select * from information_schema.innodb_trx where trx_id = 45638005;
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id   | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 45638005 | RUNNING   | 2019-12-25 14:29:57 | NULL                  | NULL             |       1967 |                  20 | NULL      | NULL                |                 0 |                 0 |                2 |                   376 |               1 |              1965 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

My Application uses Apache Tomcat Connection Pool to get a Connection and Execute the Queries. I'm not explicitly starting any tranasction so I'm giving any COMMIT or ROLLBACK.

Can someone Explain what is the meaning of the above transaction, It shows transaction is running but trx_query is NULL

I checked the Queries executing using trx_mysql_thread_id in events_statements_history and events_statements_history_long and found there are multiple Queries. How is it possible that all the Queries are using Same Transaction, Even though i didn't use any Transactions ??

  • Show us the query. And it's SHOW CREATE TABLE. – Rick James Dec 27 '19 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.