postgresql - Optimistic locking queue -

i'm writing application in node.js using postgresql database. have problems. have table information 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) ) 

users clicks on button, application calculates various parameters based on current_resource , current_resource - $calc_value. because it's maybe concurently use transactions. in process calculation maybe errors, , need repeat calculations. i'm using select ... update locking row using current_resource. how can no lock using optimistic locking, if current value of current_resource important, , user clicks first should use max. avalaible current_resource. in other words, should implement acess queue current_resource.

for optimistic locking need define means check if row has changed since saw last time. example, lets add identifier:

alter table regions_indexes add version_id integer default 1 not null; 

now application reads row, shows data user , waits until button clicked. must remember value of version_id got.

after button clicked, perform necessary calculations. when you're ready update row, lock row , check whether version_id has not changed. if has not, increment version_id , commit. if has, bad luck --- need tell user repeat operation because outrun him.

it may looks (in pseudocode):

-- remember version_id select * regions_indexes id = ... , resource_type = ...;  -- wait user click -- can wait long time, because no lock yet acquired ...  update regions_indexes set current_resource = current_resource - ..., version_id = version_id + 1 id = ... , resource_type = ... returning version_id;  if new_version_id = old_version_id + 1   -- success, commit else    -- fail, rollback end if; 

but optimistic locking not work in situation of high concurrency. when conflicts not rare, have restart transactions frequently.