Thread: fetching unique pins in a high-transaction environment...
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. Is there any equivalent in Postgres? Any help would be greatly appreciated...
I think we've figured out a way to implement the equivalent of a
READPAST hint in a function.
The basic idea is to loop until we find the next available unlocked
row, using the lock_not_available exception to determine if the record
is locked or not. Our early testing seems to indicate that this
solution will work, but we would love to hear about simpler and more
efficient ways to accomplish this.
Here's a simplified version of the function which illustrates the
principle:
CREATE OR REPLACE FUNCTION "getpin"() RETURNS varchar
as $$
DECLARE
v_id integer := 0;
v_pin varchar;
BEGIN
LOOP
BEGIN
-- Find the first available PIN.
-- Note: we cannot lock down the row here since we need to be
-- able to store the ID of the pin to implement the READPAST.
select id into v_id from pins where id > v_id and used = 0
order by id limit 1;
-- Exit if there are no PINs available.
IF NOT FOUND THEN
RAISE EXCEPTION 'no pins available';
END IF;
-- Lock down the PIN. If another transaction beat us to it, we
-- trap the error (see below) and loop looking for the next
-- available pin. If another transaction already updated
-- used to 1 in between this select and the previous, then we
-- loop (see ELSE statement).
select pin into v_pin from pins where id = v_id and used = 0
for update nowait;
IF FOUND THEN
-- Update the PIN. The used = 0 check is unnecessary,
-- but better safe than sorry.
update pins set used = 1 where id = v_id and used = 0;
-- I don't think this should ever happen.
IF NOT FOUND THEN
RAISE EXCEPTION 'this should never happen';
END IF;
RETURN v_pin;
ELSE
-- Somebody snuck in and updated/grabbed the pin. Loop.
END IF;
EXCEPTION WHEN lock_not_available THEN
-- Loop looking for the next available unlocked pin.
END;
END LOOP;
END;
$$
language plpgsql;
Thanks...
On Sun, Oct 29, 2006 at 08:32:12AM -0800, Bobus wrote: > 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? What are you doing that holds locks for so long? If you do a select for update, take the first row, update and commit, you should be able to handle dozens of those per second. In any case, another approach I've seen is to divide the list into several. For example, make your query do a: select for update <blah> where pin > 'X' where X is a random number between 0 and 9. That cuts the amount of contention dramatically, so you can use the simple method. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> 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... if your pin is a kind of auto-incremented number, then postgresql equivalent functionality is sequences or the pseudo type serial (they are really the same thing). http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE-SERIAL Regards, Richard Broersma Jr.
In response to "Bobus" <roblocke@gmail.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.
> That second query may not be the best, as it will probably seqscan and > grab all the pins before only returning the first one ... A partial index where USED_BY is null would eliminate the need for the seqscan on the table... -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice