Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html) defines "bulk insert" as:
"Statements for which the number of rows to be inserted (and the number of required auto-increment values) is not known in advance. This includes INSERT ... SELECT, REPLACE ... SELECT, and LOAD DATA statements, but not plain INSERT."
and then explains that with innodb_autoinc_lock_mode=1:
"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."
It seems to NOT cover a special case of INSERT INTO t ... SELECT FROM t, that is widely used. See test case below for details and http://bugs.mysql.com/bug.php?id=72748 for some history behind this report.
How to repeat:
With default transaction isolation level of READ COMMITTED and binary logging not enabled, run this in session 1:
mysql> create table t(id int auto_increment primary key, val int) engine=InnoDB;
Query OK, 0 rows affected (0.27 sec)
mysql> create table tt(id int auto_increment primary key, val int) engine=InnoDB
;
Query OK, 0 rows affected (0.25 sec)
mysql> insert into t(val) values (1), (2), (3), (4);
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into tt(val) values (1), (2), (3), (4);
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table innodb_lock_monitor(i int) engine=InnoDB;
Query OK, 0 rows affected (0.25 sec)
mysql> select version(), @@log_bin;
+-----------+-----------+
| version() | @@log_bin |
+-----------+-----------+
| 5.5.41 | 0 |
+-----------+-----------+
1 row in set (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t(val) select sleep(20) from t;
Soon after this, run the following in session 2:
mysql> insert into t(val) select 100 from tt;
Query OK, 4 rows affected (0.09 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+----+------+
| id | val |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
+----+------+
8 rows in set (0.00 sec)
Note that "bulk insert" in other session that surely needs auto-inc values is NOT blocked.
When INSERT in the first session completes (if INSERT in session 2 is a part of transaction that is NOT complete, it breaks after waiting for a lock), we see:
...
Query OK, 8 rows affected (2 min 40.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from t;
+----+------+
| id | val |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 100 |
| 6 | 100 |
| 7 | 100 |
| 8 | 100 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
| 15 | 0 |
| 16 | 0 |
| 17 | 0 |
| 18 | 0 |
| 19 | 0 |
+----+------+
16 rows in set (0.00 sec)
So, our INSERT ... SELECT that started first had seen rows from other INSERT ... SELECT ("bulk insert" in terms of the manual), and rows it inserted have a gap in AUTO_INC values, while manual says:
"This lock mode ensures that, in the presence of INSERT statements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication."
This does not seem to be true.
Moreover, in the innodb lock monitor output I see the following locks taken for table t by INSERT INTO t ... SELECT FROM t while it worked:
---TRANSACTION 5D73, ACTIVE 139 sec, thread declared inside InnoDB 494
mysql tables in use 2, locked 2
2 lock struct(s), heap size 376, 7 row lock(s)
MySQL thread id 14, OS thread handle 0x2f68, query id 259 localhost 127.0.0.1 root User sleep
insert into t(val) select sleep(20) from t
TABLE LOCK table `test`.`t` trx id 5D73 lock mode IS
RECORD LOCKS space id 0 page no 641 n bits 72 index `PRIMARY` of table `test`.`t` trx id 5D73 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000005d70; asc ]p;;
2: len 7; hex d6000001d50110; asc ;;
3: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000005d70; asc ]p;;
2: len 7; hex d6000001d5011d; asc ;;
3: len 4; hex 80000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000005d70; asc ]p;;
2: len 7; hex d6000001d5012a; asc *;;
3: len 4; hex 80000003; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000005d70; asc ]p;;
2: len 7; hex d6000001d50137; asc 7;;
3: len 4; hex 80000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 6; hex 000000005d74; asc ]t;;
2: len 7; hex da000001d90110; asc ;;
3: len 4; hex 80000064; asc d;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 000000005d74; asc ]t;;
2: len 7; hex da000001d9011d; asc ;;
3: len 4; hex 80000064; asc d;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 000000005d74; asc ]t;;
2: len 7; hex da000001d9012a; asc *;;
3: len 4; hex 80000064; asc d;;
--------
Note that the only table lock I see for table t is IS. Where is the AUTO-INC lock I wonder?
Both results of the test and lock output makes me think that AUTO_INC lock is NOT set in this case, while manual never mentions this possibility:
"In this mode, “bulk inserts” use the special AUTO-INC table-level lock and hold it until the end of the statement."
The above is obviously not true.
If you use INSERT INTO t(val) SELECT ... FROM tt in session one, you'll get other "bulk insert" blocked and you can see AUTO_INC lock in the output of lock monitor:
---TRANSACTION 5D63, ACTIVE 5 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 17, OS thread handle 0x3ab8, query id 229 localhost 127.0.0.1 root Sending data
insert into t(val) select 100 from tt
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`t` trx id 5D63 lock mode AUTO-INC waiting
------------------
TABLE LOCK table `test`.`tt` trx id 5D63 lock mode IS
RECORD LOCKS space id 0 page no 643 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 5D63 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000005d4c; asc ]L;;
2: len 7; hex b8000001ae0110; asc ;;
3: len 4; hex 80000001; asc ;;
TABLE LOCK table `test`.`t` trx id 5D63 lock mode AUTO-INC waiting
---TRANSACTION 5D62, ACTIVE 35 sec, thread declared inside InnoDB 498
mysql tables in use 2, locked 2
4 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 14, OS thread handle 0x2f68, query id 226 localhost 127.0.0.1 root User sleep
insert into t(val) select sleep(20) from tt
TABLE LOCK table `test`.`tt` trx id 5D62 lock mode IS
RECORD LOCKS space id 0 page no 643 n bits 72 index `PRIMARY` of table `test`.`tt` trx id 5D62 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000005d4c; asc ]L;;
2: len 7; hex b8000001ae0110; asc ;;
3: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000005d4c; asc ]L;;
2: len 7; hex b8000001ae011d; asc ;;
3: len 4; hex 80000002; asc ;;
TABLE LOCK table `test`.`t` trx id 5D62 lock mode AUTO-INC
TABLE LOCK table `test`.`t` trx id 5D62 lock mode IX
--------
Same results with both 5.5.41 and 5.6.22 I have at hand.
Suggested fix:
Please, make sure that AUTO_INC lock is used in case of selecting from the same table (... SELECT ... FROM t) in a similar way as it is used in case of selecting from other table (... SELECT ... FROM t) and there are no gaps in values generated (make it work "by the manual").
Alternatively, describe cases when AUTO_INC lock is NOT set at all for "bulk inserts" with innodb_autoinc_lock_mode=1.
In any case, please, describe when exactly the AUTO_INC lock is set for INSERT ... SELECT .... It seems to NOT happen before we start SELECT part.
