Mysql 'Interleaved' auto increment lock mode still running my concurrent statements sequentially

MySQL & MariaDB
1

Hi,

I was hoping to improve the speed and concurrency of our system by enabling the ‘Interleaved’ mode on auto increment locks as explained here: AUTO_INCREMENT Handling in InnoDB - MariaDB Knowledge Base.

I ran a test before and after enabling the mode by running several concurrent inserts into a table with an auto-increment id. Each statement inserted 50,000 records using a multi-line insert statement. (Explained here, for example: MySQL Insert Multiple Rows By Practical Examples).

I’m getting the same results regardless of changing to ‘interleaved’ mode. If I run a single insert, it takes about 20 seconds. Running 3 inserts concurrently appears to cause them all to run consecutively and it takes about 60 seconds to finish (though it does seem like all three statements finish about around the same time). The auto-increment values are consecutive by each statement, not interleaved.

Shouldn’t these be running concurrently? I was expecting to get near 20 second runtimes even when running 3 concurrent statements. Is there some other table level lock in play here that I’m not aware of?

I tried both with and without a duplicate key update as part of the statement and got the same results.

2

I ran another test using an identical table except that it did NOT have an auto-increment ID, and I’m getting similar runtimes.

It appears that inserts of this form lock the table regardless of whether there’s an auto-increment ID. If anyone has any knowledge of whether it is possible to run inserts concurrently, I’d love to hear from you.

1 Like