MySQL - id "reservation" before insert

I need to know the id (and reserve it) for a record that I will insert in database before insert it because I add a product and upload also a image for this product, and the location of image is related to the product id.

I can find t开发者_JAVA技巧he id that will follow, but I need also to keep it "lock" until the product image is uploaded, to be sure that this id isn't used by another user meanwhile.

You can split this into two operations.

In the first you create everything for the product and get the id. In the second you update the row with the image you've uploaded, which can now be saved since you know the ID.

you can do it as a transaction

I googled "php mysql transaction" and there are a lot of tutorials on how to do this

http://www.linuxdig.com/news_page/1079394922.php

id reservation can only be done safely if you have your own auto increment implementation. Even when running in a transaction, you can not know for sure what the next id is. You just can be sure what you have when you requested for it.

Some people use the mysql_insert_id to receive the last inserted id. But I have somewhere in mind this function is not threadsafe and you'll probably get the id of another thread which just inserted.

However, in your case only two options come to my mind:

1) create your own sequence generator 2) insert your datarow, select it again with all values in the where clause, and use that id. Run option 2 in a transaction (as already suggested above)

I would go for option 1. Sequence generation is commonly used with all other databases (from the widely used only mysql supports auto increment as far as I know). Sequences therefore are fully acceptable.

Cheers, Christian