乐观锁队列

问题描述：

我正在使用 PostgreSQL 作为数据库在 Node.js 中编写应用程序.但我有一些问题.我有一张表格，其中包含有关地区资源的信息:

I'm writing an application in Node.js using PostgreSQL as database. But I have some problems. I have a table with information about resources in region:

CREATE TABLE regions_indexes
(
  id integer NOT NULL,
  resource_type integer NOT NULL,
  current_resource integer,
  maximum_resource integer,
  CONSTRAINT regions_indexes_pkey PRIMARY KEY (id, resource_type)
)

用户点击按钮，应用程序根据 current_resource 计算各种参数，然后做 current_resource - $calc_value.因为我可能非常并发地使用事务.但是在计算过程中可能会出现一些错误，需要重复计算.现在我使用 SELECT ... FOR UPDATE 使用 current_resource 锁定行.如果 current_resource 的当前值非常重要，并且首先点击的用户应该使用 max.可用的 current_resource.换句话说，我应该为 current_resource 实现访问队列.

Users clicks on button, application calculates various parameters based on current_resource and then do current_resource - $calc_value. Because it's maybe very concurently I use transactions. But in process calculation maybe some errors, and we need to repeat calculations. Now I'm using SELECT ... FOR UPDATE for locking row with using current_resource. How i can do it with no lock using optimistic locking, if current value of current_resource is very important, and user who clicks first should use max. avalaible current_resource. In other words, I should implement acess queue for current_resource.

答

对于乐观锁定，您需要定义一些方法来检查一行自上次看到以来是否发生了变化.例如，让我们添加另一个标识符:

For optimistic locking you need to define some means to check if a row has changed since you saw it last time. For example, lets just add another identifier:

alter table regions_indexes add version_id integer default 1 not null;

现在应用程序读取一些行，向用户显示数据并等待按钮被点击.我们必须记住我们得到的 version_id 的值.

Now the application reads some row, shows the data to the user and waits until button is clicked. We must remember the value of version_id we got.

单击按钮后，您将执行所有必要的计算.当您准备好更新该行时，您锁定该行并检查 version_id 是否未更改.如果没有，增加 version_id 并提交.如果有，那就倒霉了——你需要告诉用户重复操作，因为有人跑得比他快.

After button is clicked, you perform all the necessary calculations. When you're ready to update the row, you lock the row and check whether version_id has not changed. If it has not, increment version_id and commit. If it has, bad luck --- you need to tell the user to repeat the operation because someone outrun him.

它可能看起来像这样(伪代码):

It may looks like this (in pseudocode):

-- remember version_id
select *
from regions_indexes
where id = ... and resource_type = ...;

-- wait for user click
-- you can wait for a long time, because no lock is yet acquired
...

update regions_indexes
set current_resource = current_resource - ..., version_id = version_id + 1
where id = ... and resource_type = ...
returning version_id;

if new_version_id = old_version_id + 1 then
  -- success, commit
else 
  -- fail, rollback
end if;

但是乐观锁在高并发的情况下效果不好.当冲突并不罕见时，您将不得不频繁地重新启动事务.

But optimistic locking does not work well in situation of high concurrency. When conflicts are not rare, you will have to restart transactions frequently.

相关推荐