In MariaDB (as well as in MySQL 8), we have no wait event instrumentation for record locks and for table locks.

As such, table and row lock waits are reported incorrectly as "wait/io/table/sql/handler".

This makes performance troubleshooting impossible for customers.

The expected outcome is to create 2 more instruments: row lock and table lock. These 2 types of lock waits can then be measured inside performance_schema. Being able to separate lock waits from IO waits will aid performance tuning, because reducing them will require difference approaches.

Table and row lock instrumentation maybe too resource intensive. In the meantime, the fix in https://github.com/MariaDB/server/pull/2206 marks this kind of wait as wait/lock/table/sql/handler

Steps to reproduce:

1. Add the following line into my.cnf before instance start

   performance_schema=ON

2. Run the following to set up all consumers and instrumentation

   UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';

   UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';

   set global innodb_lock_wait_timeout=7200;

3. Set up test table

   create database test;

   use test

   create table t1 (id1 int(11), col1 varchar (200));

   insert into t1 values (1, 'aa');

   insert into t1 values (2, 'bb');

4. Run 3 concurrent sessions to observe the bug
   1. Session 1

      start transaction;

      select id1 from test.t1 where id1=1 for update;

      select sleep(100000);

   2. Session 2

      start transaction;

      select id1 from test.t1 where id1=1 for update;

   3. Session 3

      MariaDB [test]> select * from performance_schema.threads where type = 'foreground'\G

      *************************** Only relevant row displayed below ***************************

      THREAD_ID: 12

      NAME: thread/sql/one_connection

      TYPE: FOREGROUND

      PROCESSLIST_ID: 4

      PROCESSLIST_USER: haidong

      PROCESSLIST_HOST: NULL

      PROCESSLIST_DB: NULL

      PROCESSLIST_COMMAND: Query

      PROCESSLIST_TIME: 9

      PROCESSLIST_STATE: Sending data

      PROCESSLIST_INFO: select id1 from test.t1 where id1=1 for update

      PARENT_THREAD_ID: 1

      ROLE: NULL

      INSTRUMENTED: YES

      HISTORY: YES

      CONNECTION_TYPE: Socket

      THREAD_OS_ID: 22508

      MariaDB [test]> select * from performance_schema.events_waits_current where thread_id=12\G

      *************************** 1. row ***************************

      THREAD_ID: 12

      EVENT_ID: 236

      END_EVENT_ID: NULL

      EVENT_NAME: wait/io/table/sql/handler <<<Incorrect instrument name

      SOURCE:

      TIMER_START: 767747433709600

      TIMER_END: 791300625915200

      TIMER_WAIT: 23553192205600

      SPINS: NULL

      OBJECT_SCHEMA: test

      OBJECT_NAME: t1

      INDEX_NAME: NULL

      OBJECT_TYPE: TABLE

      OBJECT_INSTANCE_BEGIN: 139905003640240

      NESTING_EVENT_ID: 235

      NESTING_EVENT_TYPE: STAGE

      OPERATION: fetch

      NUMBER_OF_BYTES: 47

      FLAGS: NULL

      1 row in set (0.001 sec)

I'm working on a fix.