Bug #84610 Provide more information on various table locks

• Submitted: 23 Jan 2017 12:51
• Reporter: Sveta Smirnova (OCA)
• Status: Open
• Category: MySQL Server: Performance Schema
• Severity: S4 (Feature request)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any

[23 Jan 2017 12:51] Sveta Smirnova

Description:
Experiencing with table_handles table I found what I can get output 

mysql> select * from table_handles where object_name='t'\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140108477034256
      OWNER_THREAD_ID: 23
       OWNER_EVENT_ID: 3788
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: READ EXTERNAL
1 row in set (0,00 sec)

at least for queries like:

lock table t read;
select * from t [lock in share mode];
select * from t where i [=,in,<,>] ...

And

mysql> select * from table_handles where object_name='t'\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
OBJECT_INSTANCE_BEGIN: 140108477034256
      OWNER_THREAD_ID: 23
       OWNER_EVENT_ID: 4379
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: WRITE EXTERNAL
1 row in set (0,00 sec)

At least for:

lock table t write;
select * from t for update;
update t set i=i+sleep(i) where i [=,in,<,>] ...

There are, probably, other cases.

But they all are quite different kinds of locks.

How to repeat:
See description

Suggested fix:
Provide way for DBA who analyzes load using Performance Schema a way to distinguish these kinds of locks.