I do not know how to correctly simulate the situation to understand, so I ask:

CREATE TABLE `test` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `value` INT UNSIGNED NULL DEFAULT '0', PRIMARY KEY (`id`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB;

I insert in one request a large amount of data, for example 10,000 lines:

 START TRANSACTION; INSERT INTO test (`value`) VALUES (1),(2),(3), ... , (10000); COMMIT;

If at the same time in another stream there will be another similar transaction, the auto-increment field will be values ​​in order, without breaks 1 ... 10,000 or mixed with values ​​from another transaction?

Is it possible to lock the database for the duration of the request?

What I do: you need to occasionally insert large amounts of data with many-to-many links, but the script will work on shared hosting with restrictions on database queries and do 10,000 INSERT + 10,000 LAST_INSERT_ID + ~ 30000 INSERT in a linked table there is no possibility. Here also I think up how to reduce the number of requests to a DB.

    2 answers 2

    I will answer myself. Whatever happens to the database, while you are inside your transaction, you only see changes that occur within the transaction. Therefore, you need to do this:

     START TRANSACTION; /* Вставляем данные в основную таблицу */ INSERT INTO test (`value`) VALUES (1),(2),(3), ... , (10000); /* Получаем список PRIMARY KEY 10 000 последних строк */ SELECT _id FROM (SELECT id AS _id FROM `test` ORDER BY id DESC LIMIT 100000) AS subq ORDER BY _id ASC; /* На основании этих `_id` формируем новый запрос на вставку данных в связанную таблицу */ INSERT INTO pivot_table (`test_id`, `relation_id`) VALUES (...),(...), ...;

    It turned out only three requests.

    • “while you are inside your transaction - you see only changes” - depending on the isolation level of the transactions! Only yours is Repeatable read. On Read committed there will be others recorded. - Small
    • @ Small right. just default Repeatable read . - toxxxa

    When inserting with a single query (and this is an atomic operation!), The block of auto-increment values ​​is reserved BEFORE insertion. So there will be no "left inserts" from the neighbors in the server.

    It makes no sense to lock the entire database - it is enough to lock only the tables used.