From: Bobus <roblocke@gmail.com>
To: pgsql-sql@postgresql.org
Subject: fetching unique pins in a high-transaction environment...
Date: 29 Oct 2006 08:36:48 -0800
Message-ID: <1162139808.031657.23340@f16g2000cwb.googlegroups.com> (raw)
Hi,
I posted this question to the "general" forum, but then discovered this
one which I think is more appropriate. Apologies for the cross-post.
We are in the process of porting an application from SQL Server to
PostgresQL.
We have a table which contains a bunch of prepaid PINs. What is the
best way to fetch the next available unique pin from the table in a
high-traffic environment with lots of concurrent requests?
For example, our PINs table might look like this and contain thousands
of records. (FYI, the PIN numbers are generated by a third party and
loaded into the table):
ID PIN USED_BY DATE_USED
....
100 1864678198
101 7862517189
102 6356178381
....
10 users request a pin at the same time. What is the easiest/best way
to ensure that the 10 users will get 10 unique pins, while eliminating
any waiting?
SQL Server supports the notion of a SELECT FOR UPDATE with a READPAST
hint which tells SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This guarantees a unique pin will be
acquired every time without hampering performance.
Is there any equivalent in Postgres?
Any help would be greatly appreciated...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roblocke@gmail.com
Subject: Re: fetching unique pins in a high-transaction environment...
In-Reply-To: <1162139808.031657.23340@f16g2000cwb.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox