Re: Concurrency question

From: Tim X <timx_at_spamto.devnul.com>
Date: 09 Jun 2003 12:51:10 +1000
Message-ID: <87u1b0arq9.fsf@tiger.rapttech.com.au>

>>>>> "John" == John Bossert <jbossert_at_usa.net> writes:

 John> I have a table of unique numbers, call them PINs.  Once a PIN
 John> has been assigned to a user, it needs to be unavailable to
 John> other users.

 John> I have a query which identifies unallocated PINs.  However, I
 John> don't know the best way to avoid the situation where two

 John> transactions could grab the same PIN, if a second transaction  John> began before the first transaction committed.

 John> Is there a convention/best practice for handling this scenario?
 John> I'm aware of SERIALIZABLE, but am concerned about the potential
 John> impact on throughput.

 John> Oracle 8.1.7.3.0 and 9.2.0.3.0 on Solaris 8. Client  John> application is Tomcat 4.1 and java servlets.

 John> Pointers appreciated.

 John> -- John Bossert

 John> In what concerns you much, do not think that you have  John> companions: know that you are alone in the world.

 John> -- Henry David Thoreau

When dealing with unique number requirements, my first choice is to use sequences. However, if you have a situation where you do not want any gaps in your sequences, then another (usually less optimal) solution is needed. Therefore, the first thing I'd do is determine if you really must re-use old PINS and whether you cannot have gaps.

One possible solution, off the top of my head and which would need refinement would be to do this.

1. Make pin+pintype a unique index.
2. Have a sequence to generate unique pin numbers - used when you really need a unique number
3. Have a procedure which looks for missing/unused pin values
4. Have an exception handler in a procedure which adds pin record entries. If an insert fails because it is not unique, then grab a new pin from the sequence and insert it.

The algorithm would be something like -

- Get pin number

---- If no pin numbers in table,

-------- return SEQUENCE.NEXTVAL

---- else if exists unused/missing pin

-------- return first unused/missing pin

---- else

-------- return SEQUENCE.nextval
- Insert pin

---- If insert successful

--------return

---- else if non-unique value exception

-------- Get SEQUENCE.nextval
-------- insert pin

---- else some other error

-------- do error handling

The idea I'm trying to express here is that first you try to re-use missing/unused pins, but if that fails get a new pin even if there may be other unused pins available. This will mean that

1. You can guarantee uniqueness of pin+pintype (via unique index)
2. The system is "biased" towards re-using unallocated/missing pins
3. You would avoid a possible race condition during high transaction loads where a transaction keeps failing with a non-unique key because each time it attempts to re-use an unallocated id, it is beaten to the insert by another process which has also grabbed that ID. (unlikely, but I guess possible).

Tim

-- 
Tim Cross
The e-mail address on this message is FALSE (obviously!). My real e-mail is
to a company in Australia called rapttech and my login is tcross - if you 
really need to send mail, you should be able to work it out!

Received on Sun Jun 08 2003 - 21:51:10 CDT