Bug #85927 | data_locks table does not provide any hint that lock is "insert intention" one | ||
---|---|---|---|
Submitted: | 13 Apr 2017 8:24 | Modified: | 10 Jun 2019 6:42 |
Reporter: | Valeriy Kravchuk | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
Version: | 8.0.1 | OS: | Any |
Assigned to: | Marc ALFF | CPU Architecture: | Any |
Tags: | data_locks, insert intention, performance_schema |
[13 Apr 2017 8:24]
Valeriy Kravchuk
[13 Apr 2017 10:05]
MySQL Verification Team
Hello Valeriy, Thank you for the report. Thanks, Umesh
[7 Jun 2019 14:45]
MySQL Verification Team
Hmm, have you tested recent versions? I saw this: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html "InnoDB: The list of permitted lock mode descriptors used by the LOCK_MODE column of the Performance Schema data_locks table was expanded to include REC_NOT_GAP, INSERT_INTENTION, PREDICATE, and PRDT_PAGE. REC_NOT_GAP indicates a record-only lock. INSERT_INTENTION indicates an insert intention lock. PREDICATE and PRDT_PAGE descriptors indicate a spatial index lock. (Bug #28176805)" ... /** Used by lock_get_mode_str to build a lock mode description */ static const std::map<uint, const char *> lock_constant_names{ {LOCK_GAP, "GAP"}, {LOCK_REC_NOT_GAP, "REC_NOT_GAP"}, {LOCK_INSERT_INTENTION, "INSERT_INTENTION"}, {LOCK_PREDICATE, "PREDICATE"}, {LOCK_PRDT_PAGE, "PRDT_PAGE"}, };
[10 Jun 2019 6:42]
Valeriy Kravchuk
Yes, this is fixed in 8.0.16 at least. With the same test case we see: mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068936:1062:140449792716920 ENGINE_TRANSACTION_ID: 2604 THREAD_ID: 48 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140449792716920 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068936:5:5:4:140449792713976 ENGINE_TRANSACTION_ID: 2604 THREAD_ID: 48 EVENT_ID: 12 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_c OBJECT_INSTANCE_BEGIN: 140449792713976 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: WAITING LOCK_DATA: 10, 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068032:1062:140449792710984 ENGINE_TRANSACTION_ID: 2599 THREAD_ID: 47 EVENT_ID: 15 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140449792710984 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068032:5:5:3:140449792708008 ENGINE_TRANSACTION_ID: 2599 THREAD_ID: 47 EVENT_ID: 15 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_c OBJECT_INSTANCE_BEGIN: 140449792708008 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 5, 2 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068032:5:4:3:140449792708352 ENGINE_TRANSACTION_ID: 2599 THREAD_ID: 47 EVENT_ID: 15 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140449792708352 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140449862068032:5:5:4:140449792708696 ENGINE_TRANSACTION_ID: 2599 THREAD_ID: 47 EVENT_ID: 15 OBJECT_SCHEMA: test OBJECT_NAME: t PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_c OBJECT_INSTANCE_BEGIN: 140449792708696 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 10, 3 6 rows in set (0.00 sec) mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.16 | +-----------+ 1 row in set (0.00 sec) We clearly see in row #2: LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: WAITING