使用$ GLOBAL来防止SELECT最新ID + 1和INSERT之间出现重复键问题

Question

This is a 12 year old script written in php 5.3 or something which I recently upgraded to 5.6.

It interfaces with paypal parallel payments (grandfathered in) and the bug (not sure how it survived this long) is this:

1. User A goes to the payment page
2. $order_id is generated: mysql_query("select max(order_id)+ 1 as id from orders");
3. $order_id is inserted into a couple of other tables
4. User A is redirected to Paypal

Meanwhile

1. User B goes to the payment page.
2. $order_id is the same because orders table hasn't been updated yet

So now whichever users order processes through paypal SECOND and returns success to the payment script, the INSERT statement will fail because $order_id has already been inserted into the table.

We are (supposedly) replacing the whole system in a month or two and need a quick fix.

My first thought was:

//source: https://stackoverflow.com/a/3146986/2223106
define('MYSQL_CODE_DUPLICATE_KEY', 1062);
mysql_query($sql);
if (mysql_errno() == MYSQL_CODE_DUPLICATE_KEY) {
    $Invoice_id++;
    $sql = "INSERT etc

But now I realize maybe I can define a global $order_id and check it against mysql_query("select max(order_id)+ 1 as id from orders");, then if it matches the current next-id increment it by one.

Does this seems like a reasonable approach:

$excqry=mysql_query("select max(order_id)+ 1 as id from orders ") or die(mysql_error());

if(mysql_num_rows($excqry) > 0) {

  $row1=mysql_fetch_array($excqry);

  $Invoice_id = $row1['id'];

  if (isset($GLOBALS['order_id']) && ($GLOBALS['order_id'] <= $Invoice_id)){
    $GLOBALS['order_id'] = $Invoice_id + 1;
    $Invoice_id++;
  }

  // Continue with our MySQL statements

Obviously this doesn't deal with User C potentially coming along at the same time, but the site only has a few dozen users a week.

Answer 1

So, to solve your problem, you should make following steps

1. Define order_id as a PRIMARY KEY
2. Add to it AUTO_INCREMENT.
3. Then, in your controller, you store your order immediately after all checks and then use mysql_insert_id to retrieve order_id
4. Now, you can insert $order_id anywhere you need