MySQL Bugs: #76563: Manual does NOT explain when exactly AUTO-INC lock is set for "bulk inserts"

Bug #76563	Manual does NOT explain when exactly AUTO-INC lock is set for "bulk inserts"
Submitted:	1 Apr 2015 15:26	Modified:	1 Jun 2017 14:20
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5, 5.6, 5.7	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	AUTO-INC, innodb, innodb_autoinc_lock_mode, missing manual

Description:
MySQL manual (https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-configurable.html) mentions AUTO-INC lock:

"InnoDB uses a special lock called the table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction), to ensure that auto-increment numbers are assigned in a predictable and repeatable order for a given sequence of INSERT statements."

Then it defines "bulk inserts":

"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. InnoDB will assign new values for the AUTO_INCREMENT column one at a time as each row is processed."

and says about default locking mode:

" 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. "

This explains when the lock is released. But manual does not explain when this lock is set on "target" table in INSERT ... SELECT or REPLACE ... SELECT cases. This is important to know, as before it set other "bulk insert" has a chance to be executed and committed, and its results may influence the result of our current statement.

How to repeat:
Try to explain, based on the manual, what happens in the test case described in http://bugs.mysql.com/bug.php?id=76533, for example.

Suggested fix:
There are reasons to think that when target table is different from the source one, AUTO-INC lock is set on the target table after reading the first row from the source one. Check http://mysqlentomologist.blogspot.com/2015/03/using-gdb-to-understand-what-locks-and_31.ht... for more details.

The case when source and target table is the same may be even more interesting.

So, please, explain when exactly AUTO-INC lock is set in case of bulk inserts in all modes, by design.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

Posted by developer:
 
The AUTO-INC lock content referenced by this bug report was updated to include the following text.

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

"If the source table of the bulk insert operation is different from
the target table, the AUTO-INC lock on the target table is taken after a
shared lock is taken on the first row selected from the source table. If
the source and target of the bulk insert operation are the same table, the
AUTO-INC lock is taken after shared locks are taken on all selected rows."

Thank you for the bug report.