> Jonathan Gennick <[EMAIL PROTECTED]> wrote: > My concurrency issues probably boil down to the locking > business.
Purrcisely. > because they would both issue the SELECT before either one > got around to the UPDATE. I couldn't screw things up There you go. You are supposed to LOCK the row on the SELECT, then you grab the key and update it as fast as possible. That takes care of the "same key for 2 users" issue. Then you gotta solve the scalability probem. Here, two rules: 1- Make the lock period as small as possible. Only grab the key RIGHT before you're ready to commit the new row. NOT at the start of the transaction. 2- Spread the load. Put one row in the lock table for every 10000 key values, make each row into one block. Now, select in round-robin fashion from one of the rows. That spreads the load across rows/blocks in the lock table. All par for the course in databases where sequences a-la Oracle do not exist. Of course, if you use Oracle and you still do all this, you're asking for trouble... And I strongly disagree that a PK ever has to be consecutive (with no gaps). That is NOT a PK. That may be a unique key or some other business requirement, but it is most definitely not a PK. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Pinto do Souto INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
