Bug #95070 INSERT .. VALUES ( .., (SELECT ..), ..) takes shared lock with READ-COMMITTED

• Submitted: 20 Apr 2019 14:07
• Modified: 2 May 2019 13:09
• Reporter: Seunguck Lee
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.15
• OS: Any
• CPU Architecture: x86

[20 Apr 2019 14:07] Seunguck Lee

Description:
Manual(https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html) describe that "INSERT .. SELECT .. " does not takes shared lock on select table if READ-COMMITTED and binlog_format is ROW. "INSERT .. SELECT .." statement is work as described in manual. But "INSERT .. VALUES (.., (SELECT ..), ..)" statement still takes shared lock on rows of select table.

Actually manual does not mention(I can't find) about "INSERT .. VALUES (.., (SELECT ..), ..)" syntax. So I am not sure this case is bug or not. 

SESSION-1>
    set session transaction_isolation='READ-COMMITTED';
    set binlog_format='ROW';
    
    begin;
    insert into tab values (3, 100, (select t1.balance from tab t1 order by t1.id desc limit 1)+100);
        Query OK, 1 row affected (0.00 sec)
        Records: 1  Duplicates: 0  Warnings: 0

SESSION-2>
    set session transaction_isolation='READ-COMMITTED';
    set binlog_format='ROW';
    
    begin;
    insert into tab values (4, 100, (select t1.balance from tab t1 order by t1.id desc limit 1)+100);
       ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

How to repeat:
create table tab (
  id int not null primary key,
  amount int not null,
  balance int not null
);

insert into tab values (1, 100, 100);
insert into tab values (2, 400, 500);

SESSION-1>
    set session transaction_isolation='READ-COMMITTED';
    set binlog_format='ROW';
    
    begin;
    insert into tab values (3, 100, (select t1.balance from tab t1 order by t1.id desc limit 1)+100);
        Query OK, 1 row affected (0.00 sec)
        Records: 1  Duplicates: 0  Warnings: 0

SESSION-2>
    set session transaction_isolation='READ-COMMITTED';
    set binlog_format='ROW';
    
    begin;
    insert into tab values (4, 100, (select t1.balance from tab t1 order by t1.id desc limit 1)+100);
       ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Suggested fix:
Fix not to takes shared lock on select table or Add this use case also in manual.

[24 Apr 2019 13:31] MySQL Verification Team

Hi Mr. Lee,

It is difficult to understand what makes you believe that the locks which cause the timeout are on the table that you select from (t1) as opposed to the table you insert to (tab).

We have tried to run the same queries, assuming that t1 is prepared by :

   create table t1 (id int not null primary key, balance int not null);

   insert into t1 values (1,10),(2,-10),(3,20),(4,50);

and the locks just after the first session executes are:

mysql> select engine_transaction_id, object_name, index_name, lock_type,
lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_transaction_id | object_name | index_name | lock_type |
lock_mode     | lock_status | lock_data |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 21800 | tab         | NULL       | TABLE     | IS     
     | GRANTED     | NULL      |
|                 21800 | tab         | PRIMARY    | RECORD    |
S,REC_NOT_GAP | GRANTED     | 2         |
|                 21800 | tab         | NULL       | TABLE     | IX     
     | GRANTED     | NULL      |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)

and after the second session starts waiting:

mysql> select engine_transaction_id, object_name, index_name, lock_type,
lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
| engine_transaction_id | object_name | index_name | lock_type |
lock_mode     | lock_status | lock_data |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
|                 21800 | tab         | NULL       | TABLE     | IS     
     | GRANTED     | NULL      |
|                 21800 | tab         | PRIMARY    | RECORD    |
S,REC_NOT_GAP | GRANTED     | 2         |
|                 21800 | tab         | NULL       | TABLE     | IX     
     | GRANTED     | NULL      |
|                 21800 | tab         | PRIMARY    | RECORD    |
X,REC_NOT_GAP | GRANTED     | 3         |
|       283279405915496 | tab         | NULL       | TABLE     | IS     
     | GRANTED     | NULL      |
|       283279405915496 | tab         | PRIMARY    | RECORD    |
S,REC_NOT_GAP | WAITING     | 3         |
+-----------------------+-------------+------------+-----------+---------------+-------------+-----------+
6 rows in set (0.00 sec)

Hence, please, let us know why do you  believe this is a lock on t1 that you are waiting for ???

Many thanks in advance.

[26 Apr 2019 2:10] Seunguck Lee

Hi.

>> It is difficult to understand what makes you believe that the locks which cause the timeout are on the table that you select from (t1) as opposed to the table you insert to (tab).

Sorry. it's my mistake. 
There's only one table is used, just change the table name from 't1' to 'tab'.

[26 Apr 2019 2:15] Seunguck Lee

Sorry for confused comment. (Just forget about my previous comment)

Actually no problem on my repeat scenario.
"t1" you have mentioned is just alias for "tab" table in subquery.

[2 May 2019 13:09] MySQL Verification Team

Thank you for your feedback.