0

I am working on a project that will collect many traffic on the site, so when a user creates a post, the ID of that inserted post should be available immediately for inserts into another table.

My question is, when there is too much traffic on the site, will inserting a new post conflict in a way that, if user1 inserts a post first and was on a slow network, nearly at the same time but after user1, user2 inserts a post and was on a faster network? With that happening, the user2's post will be the last inserted one, user1 may query the user2's post id as the last inserted post ID.

I don't know the best way to explain this, but I hope someone will understand and help me out.

<?php
    $sql = $db_connect->prepare("insert into post(userid,postnum,posttext)values(?,?,?)");
    $sql->bind_param("sss",$id,$postnum,$post);
    $sql->execute();
    $post_id = $sql->insert_id;
?>
Taha Paksu
  • 15,371
  • 2
  • 44
  • 78
alertme
  • 61
  • 7
  • 1
    https://dba.stackexchange.com/questions/21181/is-mysqls-last-insert-id-function-guaranteed-to-be-correct – Mat Oct 25 '17 at 08:00
  • https://www.w3schools.com/php/php_mysql_insert_lastid.asp last insert id tie to connection – Deno Oct 25 '17 at 08:04

2 Answers2

1

Use autoincrement field for your Id instead of generating it yourself. Db will take care to make it unique no matter of number of concurrent requests.

Marcin Orlowski
  • 72,056
  • 11
  • 123
  • 141
1

I think it be in the sequence in which the complete requests are reached to the server. MySQL would then queue the requests accordingly.

If the requests are exactly the same time (coincidence) then in this scenario the order of insertion cannot be guaranteed.

You can use autoincreamented ids in your table. After you insert you can fetch the last mysql_inserted_id for your reference. 

<?php
    $sql = $db_connect->prepare("insert into post(userid,postnum,posttext)values(?,?,?)");
    $sql->bind_param("sss",$id,$postnum,$post);
    $sql->execute();
    $post_id = $db_connect->lastInsertId();
?>

You might wanna check out Concurrent insert with MySQL and https://dev.mysql.com/doc/refman/5.7/en/concurrent-inserts.html

Abhilash Nayak
  • 664
  • 4
  • 10