MySQL Bugs: #72748: INSERT...SELECT fails to block concurrent inserts, results in additional records

Bug #72748	INSERT...SELECT fails to block concurrent inserts, results in additional records
Submitted:	26 May 2014 8:56	Modified:	11 Oct 2018 17:07
Reporter:	Peiran Song	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.6.17, 5.6.19, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a session doing a long INSERT... SELECT to a table with auto increment id column, a concurrent session doing simple INSERT to the same table be waiting on the auto_inc lock (innodb_autoinc_lock_mode=1), however, this feature seems to be broken in 5.6.17 and also resulted in additional rows inserted into the target table. 

How to repeat:
Prepare tables and data:
-----------------------
use test;
create table parent (id int, name varchar(20), primary key (id)) engine = innodb;
create table child (id int auto_increment, p_id int, primary key (id), key idx_p_id (p_id), constraint fk_p_id foreign key (p_id) references parent (id) ) engine = innodb;
insert into parent values (1, "foo"), (2,"blah");
insert into child (p_id) values (1);

for i in {1..20}; do echo $i; echo "insert into child (p_id) select t.p_id from child t" | mysql test; done

Check data:
-----------
mysql> select * from child where p_id = 2;
Empty set (0.00 sec)

mysql> select * from child order by id desc limit 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1310693 |    1 |
| 1310692 |    1 |
+---------+------+

Run the test:
-------------------

session1: 
echo “insert into child (p_id) select t.p_id from child t” | mysql test ;

session2: while session1 running
mysql> insert into child (p_id) value (2);

Session 2 should be blocked and waiting for session 1 to finish, however, it proceeds. When both session finished, there are two p_id=2 rows in the target table, one before the bulk insert and another one after the bulk insert. 

mysql> select * from child where p_id = 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1376221 |    2 |
| 2424798 |    2 |
+---------+------+

Suggested fix:
This is a regression.

I did not see the same problem on 5.5.37. With 5.5.37, subsequent INSERT waits on auto_inc lock as expected.

Thank you for the bug report. Please check with 5.6.19 I wasn't able to repeat
with source. Thanks.

Yes, I can reproduce it on 5.6.19.  I am running on a CentOS vm and installed mysql via yum repository. 

I tested twice. The first time, I started session2(simple insert) immediately after session1(INSERT..SELECT). And there was only one record with p_id=2. 

mysql> select * from child where p_id = 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1376221 |    2 |
+---------+------+

The second time, I waited for session1 (INSERT..SELECT) to run for a bit and then kicked in session2(simple insert). The result was like before, with session2's insert included in session1's select. 

mysql> select * from child where p_id = 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1376221 |    2 |
| 2424798 |    2 |
+---------+------+

Also, there is always a gap in the autoincrement column. It is minor though. 

mysql> select * from child where id >= 1310693 limit 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1310693 |    1 |
| 1376221 |    2 |
+---------+------+

Thank you for the report and feedback.
I'm able to see this with 5.6.21.

// 5.6.21

mysql>  select * from child where p_id = 2;
+---------+------+
| id      | p_id |
+---------+------+
| 1376221 |    2 |
| 2424798 |    2 |
+---------+------+
2 rows in set (0.00 sec)

mysql> select * from child where id >= 1310690 limit 10;
+---------+------+
| id      | p_id |
+---------+------+
| 1310690 |    1 |
| 1310691 |    1 |
| 1310692 |    1 |
| 1310693 |    1 |
| 1376221 |    2 |
| 1376222 |    1 |
| 1376223 |    1 |
| 1376224 |    1 |
| 1376225 |    1 |
| 1376226 |    1 |
+---------+------+
10 rows in set (0.00 sec)

mysql> show variables like 'version';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| version       | 5.6.21-enterprise-commercial-advanced-log |
+---------------+-------------------------------------------+

mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

Also, with 5.5.40 - Session 2 is blocked until Session 1 is completed and confirmed just one p_id=2 rows in the target table.

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.5.40 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> insert into child (p_id) value (2);
Query OK, 1 row affected (8.45 sec)

^^ This was blocked until concurrent insert is finished..

mysql> select * from child where p_id = 2;
+---------+------+
| id      | p_id |
+---------+------+
| 2490316 |    2 |
+---------+------+
1 row in set (0.00 sec)

^^ Just one row for p_id=2..

The auto inc gap which is mentioned in report is noticed in 5.6.21 and 5.5.40 as well.

[12 Aug 2014 0:12] Sunny Bains:

This is not a regression and as far as I can tell it is not a bug. I can reproduce the same behaviour in 5.5. too. If I do the following insert: echo "insert into child (p_id) value (2);" | mysql test while the select is executing in the following SQL echo "insert into child (p_id) select t.p_id from child t" | mysql test ; The insert of 2 will succeed and will be inserted into child. Why should the autoinc lock block an insert of p_id into child?

Note: tested with 5.5.40

Here is why: http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html

"
- innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

This is the default lock mode. In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement. This applies to all INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements. Only one statement holding the AUTO-INC lock can execute at a time.

With this lock mode, “simple inserts” (only) use a new locking model where a light-weight mutex is used during the allocation of auto-increment values, and no table-level AUTO-INC lock is used, unless an AUTO-INC lock is held by another transaction. If another transaction does hold an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it too were a “bulk insert”.

"

In my understanding following part in documentation may be not fully correct:

"hold it until the end of the statement."

It applies only to statements which do not know number of IDs to generate (e.g. LOAD DATA). Other statements are somewhat described earlier in that chapter:

"For INSERT statements where the number of rows to be inserted is known at the beginning of processing the statement, InnoDB quickly allocates the required number of auto-increment values without taking any lock".

Conceptually I don't see why simple insert should be blocked with innodb_autoinc_lock_mode = 1 when InnoDB knows how many numbers to generate.

Actually, this is truly not a bug.

In this autoinc-mode  the result might depend on which command started first, the one that uses table lock or the other one.

Andrii, 

For INSERT...SELECT, the number of rows to be inserted is not known at the beginning of processing. For safe replication and point-in-time recovery, it should block subsequent inserts. 

Sinisa,

The test case shows the sequency of execution. 

I highly recommend you both to run the test case. If needed, run it on 5.5 and 5.1 as well to observe the difference.

Peiran Song, could you repeat the problem when binary logging is enabled and row_format is not set to ROW ?

That is the only configuration when behavior may bring problems and for me session #2 is blocked for both STATEMENT and MIXED binlog_format.

For ROW format and without binlog session #2 is not blocked.

Server behavior looks correct and safe, just documentation may be incomplete.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I think this should be checked with recent MySQL 5.6.x. I see the same behavior on 5.6.22 and on 5.5.41 for similar cases: single row INSERT hangs waiting for AUTO_INC lock, as expected from the manual.

Like this:

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0x3ee0, query id 13 localhost ::1 root cleaning up
---TRANSACTION 69136, ACTIVE 17 sec setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 2, OS thread handle 0x2c10, query id 11 localhost ::1 root update
insert into t(val) values(100)
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`t` trx id 69136 lock mode AUTO-INC waiting
------------------
TABLE LOCK table `test`.`t` trx id 69136 lock mode AUTO-INC waiting
---TRANSACTION 69135, ACTIVE 20 sec, thread declared inside InnoDB 4997
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 4
MySQL thread id 3, OS thread handle 0x6010, query id 9 localhost ::1 root User sleep
insert into t(val) select sleep(5) from mysql.user
TABLE LOCK table `test`.`t` trx id 69135 lock mode AUTO-INC
TABLE LOCK table `test`.`t` trx id 69135 lock mode IX

As a side note, if we speak about default REPEATABLE READ isolation level, it should not matter if we have binlog enabled or what format is used for binlog.

I am not able to reproduce any variation of original test case so far on 5.6.22 so far, single row INSERT hangs waiting for the AUTO_INC lock, but this output of lock monitor:

...
---TRANSACTION 69237, ACTIVE 44 sec setting auto-inc lock
mysql tables in use 1, locked 1
LOCK WAIT 1 lock struct(s), heap size 360, 0 row lock(s)
MySQL thread id 2, OS thread handle 0x2c10, query id 54 localhost ::1 root update
insert into child (p_id) value (2)
------- TRX HAS BEEN WAITING 44 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`child` trx id 69237 lock mode AUTO-INC waiting
------------------
TABLE LOCK table `test`.`child` trx id 69237 lock mode AUTO-INC waiting
---TRANSACTION 69236, ACTIVE 47 sec inserting, thread declared inside InnoDB 1310
mysql tables in use 2, locked 2
1302 lock struct(s), heap size 210472, 208918 row lock(s), undo log entries 77659
MySQL thread id 3, OS thread handle 0x6010, query id 53 localhost ::1 root Sending data
insert into child (p_id) select t.p_id from child t
TABLE LOCK table `test`.`child` trx id 69236 lock mode IS
RECORD LOCKS space id 503 page no 8 n bits 1192 index `idx_p_id` of table `test`.`child` trx id 69236 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 4; hex 80000001; asc     ;;
... many more rows locks here

looks suspicious. Note that INSERT ... SELECT while "Sending data" holds S table lock, NOT AUTO_INC one.

HI,

I am not able to repeat this any longer, with 5.7, so this is not a bug.