Shulou(Shulou.com)05/31 Report--

This article is about how to find uncommitted transaction information in MySQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Mysql > select connection_id () from dual;+-+ | connection_id () | +-+ | 6 | +-+ 1 row in set (0.00 sec) mysql > set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql > delete from kkk where id = 1 sec query OK, 1 row affected (0.00 sec) mysql >

In another session (connection), query the details of the uncommitted transaction for more than 10 seconds:

SELECT t.trx_mysql_thread_id, t.trx_state, t.trx_tables_in_use, t.trx_tables_locked, t.trx_query, t.trx_rows_locked, t.trx_rows_modified, t.trx_lock_structs, t.trx_started, t.trx_isolation_level, p.time, p.user, p.host, p.db P.commandFROM information_schema.innodb_trx t INNER JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id WHERE t.trx_state = 'RUNNING' AND p.time > 10 AND p.command =' Sleep'\ G

As shown in the screenshot above, trx_query is null. It is almost impossible to find SQL statements for uncommitted transactions, and the information about transactions within MySQL is not very detailed, or even a little concise. I can't even locate a lock on that table. Only trx_row_locked, trx_row_modified, trx_started and other information can be seen. The same is true with show engine innodb status, where you can only see some basic information

Mysql > show engine innodb status;---TRANSACTION 1282583, ACTIVE 11937 sec2 lock struct (s), heap size 360,8 row lock (s), undo log entries 1MySQL thread id 6, OS thread handle 0x7f8da2de3700, query id 190localhost root

If an uncommitted transaction blocks other sessions, it is possible (only a possibility exists, and many scenarios cannot find the relevant SQL of the committed transaction) to find the SQL executed by the uncommitted transaction

As shown in the following test, the delete operation was performed in the session (connection connection_id=11), but the transaction was not committed

Mysql > set session autocommit=0;Query OK, 0 rows affected (0.00 sec) mysql > select connection_id (); +-+ | connection_id () | +-+ | 11 | +-+ 1 row in set (0.01 sec) mysql > delete from kkk where id=1;Query OK, 1 row affected (0.00 sec) mysql >

Another session (connection) performs an operation to update the record. At this point, SQL will be blocked.

Mysql > select connection_id (); +-+ | connection_id () | +-+ | 13 | +-+ 1 row in set (0.00 sec) mysql > mysql > update kkk set id=100 where id=1

In another session, execute the following SQL to find the last executed SQL of the uncommitted transaction.

Mysql > SELECT r.trx_id waiting_trx_id,-> r.trx_mysql_thread_id waiting_thread,-> r.trx_query waiting_query,-> b.trx_id blocking_trx_id,-> b.trx_mysql_thread_id blocking_thread -> b.trx_query blocking_query-> FROM information_schema.innodb_lock_waits w-> INNER JOIN information_schema.innodb_trx b-> ON b.trx_id = w.blocking_trx_id-> INNER JOIN information_schema.innodb_trx r-> ON r.trx_id = w.requesting_trx_id +- -- + | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | +-- +-- -+-+ | 2830 | 13 | update kkk set id=100 where id=1 | 2825 | 11 | NULL | +-+ -+ 1 row in set (0.00 sec) mysql > SELECT a.sql_text -> c.id,-> d.trx_started-> FROM performance_schema.events_statements_current a-> join performance_schema.threads b-> ON a.thread_id = b.thread_id-> join information_schema.processlist c-> ON b.processlist_id = c.id-> join information_schema.innodb_trx d-> ON c.id = d.trx_mysql_thread_id-> where c.id=11-> ORDER BY d.trx_started\ G * * 1. Row * * sql_text: delete from kkk where id = 1 id: 11trx_started: 2019-06-12 23 row 3613 row in set (0.03 sec) ERROR: No query specified mysql >

The above is how to find out the uncommitted transaction information in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.