Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
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?