Not the answer you need?
Register and ask your own question!

how to find out the table which is locking

elison2000elison2000 EntrantInactive User Role Beginner
hi,i find some uncommit transactions on table innodb_trx,how to find out the table which is locking?

[email protected])[(none)]>select 'innodb_trx',
-> trx_id,
-> trx_mysql_thread_id thread_id,
-> trx_state,
-> timestampdiff(second, trx_started, now()) trx_elap_sec,
-> timestampdiff(second, trx_wait_started, now()) wait_sec,
-> trx_rows_locked rows_locked,
-> trx_rows_modified rows_modified,
-> substring(trx_query, 1, 60)
-> from information_schema.innodb_trx
-> order by trx_elap_sec desc, wait_sec desc;
+
+
+
+
+
+
+
+
+
+
| innodb_trx | trx_id | thread_id | trx_state | trx_elap_sec | wait_sec | rows_locked | rows_modified | substring(trx_query, 1, 60) |
+
+
+
+
+
+
+
+
+
+
| innodb_trx | 8A006C5D | 10088406 | RUNNING | 100949 | NULL | 2 | 7 | NULL |
+
+
+
+
+
+
+
+
+
+
1 row in set (0.00 sec)

Comments

  • elison2000elison2000 Entrant Inactive User Role Beginner
    i find below some transaction is executing from information_schema.innodb_trx ,i want to tuning that,but i don't find the sql and table information,who can help me?

    2015-12-16 10:00:42
    innodb_trx 8A006C5D 10088406 RUNNING 80169 NULL 2 7 NULL
    innodb_trx 8AF4D6D4 10119037 RUNNING 10 NULL 0 0 NULL
    innodb_trx 8AF4F536 10143800 RUNNING 8 NULL 0 0 NULL
    innodb_trx 8AF532C3 10122351 RUNNING 5 NULL 0 0 NULL
    innodb_trx 8AF527F9 10119038 RUNNING 5 NULL 0 0 NULL
    innodb_trx 8AF532F9 10119034 RUNNING 5 NULL 2 0 NULL
    innodb_trx 8AF532B6 10122352 RUNNING 5 NULL 0 0 NULL
    innodb_trx 8AF532B7 10143801 RUNNING 5 NULL 2 0 NULL
    innodb_trx 8AF58175 10119043 RUNNING 1 NULL 0 0 NULL
    innodb_trx 8AF57880 10119036 RUNNING 1 NULL 0 0 NULL
    innodb_trx 8AF5789D 10245006 RUNNING 1 NULL 2 0 NULL
    innodb_trx 8AF5873C 10132546 RUNNING 0 NULL 0 0 SELECT COUNT(*) FROM ((SELECT F01, F02, F03, F04, F05, F06,
    innodb_trx 8AF5864C 10132503 RUNNING 0 NULL 0 0 SELECT * FROM ((SELECT F01, F02, F03, F04, F05, F06, F07, F0
    innodb_trx 8AF58672 10132542 RUNNING 0 NULL 0 0 SELECT * FROM ((SELECT F01, F02, F03, F04, F05, F06, F07, F0
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.