Re: Concurrency question

"John Bossert" <jbossert_at_usa.net> wrote in message news:3EE37324.7090604_at_usa.net...
> I have a table of unique numbers, call them PINs. Once a PIN has been
assigned
> to a user, it needs to be unavailable to other users.
>
> I have a query which identifies unallocated PINs. However, I don't know
the
> best way to avoid the situation where two transactions could grab the same
PIN,
> if a second transaction began before the first transaction committed.
>
> Is there a convention/best practice for handling this scenario? I'm aware
of
> SERIALIZABLE, but am concerned about the potential impact on throughput.
>
> Oracle 8.1.7.3.0 and 9.2.0.3.0 on Solaris 8. Client application is Tomcat
4.1
> and java servlets.
>
> Pointers appreciated.

You can only lock data to prevent other processes from updating, so in your case the obvious solution of using locks won't work.

You need to change your method of allocating the PIN, for example you could create a sequence and use it to index your next unused PIN. This will guarantee no two processes will try allocating the same one. Received on Sun Jun 08 2003 - 13:52:01 CDT