How should I handle query synchronization in PHP?

Question

I would like to insert some value into a table, which has an auto-incrementing field as a primary key. Then I want to retrieve the ID by using mysql_insert_id() and use this ID as a foreign key in another table. The problem is - although very unlikely - it may happen that between the first insertion and the later retrieving, another insertion happens into the first table, thus a wrong ID will be given back.

Does PHP handle this automatically, or are my concerns valid? If so, how can I overcome them?

Answer 1

SQL transactions are what you need. In MySQL, InnoDB is the only engine that supports transactions.

Answer 2

mysql_insert_id() will return the last inserted id on a per connection base. So basically, you don't have to worry about concurrent script requests, if that is your worry.

For reference: see the MySQL docs.

Edit:

BTW, you could test this quite easily.

test.php

<?php

    $sleep = isset( $_GET[ 'sleep' ] ) ? true : false;

    $conn = mysql_connect( /* your parameters */ );
    mysql_select_db( /* your db */, $conn );

    $sql = 'INSERT INTO yourtable(id,col1,col2) VALUES(null,"test","test")';
    mysql_query( $sql );

    if( $sleep ) sleep( 5 );

    echo mysql_insert_id();
?>

open two browser tabs:

request this in the first:
http://localhost/test.php?sleep=1

request this in the second within, say, 4 seconds max:
http://localhost/test.php

First request should give you an ID less than the second request.

Answer 3

There are two general strategies that you can use to ensure that this problem does not impact you. First, you can use transactions (as another author pointed out).

However from a performance perspective, it may be faster for you to manually track Id numbers. You can do this by using a global id in the database. A rolling number that makes ids in the system globally unique.

Lets say the global id is 100 and you know that you are going to need 6 ids. Then you write out 106 to the global id row in the global id table... then you use 101 for the first entry, which is the foreign key in the 102 data point and so on. This improves performance considerably when working on large data sets.

So if you need to make 100 new inserts this might be a good idea. If you only need 6 at a time.. use transactions...

As suggested by jmucchiello in comments. You can use an Update statement to ensure that another process is not writing to the global id entry. Something like

update globaltable set id = 106 where id = 100

I can see that I am getting modded down on this answer, but this really is the best strategy if you have a million rows to import... oh well...

-FT