1

I have the following setup:

A query like: 

  "INSERT INTO table (c1, c2,...) values (v01, v02,...), (v11, v12,...)..."

The table has a primary key with auto increment. I need to know what are the indexes for each row inserted.

One way to do this would be to take the last rows index and inserted indexes are from lastRowIndex - nrRows to lastRowIndex.

My problem/uncertainty is: If another insert is run in parallel with this one that inserts rows in the same table (another user calls the same function for example), is there any chance (no matter how small) to insert a row between those generated by the query stated before? Again... is very important (the reason is clear... it kills the ids structure) that that won't happen so I need to be sure.

Or any chance for the ids not to be consecutive for no matter what reason.

zozo
  • 8,230
  • 19
  • 79
  • 134

1 Answers1

1

Suppose that there are two transactions running, each inserting rows into a table with an AUTO_INCREMENT column. One transaction is using an INSERT ... SELECT statement that inserts 1000 rows, and another is using a simple INSERT statement that inserts one row:

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB cannot tell in advance how many rows will be retrieved from the SELECT in the INSERT statement in Tx1, and it assigns the auto-increment values one at a time as the statement proceeds. With a table-level lock, held to the end of the statement, only one INSERT statement referring to table t1 can execute at a time, and the generation of auto-increment numbers by different statements is not interleaved. The auto-increment value generated by the Tx1 INSERT ... SELECT statement will be consecutive, and the (single) auto-increment value used by the INSERT statement in Tx2 will either be smaller or larger than all those used for Tx1, depending on which statement executes first.

As long as the SQL statements execute in the same order when replayed from the binary log (when using statement-based replication, or in recovery scenarios), the results will be the same as they were when Tx1 and Tx2 first ran. Thus, table-level locks held until the end of a statement make INSERT statements using auto-increment safe for use with statement-based replication. However, those locks limit concurrency and scalability when multiple transactions are executing insert statements at the same time.

Reference

Community
  • 1
  • 1
Kermit
  • 33,827
  • 13
  • 85
  • 121
  • Yes... I know how it works and my thoughts are same as yours "This is not likely to happen". The problem is that is a bank account table. that "likely" must be a "definite no". – zozo Mar 15 '13 at 13:42
  • @zozo Take a look at [this question](http://stackoverflow.com/questions/8481421/does-a-mysql-multi-row-insert-grab-sequential-autoincrement-ids) – Kermit Mar 15 '13 at 13:43
  • Ty for the link. Clarifies a bit but still... Is innodb... The question is if mysql treats the multiple insert as a single query and locks table till all insert is finished or as multiple insert queries and locks for each. What I mean is that I know ids will be consecutive. I am not sure if the right ids are consecutive. Dunno how to say it in english better :(. – zozo Mar 15 '13 at 13:46