MySQL atomic update question

[Yelgnidroc]

I have an on-line order system where many customers can place an order.

I need to have an order number, and increment it each time a new order number is issued. I don't want to lose any order numbers, or duplicate any.

If I use the following statement from php will the update be atomic i.e. between the two statements no other user can access the data.

$query = "SELECT `on` FROM order_number; UPDATE order_number set `on` = `on`+1;";

mysql_query($query);


Would it make any difference which engine I used?

[chazzy]

that's not true, no.

[Yelgnidroc]

Any ideas how to do such an atomic update?

[chazzy]

why can't you just use an auto increment id?

[Yelgnidroc]

I thought about that first, but how would I know what it got incremented to?

Of course you can check after the increment, but what if another user auto-incremented before you read the new value?

Are transactions the way to go?

[chazzy]

mysql has a function, LAST_INSERT_ID(). There's also a matching PHP function.