MySQL Bugs: #85927: data_locks table does not provide any hint that lock is "insert intention" one

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:	None
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

Description:
Unfortunately, data_locks table (unlike SHOW ENGINE INNODB STATUS output, for example) has no indication that lock waiting is actually an insert intention lock.

How to repeat:
Use terst case from Bug #85749 and, while this statement hangs:

insert into t values(6,8);

being blocked, check the content of data_locks table:

mysql> select * from performance_schema.data_locks\G                            *************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1560:75
ENGINE_TRANSACTION_ID: 1560
            THREAD_ID: 35
             EVENT_ID: 7
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139693610016200
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1560:62:6:4
ENGINE_TRANSACTION_ID: 1560
            THREAD_ID: 35
             EVENT_ID: 8
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_c
OBJECT_INSTANCE_BEGIN: 139693610013600
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: WAITING
            LOCK_DATA: 10, 3
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1554:75
ENGINE_TRANSACTION_ID: 1554
            THREAD_ID: 34
             EVENT_ID: 13
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139693610010280
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1554:62:6:3
ENGINE_TRANSACTION_ID: 1554
            THREAD_ID: 34
             EVENT_ID: 13
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_c
OBJECT_INSTANCE_BEGIN: 139693610007400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 5, 2
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1554:62:5:3
ENGINE_TRANSACTION_ID: 1554
            THREAD_ID: 34
             EVENT_ID: 13
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 139693610007744
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 1554:62:6:4
ENGINE_TRANSACTION_ID: 1554
            THREAD_ID: 34
             EVENT_ID: 13
        OBJECT_SCHEMA: test
          OBJECT_NAME: t
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: idx_c
OBJECT_INSTANCE_BEGIN: 139693610008088
            LOCK_TYPE: RECORD
            LOCK_MODE: X,GAP
          LOCK_STATUS: GRANTED
            LOCK_DATA: 10, 3
6 rows in set (0.00 sec)

None of the columns in the table shows that in row #2 above we are waiting for insert intention lock. INNODB STATUS shows this clearly:

------------
TRANSACTIONS
------------
Trx id counter 1561
Purge done for trx's n:o < 1559 undo n:o < 0 state: running but idle
History list length 6
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 1560, ACTIVE 130 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 139693343512320, query id 28 localhost root update
insert into t values(6,8)
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 62 page no 6 n bits 72 index idx_c of table `test`.`t` trx id 1560 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000003; asc     ;;
...

Suggested fix:
Please, add some column or value to show that the lock is insert intention one.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

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"},
};

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