TL;DR

MySQL 8.0.20のInnoDBにおいては row->lock_trx_immutable_id ":" row->lock_space ":" row->lock_page ":" row->lock_rec ":" row->lock_immutable_id らしい
- https://github.com/mysql/mysql-server/blob/mysql-8.0.20/storage/innobase/trx/trx0i_s.cc#L1134-L1170
- trx0 i_s .ccにつながってるけどperformance_schemaからもこれを呼んでる
ちなみにこのENGINE_LOCK_ID(実体は pk_pos_data_lock::m_engine_lock_id ?)を真面目に実装しているのはInnoDBだけっぽく見える
- NDBCLUSTERは読んでない

そう思って見てみると、行単位でどこのページに乗っかってるのかとか調べられたりするかなあと思ったり思わなかったり。

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FR
OM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 166      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 166      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 166      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 166      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 166      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 166      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 166      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

mysql80 19> OPTIMIZE TABLE t1; -- これで↓のspace_idが変わった

mysql80 18> SELECT SUBSTRING_INDEX(engine_lock_id, ':', 1) AS lock_trx_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 2), ':', -1) AS space_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 3), ':', -1) AS page_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1) AS record_id, SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 5), ':', -1) AS lock_id, lock_data FROM data_locks WHERE lock_type = 'RECORD';
+-----------------+----------+---------+-----------+-----------------+------------------------+
| lock_trx_id     | space_id | page_id | record_id | lock_id         | lock_data              |
+-----------------+----------+---------+-----------+-----------------+------------------------+
| 140293334355320 | 167      | 4       | 1         | 140293227750848 | supremum pseudo-record |
| 140293334355320 | 167      | 4       | 2         | 140293227750848 | 1                      |
| 140293334355320 | 167      | 4       | 3         | 140293227750848 | 2                      |
| 140293334355320 | 167      | 4       | 4         | 140293227750848 | 3                      |
| 140293334355320 | 167      | 4       | 5         | 140293227750848 | 4                      |
| 140293334355320 | 167      | 4       | 6         | 140293227750848 | 5                      |
| 140293334355320 | 167      | 4       | 7         | 140293227750848 | 6                      |
+-----------------+----------+---------+-----------+-----------------+------------------------+
7 rows in set (0.00 sec)

↓i_sほにゃららのくせにp_sから呼ばれている図

(gdb) bt
+bt
#0  trx_i_s_create_lock_id (row=row@entry=0x7f4918052850,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/trx/trx0i_s.cc:1154
#1  0x000000000205a373 in print_record_lock_id (lock=lock@entry=0x7f4914013dd0, heap_no=heap_no@entry=2,
    lock_id=lock_id@entry=0x7f4918052970 "139951965544648:1248:139951844968944", lock_id_size=lock_id_size@entry=105)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:519
#2  0x000000000205ac5a in Innodb_data_lock_iterator::scan_trx (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, trx=trx@entry=0x7f491b3120c8,
    with_filter=with_filter@entry=false, filter_lock_immutable_id=filter_lock_immutable_id@entry=0, filter_heap_id=0)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:804
#3  0x000000000205ae8f in Innodb_data_lock_iterator::scan_trx_list (this=this@entry=0x7f48c400ff40,
    container=container@entry=0x7f48c42ca828, with_lock_data=with_lock_data@entry=true, read_write=read_write@entry=true,
    trx_list=<optimized out>) at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:684
#4  0x000000000205b76a in scan (with_lock_data=true, container=0x7f48c42ca828, this=0x7f48c400ff40)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:593
#5  Innodb_data_lock_iterator::scan (this=0x7f48c400ff40, container=0x7f48c42ca828, with_lock_data=<optimized out>)
    at /home/yoku0825/mysql-8.0.20/storage/innobase/handler/p_s.cc:572
#6  0x000000000249252b in table_data_locks::rnd_next() () at /home/yoku0825/mysql-8.0.20/storage/perfschema/table_data_locks.cc:191
#7  0x000000000243fc6e in ha_perfschema::rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/storage/perfschema/ha_perfschema.cc:1641
#8  0x00000000010df114 in handler::ha_rnd_next (this=0x7f48c42bf838, buf=0x7f48c42d8e38 "\377\377")
    at /home/yoku0825/mysql-8.0.20/sql/handler.cc:2966
#9  0x0000000000e0d72d in TableScanIterator::Read (this=0x7f48c42d8390) at /home/yoku0825/mysql-8.0.20/sql/row_iterator.h:275
#10 0x0000000000f702b3 in SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1183
#11 0x0000000000f7047c in SELECT_LEX_UNIT::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_union.cc:1235
#12 0x0000000000f0245b in Sql_cmd_dml::execute_inner(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:945
#13 0x0000000000f0c449 in Sql_cmd_dml::execute(THD*) () at /home/yoku0825/mysql-8.0.20/sql/sql_select.cc:725
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
#15 0x0000000000eb8378 in mysql_parse (thd=thd@entry=0x7f48c4010140, parser_state=parser_state@entry=0x7f49180544d0)
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:5306
#16 0x0000000000eba4d5 in dispatch_command(THD*, COM_DATA const*, enum_server_command) ()
    at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1776
#17 0x0000000000ebb314 in do_command (thd=thd@entry=0x7f48c4010140) at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:1274
#18 0x0000000000fccc70 in handle_connection (arg=arg@entry=0x757af30)
    at /home/yoku0825/mysql-8.0.20/sql/conn_handler/connection_handler_per_thread.cc:302
#19 0x000000000244315c in pfs_spawn_thread (arg=0x55f8070) at /home/yoku0825/mysql-8.0.20/storage/perfschema/pfs.cc:2854
#20 0x00007f49276caea5 in start_thread (arg=0x7f4918055700) at pthread_create.c:307
#21 0x00007f49258538dd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) frame 14
+frame 14
#14 0x0000000000eb6435 in mysql_execute_command(THD*, bool) () at /home/yoku0825/mysql-8.0.20/sql/sql_parse.cc:4489
4489          res = lex->m_sql_cmd->execute(thd);
(gdb) p thd->m_query_string
+p thd->m_query_string
$3 = {
  str = 0x7f48c42d4608 "SELECT * FROM performance_schema.data_locks",
  length = 43
}

日々の覚書

GA

2020/06/29

performance_schema.data_locks.ENGINE_LOCK_ID is 何

TL;DR

0 件のコメント :

コメントを投稿