Bug #84609 Lock information in TABLE_HANDLES misplaced
Submitted: 23 Jan 2017 12:21 Modified: 23 Jan 2017 12:33
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7, 8.0, 5.7.17 OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[23 Jan 2017 12:21] Sveta Smirnova
Description:
User manual (https://dev.mysql.com/doc/refman/8.0/en/table-handles-table.html) says about which kind of locks should be put into INTERNAL_LOCK and EXTERNAL_LOCK fields of table table_handles: 

----<q>----
 INTERNAL_LOCK

The table lock used at the SQL level. The value is one of READ, READ WITH SHARED LOCKS, READ HIGH PRIORITY, READ NO INSERT, WRITE ALLOW WRITE, WRITE CONCURRENT INSERT, WRITE LOW PRIORITY, or WRITE. For information about these lock types, see the include/thr_lock.h source file.

EXTERNAL_LOCK

The table lock used at the storage engine level. The value is one of READ EXTERNAL or WRITE EXTERNAL. 
----</q>----

However it is possible to find SQL-level lock information in the EXTERNAL_LOCK field

How to repeat:
mysql1> lock table t1 read;
Query OK, 0 rows affected (0,00 sec)

mysql2> select * from table_handles\G
*************************** 1. row ***************************
          OBJECT_TYPE: TABLE
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
OBJECT_INSTANCE_BEGIN: 140156124286480
      OWNER_THREAD_ID: 43
       OWNER_EVENT_ID: 2918
        INTERNAL_LOCK: NULL
        EXTERNAL_LOCK: READ EXTERNAL
1 row in set (0,00 sec)

t1 is InnoDB table.

Suggested fix:
Either fix manual or code.
[23 Jan 2017 12:33] Umesh Shastry
Hello Sveta,

Thank you for the report.

Thanks,
Umesh