In response to "Bobus" <roblocke@xxxxxxxxx>: > 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.
