安全地减少用户余额列。我应该使用乐观锁定吗？

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).

我有一个简单的Silex Web应用程序与MySQL / Doctrine ORM。每个用户都有余额（这是一个简单的应用程序，所以只有列很好）我需要在一些操作后减少它（当然检查它是> 0）。

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.

据我所知，我可以使用乐观锁定来避免冲突/漏洞。我已经阅读了文档http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html但我找不到任何关于使用它的完整示例。

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)

此外，如果我将其作为输入传递，那么据我所知，在捕获OptimisticLockException之后无法自动重复查询而不通知用户这一点？（例如，如果用户打开了两个选项卡并逐个提交请求）

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?

我的目标是在用户同时发送多个请求并且平衡仅减少一次等时防止潜在问题。因此，如果能够在不涉及用户的情况下自动重复锁定错误，那将是一件好事。因为如果我通过表单传递它，那么很可能因为多个选项卡而出现此错误。所以看起来有点复杂，也许有其他东西而不是乐观锁定？

3 个解决方案

#1

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 :

在“user”表中创建一个名为“version”的列，并将其设置为“timestamp”列（使用“on update CURRENT_TIMESTAMP”属性）。因此，“用户”ORM类将如下所示：

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!";
}

#2

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.

您将在一次操作中检查和更新，如果检查通过且更新了余额，则更新的行数将为1，否则为0。

#3

Optimistic lock will allow concurrent access to read the entity (meaning that there might be some threads that will read out of date data) , while Pessimistic lock will lock reading if someone is performing an operation on that registry.

乐观锁将允许并发访问读取实体（意味着可能有一些线程将读取过期数据），而悲观锁将锁定读取，如果有人在该注册表上执行操作。

Depends on how critically accurate you want your concurrent access to be?! Is it ok to read out of date Data?

取决于您希望并发访问的准确性如何？！读取过时的数据是否可以？

Eg:

例如：

{OTIMISTIC LOCK}
Thread1 -> read(balance1[200$][version=1])
Thread2 -> read(balance1[200$][version=1])
Thread1 -> balance.add(100$).save()[300$ total and version=2]
Thread2 -> balance.add(50$).save()[OtimisticLockError Version-> 2 != 1]

{PESSIMISTIC LOCK}
Thread1 -> read(balance1[200$]) [lock for update | select for update |... depends on DB])
Thread2 -> read(balance1) [Pessimistic lock exception]
Thread1 -> balance.add(100$).save()[300$ total]
Thread1 -> release lock balance1
Thread2 -> read(balance1[300$]) Ok

OPTIMISTIC LOCK

PESSIMISTIC LOCK

PESSIMISTIC vs. OPTIMISTIC

PESSIMISTIC与OPTIMISTIC

#1