why the event is 'wait/io/table/sql/handler' when waiting for a row lock?

Question

I made session B waiting for row lock hold by session A , however I saw the waiting is 'wait/io/table/sql/handler' instead of I expected row lock such as 'wait/lock .....',the following are the steps to duplicate the issue .

MySQL version is '5.7.18-debug';

step 1),enable the performance schema

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

step 2 ,create the table and insert a row .

CREATE TABLE t (i INT) ENGINE = InnoDB;
INSERT INTO t (i) VALUES(1);

step 3, start a new session A hold a row lock ,and doesn't release it .

 SELECT connection_id() ;
 START TRANSACTION;
 DELETE FROM t WHERE i = 1;

step 4, start a new session B and try to get a row lock, however blocked by session A

 SELECT connection_id() ;
 START TRANSACTION;
 SELECT * FROM t  WHERE i = 1 LOCK IN SHARE MODE;

step 5, then query the performance schema below , however from the PS tables I saw the 'wait/io/table/sql/handler' for session B from table events_waits_current , which is meaning waiting for the IO operation , doesn't it should be wait for a row lock ?

SELECT * FROM performance_schema.events_waits_current;
SELECT * FROM performance_schema.events_stages_current;
SELECT * FROM performance_schema.events_statements_current;

Answer 1

Yeah, this doesn't make any sense. I've reproduced this as well as other issues with wait/io/table/sql/handler. TL;DR is that wait events are of questionable use on MySQL.

The wait wait/io/table/sql/handler shows up for

I/O - reading data from disk

CPU - reading data blocks in the cache

LOCKs - two users trying to update the same row for example.

What's the point of a wait event if it covers things as broad as I/O, CPU and Locks?

Amazon Aurora has at least patched up the locking part by creating a new wait event synch/mutex/innodb/aurora_lock_thread_slot_futex see https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-waits.waitsynch.html

I spent some time late last year going through some of the MySQL code and was surprised to see this wait "wait/io/table/sql/handler" wrapped around buffer reads from the cache which is simply a CPU state. It is a bit ironic that Oracle manages MySQL and Oracle is that database the blazed the trail on wait events which work superbly in Oracle yet are so broken on MySQL.