473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

fetching unique pins...

Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
....
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 different unacquired pins?

Thanks for any help...

Feb 14 '06 #1
14 2140
Bobus wrote:
Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
....
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 different unacquired pins?


Place a Primary Key or Unique constraint on the PIN column. When a
duplicate error occurs generate a new PIN & try to save the new user row
again. Repeate until success.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Feb 14 '06 #2
Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1

Feb 14 '06 #3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Unless you're only using the PIN for a one-time operation - somewhere
you are going to save that PIN (in a table). That table is where you'd
put the Primary Key/Unique constraint.

I don't know what the LIMIT function does. If you want to just update
one row you'd indicate which row in the WHERE clause:

UPDATE table_name SET foo = bar WHERE foo_id = 25

foo_id would be a unique value.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/GTdYechKqOuFEgEQLJ/wCgxLHQiPaeDWXwsi5BxBpg6tlKmFoAn0tv
KM3PLa2qdl2KzW3Lp/XFHbiv
=gfzL
-----END PGP SIGNATURE-----
Bobus wrote:
Thanks, however, we do not generate the PINs ourselves. We simply
maintain the inventory of PINs which are given to us from a 3rd party.

Is there a way in SQL to update a single row ala the LIMIT function in
MYSQL? Something like:
update tablename set foo = bar limit 1

Feb 14 '06 #4
Maybe this

select top 1 ID, PIN from pin_table where acquired_by = <not acquired
value> (NOTE: this could be expensive if you use null to signify Not
Acquired, perhaps a non-null value with an index would help).

update pin_table set acquired_by = <acquired value> where ID = <ID from
select>

commit

--or --

set up one table containing the unused pins and one containing the used
pins

then
select top 1 ID, PIN from unused_pin
insert into used_pin values (ID, PIN)
delete from unused_pin where ID = ID

commit

Feb 14 '06 #5
I successfully used a transactional message queue for a similar
scenario.

Besides, try this:

create table #pins(id int identity, PIN decimal(10));
insert into #pins(PIN)values(1000000000);
insert into #pins(PIN)values(1000000001);
insert into #pins(PIN)values(1000000002);
go
create table #point_to_pins(id int identity)
go
---to get a PIN
insert into #point_to_pins default values
select @@identity

use @@identity to get the PIN, you will not get any collisions ever

Feb 14 '06 #6
On 13 Feb 2006 20:00:07 -0800, Bobus wrote:
Hi,

I have a table which contains a bunch of prepaid PINs. What is the
best way to fetch a unique pin from the table in a high-traffic
environment with lots of concurrent requests?

For example, my PINs table might look like this and contain thousands
of records:

ID PIN ACQUIRED_BY
DATE_ACQUIRED
....
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 different unacquired pins?

Thanks for any help...


Hi Bobus,

To get just one row, you can use TOP 1. Add an ORDER BY if you want to
make it determinate; without ORDER BY, you'll get one row, but there's
no way to predict which one.

If you expect high concurrency, you'll have to use the UPDLOCK to make
sure that the row gets locked when you read it, because otherwise a
second transaction might read the same row before the first can update
it to mark it acquired.

If you also don't want to hamper concurrency, add the READPAST locking
hint to allow SQL Server to skip over locked rows instead of waiting
until the lock is lifted. This is great if you need one row but don't
care which row is returned. But if you need to return the "first" row in
the queue, you can't use this (after all, the transaction that has the
lock might fail and rollback; if you had skipped it, you'd be processing
the "second" available instead of the first). In that case, you'll have
to live with waiting for the lock to be released - make sure that the
transaction is as short as possible!!

So to sum it up: to get "one row, just one, don't care which", use:

BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION
--
Hugo Kornelis, SQL Server MVP
Feb 14 '06 #7
Hugo Kornelis (hu**@perFact.REMOVETHIS.info.INVALID) writes:
BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK, READPAST)
WHERE Acquired_By IS NULL
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION

And to get "first row in line", use:

BEGIN TRANSACTION
SELECT TOP 1
@ID = ID,
@Pin = Pin
FROM PinsTable WITH (UPDLOCK)
WHERE Acquired_By IS NULL
ORDER BY Fill in the blanks
-- Add error handling
UPDATE PinsTable
SET Acquired_By = @User,
Date_Acquired = CURRENT_TIMESTAMP
WHERE ID = @ID
-- Add error handling
COMMIT TRANSACTION


Yet a variation is:

SET ROWCOUNT 1
UPDATE PinsTabel
SET @ID = ID,
@Pin = Pin
WHERE Acquired_By IS NULL
SET ROWCOUNT 0

It is essential to have a (clustered) index on Acquired_By.

Which solution that gives best performance it's difficult to tell.
My solution looks shorted, but Hugo's may be more effective.

Note also that if there is a requirement that a PIN must actually
be used, the transaction scope may need have to be longer, so in
case of an error, there can be a rollback. That will not be good
for concurrency, though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 14 '06 #8
Thanks for the responses everyone!

MGFoster: I was asking about the "limit" clause so that I could
implement a solution similar to what Erland recommended. This
guarantees no collisions.

Randy: in your solution, I believe there is a chance that two
concurrent requests will end up grabbing the same pin.

Alexander: clever! It's like an Oracle sequence. But, in our
particular case, we could have a problem of unused pins for
transactions which rollback.

Hugo: that should definintely do the trick.

Erland: yours too! I will try them both out.

Thanks for the help!

Feb 15 '06 #9
Bobus,

When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention

Feb 15 '06 #10
Alexander Kuznetsov (AK************@hotmail.COM) writes:
When I was solving a similar problem, I did try out the approaches
suggested by Hugo and Erland. I hate to say that, but I was always
getting a bottleneck because of lock contention on PinsTable. Maybe I
was missing something at that time. I had a requirement to produce
hundreds of PINs per minute at peak times, so I decided to allocate a
batch of PINs at a time, instead of distrributing them one at a time -
that took care of lock contention


Bobus said "But, in our particular case, we could have a problem of unused
pins for transactions which rollback."

This would call for a design where you get a start a transaction, get a
pin, use it for whatever purpose, and then commit. But as you say, you
will get contentions on the PINs here, although it's possible that READPAST
hint could help. I did some quick tests, and it seem to work.

The other option as you say is just to grab a PIN or even a bunch of
them. A later job would then examine which PINs that were taken, and
which were never used, and then mark the latter as unused.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 15 '06 #11
Alexander/Erland: thanks for the tips. I will let you know what issues
we run into, if any.

Best wishes.

Feb 16 '06 #12
Erland,

I think you are right. I looked up that project, and in fact I did not
try READPAST at all. Having observed poor performance, I implemented
batches, which reduced amount of database calls and as a side effect
took care of lock contention. I immediately got good performance and
did not drill down any furhter.

Feb 18 '06 #13
Erland's solution seems to work great from our initial tests!

Unrelated question, and probably should be another thread, but have any
of you SQL Server geniuses tried PostgreSQL? Any comments, positive or
negative?

Feb 20 '06 #14
In 25 words or less: It is nice but has the feel of a college project
where grad students kept addinf things to it based on the last academic
fad or thesis topic. I would go with Ingres, which has a "commercial
feel" and a great optimizer.

Feb 20 '06 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: EN | last post by:
Hi, I'm trying to send 8 bits to the lpt port. I'd like to use VB 6.0. Can this be done? VB can send data to a printer, but can it go as low as actually sending bits? I think VB can only send...
9
by: Rolf Kemper | last post by:
Dear Experts, I got stuck with the following problem and need your help. What I wnat to do is to get a set of distinct nodes. Before the distinct I have selected the multiple occourences...
6
by: eson | last post by:
hi i would like to make some software for steering devices through lpt port therefore i need to know who to control pins on the lpt port and how to gain direct access to them. Shortly speaking I...
2
by: Jose | last post by:
Hello: I'm trying to use a serial port to activate a leds panel playing three lines. I decided to do this way: one to send the data (RTS), another one to use it as a data clock (DTR) and another...
13
by: jay.dow | last post by:
I want to write to the pins of an RS232 without using the serial protocol. The use would be every pin could act to complete a circuit in customized hardware. I could use python to communicate...
0
by: Piotrekk | last post by:
Hi My question is: How to conrol output pins of ( for example ) COM1 I found in .net System.IO.Ports.SerialPort class. Is there any posibility to set or not set pins D0-7? Thanks PK
2
by: Florian Albrecht | last post by:
Hi there, as I am trying to write a recordset class, I am interested in fetching field information from an oracle db. Several information on the fields are very simple to request. So I already...
0
by: JNL via DotNetMonster.com | last post by:
hi guys! i need help on how to read signals from pins 10 and 12 of the parallel port do i need to add a reference or a COM component to read signals from the pins? all i need to do is get a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.