拍卖/银行类应用程序(Rails/MySQL)的乐观或悲观锁定

I am in the process of designing an auction like web application using Rails 3.1 and MySQL 5.1. The users will have account balances, hence it is important, that someone doesn't bid for an auction item if he has insufficient funds.

我正在使用Rails 3.1和MySQL 5.1设计一个类似web应用程序的拍卖程序。用户会有账户余额，因此重要的是，如果一个人没有足够的资金，他就不会竞标一个拍卖项目。

Obviously I will be packing "winning" of an auction into a transaction, goes something like this:

很明显，我将把拍卖的“胜利”打包成这样的交易:

Transaction 1:

事务1:

ActiveRecord::Base.transaction do
    a = Account.where(:id=>session[:user_id]).first
    # now comes a long part of code with various calculations and other table updates, i.e. time pases
    a.balance -= the_price_of_the_item
    a.save!
end

By the way, I am curerntly using optimistic locking, hence all my table have the column lock_version.

顺便说一下，我使用了乐观锁定，因此我的所有表都有列lock_version。

While such a transaction is being executed, the user could via another input place other bids, hence whenever they place a bid, a piece of code checks if the current available balance is sufficient

当这样的事务被执行时，用户可以通过另一个输入放置其他的出价，因此每当他们出价时，一段代码检查当前可用的余额是否足够。

Same here again:

我也一样:

Transaction 2:

事务2:

ActiveRecord::Base.transaction do
    a = Account.where(:id=>session[:user_id]).first
    raise ActiveRecord::Rollback if a.balance < the_price_of_the_bid + Bids.get_total_bid_value_for_user(session[:user_id])
    # now process the bid saving
end

Obviously I need to ensure, that the two transactions do not overlap, otherwise transaction 2 might be reading the balance while transaction 1 is in the midst of processing and I end up with a negative account balance (the bid gets saved and afterwards transaction 1 commits, then the user has possibly bid with funds he does not have anymore).

显然我需要确保两个事务不重叠,否则事务2可能是阅读的平衡而事务1是在处理中,我最终得到一个负面的帐户余额(投标被保存,后来事务1提交,那么用户可能收购基金他没有了)。

One thing to note is, that the transaction 2 does not make any changes to the Account, it merely reads the account. I guess it comes down to the question: How to prevent any reads for selected SELECT statements while running transaction 1.

需要注意的是，事务2没有对帐户做任何更改，它只是读取帐户。我想问题的关键在于:如何在运行事务1时防止对所选SELECT语句的读取。

How do I make transaction 2 wait for transaction 1 to complete? Is it possible with optimistic locking and one of the available MySQL transaction isolation levels or do I need to use pessimistic locking here? If pesimistic locking is the only answer would adding a.lock! after reading the account record in each of the two transactions be sufficient?

如何使事务2等待事务1完成?乐观锁定和可用的MySQL事务隔离级别是否可能，或者我是否需要在这里使用悲观锁定?如果是pesilock，唯一的答案是添加a.lock!在阅读两个交易中的每一个账户记录后，是否足够?

Design criterias are of course

设计标准是当然的

• I am looking for the most performant solution even if it means more coding.
• 我正在寻找性能最好的解决方案，即使它意味着更多的编码。
• data consistency is of utmost importance
• 数据一致性是最重要的

2 个解决方案

#1

Having spend now almost 10 hours non-stop reading various posts and documents as well as trial and erroring using the Rails console, I want to summarize my findings:

现在我已经花了近10个小时不停地阅读各种文章和文件，以及使用Rails控制台进行尝试和犯错，我想总结一下我的发现:

Optimistic locking: Is of no use to address my requirements, the locking only kicks in if I actually save the account balance record. But placing a bid does not update the account record so it wont trigger the optimistic locking, unless I maintain a field in the account record that tracks my current committed funds for all open bids and hence will update the account record upon bid placement (which I dont want to do as it will require another DB update whenever a bid gets saved).

乐观锁定:对于满足我的需求没有用处，只有当我实际保存帐户余额记录时才会进行锁定。但放置一个出价不更新帐户记录所以不会触发乐观锁定,除非我帐户维护字段记录跟踪我目前所有打开的投标承诺资金,因此将更新帐户记录在投标位置(我不想做,因为它需要另一个数据库更新报价时被保存)。

So that leaves me only with Pesimistic locking. For simplicity purposes I decided to put a lock on the User record, thus the code for my transaction 1 is changed to:

所以我只剩下了通灵锁。为了简单起见，我决定将一个锁放在用户记录上，因此我的事务1的代码改为:

Transaction 1:

事务1:

ActiveRecord::Base.transaction do     
    u = User.find(session[:user_id],:lock=>true)
    a = Account.where(:id=>session[:user_id]).first 
    a.balance -= the_price_of_the_item
    ... some more code here ...
    a.save!      
end      

and transaction 2:

和事务2:

ActiveRecord::Base.transaction do
    u = User.find(session[:user_id],:lock=>true)
    raise ActiveRecord::Rollback if a.balance < the_price_of_the_bid + Bids.get_total_bid_value_for_user(session[:user_id])          
    # now process the bid saving
    ....
end

In addition I decided to set the MySQL transaction isolation level to SERIALIZABLE.

此外，我决定将MySQL事务隔离级别设置为SERIALIZABLE。

#2

I interpret your question in the following way.

我对你的问题作如下解释。

1. An Account has a balance
2. 帐户有余额。
3. An Account has many Bids
4. 一个帐户有许多出价
5. A Bid has a value
6. 出价有其价值。
7. A Bid which has neither won, nor lost, is "Open"
8. 一个既没有赢也没有输的出价是“公开的”
9. When a Big is won, its value is deducted from the balance of the Account.
10. 当一个大的赢了，它的价值从帐户余额中扣除。
11. A Bid can only be made, if the sum of values for "Open" Bids is less than the balance.
12. 只有当“公开”投标的价值之和小于余额时，才可以投标。

As such you have identified the transactions that matter.

因此，您已经确定了重要的事务。

1. Placing a bid
2. 放置一个出价
3. Winning a bid
4. 赢得投标

This is how I'd do it.

我就是这么做的。

1.

1。

account = Account.find_by_id(session[:user_id])
# maybe do some stuff here

transaction do
  account.lock!

  bid_amount = account.bids.open.sum(:value)
  if bid_amount + this_value > account.balance
    raise "you're broke, mate"
  end

  account.bid.create!(:value => this_value)
end

We hold a row lock on the Account for a short while, but assuming you're using the right database, this should be okay.

我们在帐户上保持行锁很短一段时间，但是假设您使用的是正确的数据库，这应该没问题。

Assuming the first bit was right, then the next is much easier

假设第一个比特是正确的，那么下一个比特就容易得多

2.

2。

class Bid
  def win!
    transaction do
      account.lock!
      account.decrement(:balance, self.value)
      account.save!
      close!
    end
  end
end

Notably if you did a SQL update SET balance = balance - ? you wouldn't need to do a lock on the 2nd one.

值得注意的是，如果您做了SQL update SET balance = balance - ?你不需要对第2个进行锁定。

But generally, do the lock around the minimal section of code.

但是，通常情况下，对代码的最小部分进行锁定。

Realistically, you shouldn't have a lock for more than 100ms.

实际上，您的锁不应该超过100ms。