MySQL - Is it possible to run multiple synchronous inserts?

Question

I googled and searched on SO, but was not able to find an answer; maybe you could point me to some reference/docs?

This is more about understanding the way MySQL treats table contents while inserting. I have a table (Myisam) which has an auto-increment primary key 'autoid'. I am using a simple script to insert 1000s+ of records. What I am trying to do is running multiple instances of this script (you can image it similar to accessing the script from different machines at same time).

Is MySql capable of distributing the auto-increment primary keys accordingly without any further action from my side or do I have to do some sort of table locking for each machine? Maybe I have to choose InnoDb over MyIsam?

What I am trying to achieve is: irrespective of how many machines are simultaneously triggering the script, all inserts should be completed without skipping any auto-increment id or throwing errors like "Duplicate Value for...".

Thanks a lot

If you have the table, and you have the script, what prevents you from trying it and seeing for yourself instead of asking here? — Mike Nakis
– Mike Nakis, Commented Oct 4, 2015 at 6:43
I was/am not sure whether I'd be able to simulate it dealing with (and capturing potential errors) that amount of data; like running 5 instances where each instance performs 1k+ inserts. — DingDong
– DingDong, Commented Oct 4, 2015 at 6:52

Niels Keurentjes · Accepted Answer · 2015-10-04 06:46:42Z

3

The whole point of using a database is that can handle situations like this transactionally. So yes, this scenario works fine on every commonly used DBMS system, including MySQL.

How do you think the average forum would work with 50 users simultaneously posting replies to a topic, all from forked parallel Apache processes so possible only microseconds apart, or from multiple loadbalanced webservers?

Internally it just uses a mutex/semaphore like any other process when accessing and incrementing the shared resource (the autoincrement value of a table in this case) to mitigate the inherent race conditions.

answered Oct 4, 2015 at 6:46

Niels Keurentjes

42.1k9 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

DingDong Over a year ago

Thousand thanks for your reply. So it is built in, I did not know that. Is there any expected (worth being concerned about) delay while dealing with data that goes beyond 1m records?

Niels Keurentjes Over a year ago

Nothing worse than the lot of work it takes anyway. You'd best look into the various bulk insert methods though (INSERT with multiple rows and LOAD DATA command) as they're likely (read: most certainly) far more efficient than your own parallel processing implementation. Just keep it in 1 script and let MySQL do the hard work it was designed to do.

DingDong Over a year ago

Thanks, Niels. But the current structure allows no other method than simultaneous insert-access from several machines; it has been built like that. I am sure to look into your advice regarding structure/performance improvement.

Niels Keurentjes Over a year ago

The bulk inserts are still likely to (dramatically!) improve performance from the separate machines.

Collectives™ on Stack Overflow

MySQL - Is it possible to run multiple synchronous inserts?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related