Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id hA6HfeR11922 for ; Thu, 6 Nov 2003 11:41:40 -0600 X-ClientAddr: 66.27.56.212 Received: from www3.fatcity.com (rrcs-west-66-27-56-212.biz.rr.com [66.27.56.212]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id hA6Hfec11917 for ; Thu, 6 Nov 2003 11:41:40 -0600 Received: (from root@localhost) by www3.fatcity.com (8.11.6/8.11.6) id hA6E0SW10349 for oracle-l@orafaq.net; Thu, 6 Nov 2003 06:00:28 -0800 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D5D4A; Thu, 06 Nov 2003 05:59:25 -0800 Message-ID: Date: Thu, 06 Nov 2003 05:59:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Jonathan Gennick Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Jonathan Gennick Subject: Re[2]: How do you genrate primary keys? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale (hkchital@singnet.com.sg) wrote: HKC> 1. Hit a table that keeps a counter. HKC> Used to be a mechanism in the Oracle5 days [If I remember correctly, HKC> Sequences came in Oracle6]. Issues were with locking the single HKC> record used as the generator or scanning for the max(value) of the HKC> key. HKC> Not quite sure I understand how you encountered concurrency issues, though. My concurrency issues probably boil down to the locking business. The app I'm thinking of originally did something like: SELECT counter INTO :1 FROM counter_table WHERE counter_name = 'table name'; ...some app code goes here... UPDATE counter_table SET counter := counter+1 WHERE counter_name = 'table name'; Well, it all worked fine in single-user mode. But it was easy enough for me to sit down in front of two computers, create two new records, press SAVE at the same time, and cause two sessions to grab the same key value, because they would both issue the SELECT before either one got around to the UPDATE. I couldn't screw things up consistently, but just by hitting the SAVE button at the same time I could screw things up often enough to make the problem obvious. Maybe there's a way to lock the table, to make the above approach work. In my case, I didn't bother trying to find that solution. Once I did my little demo, it was easy enough to convince the project manager that we should switch to using Oracle sequences. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request@gennick.com and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: jonathan@gennick.com 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: ListGuru@fatcity.com (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).