• 周三. 2月 28th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL finds information about uncommitted transactions

[db:作者]

1月 3, 2022

Catalog

One 、processlist Uncommitted transactions in

Two 、information_schema.innodb_trx Uncommitted transactions in

3、 … and 、performance_schema.events_statements_current Uncommitted transactions in

Reference resources :


        We often encounter such situations , A transaction is not committed after execution , And then one more DDL and DML operation , Lead to the following session Or in waiting for metadata lock, Or lock wait timeout . At this time, we can only find the uncommitted transaction id and session id, But it’s usually in sleep state , It’s not easy to analyze what the transaction content is , So it’s usually rude kill This session Solve the problem after , But application developers often can’t find out which transaction caused it , When there are problems later, repeat kill.

One 、processlist Uncommitted transactions in

        For a transaction executed but uncommitted , Cannot be in show processlist Find the information in the output of :

-- session 1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t1(a int);
Query OK, 0 rows affected (0.45 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select connection_id() from dual;
+-----------------+
| connection_id() |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)
-- session 2
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  6 | root | localhost | test | Sleep   |   44 |       | NULL             |
|  7 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

        You can see , Uncommitted Command by Sleep,State It’s empty ,Info by NULL.

Two 、information_schema.innodb_trx Uncommitted transactions in

        Again ,information_schema.innodb_trx.trx_query Also for the NULL, Unable to provide… For uncommitted transactions SQL sentence :

mysql> select * from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 4632
                 trx_state: RUNNING
               trx_started: 2020-03-28 07:18:32
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 6
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 1
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

3、 … and 、performance_schema.events_statements_current Uncommitted transactions in

        By looking at performance_schema.events_statements_current You can see every one of them session Ongoing sql, Even if it’s still done , Just didn’t submit :

mysql> select * from performance_schema.events_statements_current where sql_text not like 'select * from performance_schema.events_statements_current%'\G
*************************** 1. row ***************************
              THREAD_ID: 25
               EVENT_ID: 9
           END_EVENT_ID: 9
             EVENT_NAME: statement/sql/select
                 SOURCE: mysqld.cc:937
            TIMER_START: 1897350780117784000
              TIMER_END: 1897350780310674000
             TIMER_WAIT: 192890000
              LOCK_TIME: 0
               SQL_TEXT: select connection_id() from dual
                 DIGEST: e5d97521478adc05b74560b51be5d6f7
            DIGEST_TEXT: SELECT `connection_id` ( ) FROM DUAL 
         CURRENT_SCHEMA: test
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 1
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
1 row in set (0.00 sec)

        There is a flaw in the scheme : A transaction may have a group of sql form , This method can only see what the last execution of the transaction is SQL, Can’t see all . Can pass performance_schema.threads Table to associate , take information_schema.processlist and performance_schema.events_statements_current One by one :

mysql> select t1.id, t2.thread_id, t3.sql_text
    ->   from information_schema.processlist t1, 
    ->        performance_schema.threads t2,
    ->        performance_schema.events_statements_current t3
    ->  where t1.id=6
    ->    and t1.id=t2.processlist_id 
    ->    and t2.thread_id = t3.thread_id\G
*************************** 1. row ***************************
       id: 6
thread_id: 25
 sql_text: select connection_id() from dual
1 row in set (0.00 sec)

Reference resources :

  • MySQL How to locate uncommitted transaction execution SQL sentence ?
  • MySQL How to find out uncommitted transaction information
《MySQL finds information about uncommitted transactions》有4,350个想法
  1. brillx регистрация
    Brillx
    Как никогда прежде, в 2023 году Brillx Казино предоставляет широкий выбор увлекательных игровых автоматов, которые подарят вам незабываемые моменты радости и адреналина. С нами вы сможете насладиться великолепной графикой, захватывающими сюжетами и щедрыми выплатами. Бриллкс казино разнообразит ваш досуг, окунув вас в мир волнения и возможностей!В 2023 году Brillx предлагает совершенно новые уровни азарта. Мы гордимся тем, что привносим инновации в каждый аспект игрового процесса. Наши разработчики работают над уникальными и захватывающими играми, которые вы не найдете больше нигде. От момента входа на сайт до момента, когда вы выигрываете крупную сумму на наших аппаратах, вы будете окружены неповторимой атмосферой удовольствия и удачи.