From: =?iso-8859-15?q?C=E9dric_Villemain?= <cedric@2ndquadrant.com>
Reply-To: cedric@2ndquadrant.com
Organization: 2ndQuadrant
To: pgsql-performance@postgresql.org
Subject: Re: Multiple Concurrent Updates of Shared Resource Counter
Date: Fri, 8 Jun 2012 12:59:53 +0200
User-Agent: KMail/1.13.7 (Linux/3.2.0-2-amd64; KDE/4.7.4; x86_64; ; )
Cc: Nir Zilberman <nirz@checkpoint.com>
References: 
 <A305A29309C1274FAA7A0FED4F1F75666EBC3CD433@il-ex01.ad.checkpoint.com>
In-Reply-To: 
 <A305A29309C1274FAA7A0FED4F1F75666EBC3CD433@il-ex01.ad.checkpoint.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart3159809.0WS9EmNGKK";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <201206081259.57036.cedric@2ndquadrant.com>

--nextPart3159809.0WS9EmNGKK
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: quoted-printable

Le jeudi 7 juin 2012 09:53:48, Nir Zilberman a =E9crit :
> Hi.
>=20
> 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.
>=20
> 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.
>=20
> 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.
>=20
> Any suggestions for better implementation/design of this feature would be
> much appreciated.

maybe you can manage something around UNIQUE (license_id,license_seat_numbe=
r).

It depends of what you achieve, and the tables structures you have.
=2D-=20
C=E9dric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - D=E9veloppement, Expertise et Formation

--nextPart3159809.0WS9EmNGKK
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)

iQEcBAABAgAGBQJP0dspAAoJENsgH0yzVBebEhEH/0Av0FnTU+/e7lOUErQHZ2zK
QZHTrSBXTt8cftBmEtJEffOFFoaLd33j5CWF5oHp1BXJLDxp7VkVs6vF81PAruSs
BHH+/65fCFXfStgdj60hXzykO3a0QWJSZKw5gCCbqRWngaY76To0zMvvl4GEiJJk
D/seGzlXjybBDCnmEbDi/HRhthlwHqENgGzN54ltBd+jLTXbJ0c2pY0VcAyc31bU
e2UrBGQzXUJtmNUxNLOxhoccozir/y6w/aqWsKT5NtegmcR6SPMgftL1DOw1/bsL
bUGH621WU196mcetCDxh+qfGf21zeXuD8xbeU07kw8HgYHE8DooUiqe2NQmnTlI=
=e+7h
-----END PGP SIGNATURE-----

--nextPart3159809.0WS9EmNGKK--