PHP / MySQL - how to prevent two requests *Update

Question

I have some question ... example: a user will buy something for his USD

1. Check his USD Balance
2. Deduct the USD from his account
3. Make an Order -> order queue
4. user gets his item and the other one gets his USD

Lets say, the users makes 5 requests in the same second (very fast). So it is possible (and happen) that 5 requests are running. He has only money to buy only from 1 request. Now the requests are so fast, that the script checks his balance, but is not so fast, that it deduct the money from his account. So the requests will pass two times! How to solve it?

I use LOCK in mysql before I start the process:

1. IS_FREE_LOCK - check is there a lock for this user if not -> 2.
2. GET_LOCK - sets the lock
3. make the order / transaction
4. RELEASE_LOCK - releases the lock

But this does not really work. Is there another way?

function lock($id) {
  mysql_query("SELECT GET_LOCK('$id', 60) AS 'GetLock'");
}

function is_free($id) {
  $query = mysql_query("SELECT IS_FREE_LOCK('$id') AS 'free'");
  $row = mysql_fetch_assoc($query);
  if($row['free']) {
    return true;
  } else {
    return false;
  }
}

function release_lock($id) {
  mysql_query("SELECT RELEASE_LOCK('$id')");
}

function account_balance($id) {
  $stmt = $db->prepare("SELECT USD FROM bitcoin_user_n WHERE id = ?");
  $stmt->execute(array($id));
  $row = $stmt->fetch(PDO::FETCH_ASSOC);

  return $row['USD'];
}

if(is_free(get_user_id())) {
  lock(get_user_id());
  if(account_balance(get_user_id()) < str2num($_POST['amount'])) {
    echo "error, not enough money";
  } else {
    $stmt = $db->prepare("UPDATE user SET USD = USD - ? WHERE id = ?");
    $stmt->execute(array(str2num($_POST['amount']), get_user_id()));
    $stmt = $db->prepare("INSERT INTO offer (user_id, type, price, amount) VALUES (?, ?, ?, ?)");
    $stmt->execute(array(get_user_id(), 2, str2num($_POST['amount']), 0));
}

Update Tested the transaction function with SELECT ... FOR UPDATE

$db->beginTransaction();
$stmt = $db->prepare("SELECT value, id2 FROM test WHERE id = ? FOR UPDATE");
$stmt->execute(array(1));
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if($row['value'] > 1) {
  sleep(5);
  $stmt = $db->prepare('UPDATE test SET value = value - 5 WHERE id = 1');
  $stmt->execute();
  $stmt = $db->prepare('UPDATE test SET value = value + 5 WHERE id = 2');
  $stmt->execute();
  echo "did have enough money";
} else {
  echo "no money";
}
$db->commit();

Answer 1

You need to use Data revision for MySQL UPDATE.

Answer 2

Use TRANSACTION and if it fails you can rollback.

For example, assume the current balance is $20.

Connection A               Connection B
=======================    ===========================
BEGIN TRANSACTION         
                           BEGIN TRANSACTION
SELECT AccountBalance  
                           SELECT AccountBalance
--returns $20
--sufficient balance,
--proceed with purchase
                           --returns $20
                           --sufficient balance,
                           --proceed with purchase

                            --update acquires exclusive lock
                           UPDATE SET AccountBalance
                              = AccountBalance - 20
--update blocked due
UPDATE SET AccountBalance
  = AccountBalance - 20

                           --order complete
                           COMMIT TRANSACTION

--update proceeds

--database triggers
--constraint violation
--"AccountBalance >= 0"

ROLLBACK TRANSACTION

Answer 3

This is how I used to do it many years ago..

results = query("UPDATE table SET value=value-5 WHERE value>=5 AND ID=1")
if (results == 1) YEY!

(Is this still a reliable method?)

Answer 4

you need to use TRANSACTION at the SERIALIZABLE isolation level.

Answer 5

First off, you have to use transactions, but that's not enough. In your transaction, you can use SELECT FOR UPDATE.

It's basically saying, "I'm going to update the records I'm selecting", so it's setting the same locks that an UPDATE would set. But remember this has to happen inside a transaction with autocommit turned off.