Re: fetching unique pins in a high-transaction

In response to "Bobus" <roblocke(at)gmail(dot)com>:

> Hi,
>
> 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.

I'm assuming your USED_BY column can be used to find the pin again?

UPDATE pins SET date_used='whatever', used_by='whatever'
WHERE pin = (SELECT FOR UPDATE pin FROM pins WHERE used_by IS NULL LIMIT 1);

If my assumption that each user will only have 1 pin is correct, you
can then do subsequent queries to find out what PIN was used. Otherwise,
you might need to get a little more creative.

That second query may not be the best, as it will probably seqscan and
grab all the pins before only returning the first one ...

--
Bill Moran
Collaborative Fusion Inc.