Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27078

Add STATEMENT_ID column in the Performance Schema statement event tables

    XMLWordPrintable

    Details

      Description

      Dear Team,
      when I do some research on deadlock, I can't easily make out the relation of the info in table events_statements_history_long and detected deadlock information.

      (dbausr@localhost) [performance_schema] 14:44:29> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, EVENT_NAME, LOCK_TIME, SQL_TEXT, ROWS_AFFECTED, ROWS_SENT FROM events_statements_history_long WHERE THREAD_ID = 8680;
      +-----------+----------+--------------+-----------------------------+-----------+-------------------------------------------------------------+---------------+-----------+
      | THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME                  | LOCK_TIME | SQL_TEXT                                                    | ROWS_AFFECTED | ROWS_SENT |
      +-----------+----------+--------------+-----------------------------+-----------+-------------------------------------------------------------+---------------+-----------+
      ...
      |      8680 |       98 |          123 | statement/sql/select        | 149000000 | select c1, c2 from t_deadlock where c1=3 lock in share mode |             0 |         1 |
      |      8680 |      125 |          150 | statement/sql/update        | 281000000 | update t_deadlock set c2='session2: share lock' where c1=3  |             0 |         0 |
       
      2021-11-18 10:24:08 4296 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
      2021-11-18 10:24:08 4296 [Note] InnoDB:
      *** (1) TRANSACTION:
      TRANSACTION 270, ACTIVE 31 sec starting index read mysql tables in use 1, locked 1
      LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
      MariaDB thread id 4296, OS thread handle 139621621896960, query id 5048 localhost dbausr Updating 
      update t_deadlock set c2='session2: share lock' where c1=3
      2021-11-18 10:24:08 4296 [Note] InnoDB: *** WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 17 page no 3 n bits 8 index PRIMARY of table `sbtest`.`t_deadlock` trx id 270 lock_mode X locks rec but not gap waiting
      Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
      

      And I noticed that, from MySQL 8.0.14, there's a new feature descript as below:
      https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-14.html
      Performance Schema Notes
          The Performance Schema statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) now have a STATEMENT_ID column that indicates the query ID maintained by the server at the SQL level. Column values are unique for the server instance because they are generated using a global counter that is incremented atomically. 

      https://dev.mysql.com/worklog/task/?id=12165
      WL#12165: PERFORMANCE_SCHEMA, CAPTURE QUERY_ID
      https://github.com/mysql/mysql-server/commit/3c597abd796ff600c136b1737f828ed800e08eeb

      Would we merge this feature into the latest MariaDB version?

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            shawn2016 Shawn Yan
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.