CODEWITHSUNDEEP
mysqlsql
zozo
zozo

Reputation: 8602

mysql - multiple insert indexes

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.

Upvotes: 1

Views: 1337

Answers (1)

Kermit
Kermit

Reputation: 34063

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

Upvotes: 1

Related Questions