Testing concurrent mysql insert operations in php

Question

I want to run this php script multiple times concurrently.

$db =  new mysqli('localhost', 'me', 'pw', 'test');   
$time = time();
$sql = "  INSERT INTO test  (  `test_created`  ) VALUES( ". $time  .") ";
$result = $db->query($sql);

I am doing this because I want to replicate a scenario in which many users submit database entries at exactly identical time up to nanosecond, which is what I mean by 'concurrent' insert.

I tried using the virtual cron scheduler of my shared hosting provider by specifying the hour and minute of execution. Then I made five identical files containing the above-mentioned codes.

However, when I look at the table entries, the inserted timestamp are not identical. Is this because mysql can't handle concurrent insert operations? If mysql can handle 'concurrent insert', is this the right way to test concurrent insert operations? If not, how should I do it for testing purposes in php environment with or without cron?

I believe MYSQL deals with concurrent write access by queuing and therefore serializing all updates that are to be done on a single resource. Depending on the database engine you use the resource could be a whole table or a page ( subsection ) of a table — RiggsFolly
– RiggsFolly, Commented Dec 9, 2015 at 0:38

Sammitch · Accepted Answer · 2015-12-09 00:37:37Z

6

Attempting to simulate actions occurring within the same nanosecond is difficult and impractical in general, and I'm just going to make a blanket statement that it's simply impossible in either PHP or mySQL separately, let alone in tandem.
You can't insert two rows into a table simultaneously. Not ever. You can have two threads within an RDBMS inserting rows so fast and seamlessly that for all intents and purposes you can claim that it has happened simultaneously, but for a RDBMS to maintain consistency there must be on single, contiguous order of operations with no branching.
Don't even worry about the situation you're attempting to simulate. I would have to do serious research to find an RDBMS that can't handle the situation gracefully.

answered Dec 9, 2015 at 0:37

Sammitch

32.5k7 gold badges58 silver badges92 bronze badges

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

1 Comment

RiggsFolly Over a year ago

Yea, what he said, as he took the time to say it so much better than me

Collectives™ on Stack Overflow

Testing concurrent mysql insert operations in php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related