Re: Multiple Concurrent Updates of Shared Resource Counter - Mailing list pgsql-performance

On Thu, Jun 7, 2012 at 9:53 AM, Nir Zilberman <nirz@checkpoint.com> wrote:
> We are handling multiple concurrent clients connecting to our system -
> trying to get a license seat (each license has an initial capacity of
> seats).
> We have a table which keeps count of the acquired seats for each license.
> When a client tries to acquire a seat we first make sure that the number of
> acquired seats is less than the license capacity.
> We then increase the number of acquired seats by 1.
>
> Our main problem here is with the acquired seats table.
> It is actually a shared resource which needs to be updated concurrently by
> multiple transactions.
>
> When multiple transactions are running concurrently - each transaction takes
> a long time to complete because it waits on the lock for the shared resource
> table.
>
> Any suggestions for better implementation/design of this feature would be
> much appreciated.

Well, there are the usual suspects for lock contention

1. Reduce time a lock needs to be held.
2. Increase granularity of locking.

ad 1)

It sounds as if you need two statements for check and increase.  That
can easily be done with a single statement if you use check
constraints.  Example:

$ psql -ef seats.sql
drop table licenses;
DROP TABLE
create table licenses (
  name varchar(200) primary key,
  max_seats integer not null check ( max_seats >= 0 ),
  current_seats integer not null default 0 check ( current_seats >= 0
and current_seats <= max_seats )
);
psql:seats.sql:6: NOTICE:  CREATE TABLE / PRIMARY KEY will create
implicit index "licenses_pkey" for table "licenses"
CREATE TABLE
insert into licenses (name, max_seats) values ('foo', 4);
INSERT 0 1
update licenses set current_seats = current_seats + 1 where name = 'foo';
UPDATE 1
update licenses set current_seats = current_seats + 1 where name = 'foo';
UPDATE 1
update licenses set current_seats = current_seats + 1 where name = 'foo';
UPDATE 1
update licenses set current_seats = current_seats + 1 where name = 'foo';
UPDATE 1
update licenses set current_seats = current_seats + 1 where name = 'foo';
psql:seats.sql:12: ERROR:  new row for relation "licenses" violates
check constraint "licenses_check"
update licenses set current_seats = current_seats - 1 where name = 'foo';
UPDATE 1

The increase will fail and you can react on that.  Another scheme is to use

update licenses set current_seats = current_seats + 1
where name = 'foo' and current_seats < max_seats;

and check how many rows where changed.

If however your transaction covers increase of used license seat
count, other work and finally decrease used license seat count you
need to change your transaction handling.  You rather want three TX:

start TX
update licenses set current_seats = current_seats + 1 where name = 'foo';
commit

if OK
  start TX
  main work
  commit / rollback

  start TX
  update licenses set current_seats = current_seats - 1 where name = 'foo';
  commit
end

ad 2)
At the moment I don't see a mechanism how that could be achieved in
your case.  Distribution of counters of a single license across
multiple rows and checking via SUM(current_seats) is not concurrency
safe because of MVCC.

Generally checking licenses via a relational database does neither
seem very robust nor secure.  As long as someone has administrative
access to the database or regular access to the particular database
limits and counts can be arbitrarily manipulated.  License servers I
have seen usually work by managing seats in memory and counting
license usage via network connections.  That has the advantage that
the OS quite reliably informs the license server if a client dies.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/