Safely decrease User balance column. Should I use optimistic locking?

Question

I have a simple Silex web app with MySQL/Doctrine ORM. Each User has balance (it's a simple app, so just column is fine) and I need to decrease it after some action (checking that it is > 0 of course).

As I understand I can use optimistic locking to avoid conflicts/vulnerabilities. I have read the docs http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html but I can't find any complete example about using it.

Where do I get the "expected version"? Do I need to pass it as input (hidden form field)? Or there are better ways? The docs say something about session but I don't get how I could store it there (update session on each request?).

Also if I pass it as input, then as I understand there is no way to repeat the query automatically after catching OptimisticLockException without notifying user about that? (for example if user opened two tabs and submitted the request in them one by one)

My goal is just to prevent potential issues when user sends several requests at the same time and balance gets decreased only once etc. So it would be good to be able to repeat it automatically on lock error without involving the user. Because if I pass it via form then getting this error because of multiple tabs is very likely. So it seems kind of complicated, maybe there is something else instead of optimistic locking?

Answer 1

You should only use locking for operations that can't be executed atomically. So if possible avoid querying the object, checking the amount and then updating it. If instead you do:

update user set balance = (balance + :amount) 
where (balance + :amount) >= 0 
and id = :user_id

This you will check and update in one operation, updated rows count will be 1 if the check passed and the balance was updated and 0 otherwise.

Answer 2

Create a column named "version" in the "user" table and make it a "timestamp" column ( with "on update CURRENT_TIMESTAMP" attribute). So, "User" ORM class will look like below :

class User
{
    // ...
    /** @Version @Column(type="timestamp") */
    private $version;
    // ...
}

Now, read the current record with its "version".

$theEntityId = YOUR ENTITY ID;
$entity = $em->find('User', $theEntityId);
$expectedVersion = entity->version;
try {
   // assert version
    $em->lock($entity, LockMode::OPTIMISTIC, $expectedVersion);

    // do the work

    $em->flush();
} 
catch(OptimisticLockException $e) {
    echo "Sorry, but someone else has already changed this entity. Please apply the changes again!";
}