MySQL Bugs: #79424: lock wait taken as io wait when using performance schema

Bug #79424	lock wait taken as io wait when using performance schema
Submitted:	26 Nov 2015 14:22	Modified:	15 May 2019 13:28
Reporter:	Rony Pan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.9	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	performance schema

Description:
I run a simple test when studying your performance schema.
For the first connection, I turned off autocommit and run query like:
select * from test_table where id = 1 for update;
I did not issue COMMIT;

then I use a second connection and run the following query:
update test_tabe shet content = 'test' where id = 1;

For sure the second UPDATE has to wait for the first query to release its lock

but when I check the events_waits_current table;
I found the waiting thread is waiting for wait/io/table/sql/handler

I used to be a DBA for DB2 z/OS, I'm prety sure such kind of waiting events shall be taken as lock waiting, at least it is no IO waiting

How to repeat:
just explain the phenomenon

Suggested fix:
change the waiting events to correct one

Hi Mr. Pan,

Thank you for your bug report.

I have just tested your test case with 5.7.26 and it works just fine. That UPDATE is still hanging while I am writing this reply to you.

Here is the proof from the InnoDB status:

------------
TRANSACTIONS
------------
Trx id counter 1984029
Purge done for trx's n:o < 1984026 undo n:o < 0 state: running but idle
History list length 9
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1984028, ACTIVE 20 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 6, OS thread handle 123145549975552, query id 39 localhost sinisa updating
update t1 set x=3 where id=2
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 467 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 1984028 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 0000001e4619; asc     F ;;
 2: len 7; hex 31000001f604d0; asc 1      ;;
 3: len 4; hex 80000005; asc     ;;

------------------
---TRANSACTION 1984027, ACTIVE 33 sec
2 lock struct(s), heap size 1160, 1 row lock(s)
MySQL thread id 5, OS thread handle 123145534136320, query id 41 localhost JohnnyD starting

Not a bug.