Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx02.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail
Date: Wed, 19 Dec 2007 08:45:37 -0800
From: DA Morgan <damorgan@psoug.org>
Organization: Puget Sound Oracle Users Group
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: Inserting rows into master table from multiple threads
References: <5b27a260-2913-40b0-9c56-4b0e69a60a4b@e10g2000prf.googlegroups.com>
In-Reply-To: <5b27a260-2913-40b0-9c56-4b0e69a60a4b@e10g2000prf.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <1198082723.563975@bubbleator.drizzle.com>
Cache-Post-Path: bubbleator.drizzle.com!unknown@216.162.218.178
X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/)
Lines: 82
X-Complaints-To: abuse@csolutions.net
Xref: usenetserver.com comp.databases.oracle.misc:251076
X-Received-Date: Wed, 19 Dec 2007 11:45:26 EST (text.usenetserver.com)

sameergn@gmail.com wrote:
> Hi,
> 
> We have a master table, say with two columns name (varchar) and id
> (number) and a stored procedure insert_name_details(name, other
> details).
> 
> SP will first check if name is present in master table or not. If
> present, it will get the id otherwise it will insert a row in master
> table with a new sequence number and use that id in other tables as
> foreign key. So its basically an atomic "get or create-if-not found"
> operation.
> 
> There are two ways of handling concurrency here.
> Option 1
> -------------
> select id from master_table;
> if (not_found)
> {
>     lock master_table;
>     (again) select id from master_table;
>     if (still_not_found)
>     {
>         insert record in master table;
>     }
> 
> }
> 
> Option 2
> ------------
> select id from master_table;
> if (not_found)
> {
>     try
>     {
>         insert record in master table;
>     }
>     catch (PrimaryKeyVIolationException)
>     {
>         select id from master_table;
>     }
> 
> }
> 
> Both these options are for stored procedure implementation although
> code is written is Java style.
> 
> Which method is preferred? Locking table or catching exceptions? I
> know that using exceptions  should not be used to drive logical flow,
> but it makes life simpler for a programmer vs. locking table. Also
> locking tables means holding resources from other threads, but table
> will be locked for a brief amount of time i.e. till master row is
> inserted.
> 
> Any comments, suggestions or alternatives?
> 
> Thanks,
> Sameer

Neither of these is a good idea.

First of all this is Oracle not SQL Server so the data type is not
VARCHAR. A strong clue that you are not dealing with Oracle as someone
who understands the way it works.

Second what is the form of your primary key? If it is a surrogate key
it should be generated by a sequence object making a collision impossible.

Third, assuming a natural key and a possible collision which is more
likely? A duplicate or a non-duplicate? Assuming some competence in
system design a duplicate is highly unlikely so you should just to the
insert and trap the rare exception.

Again, as at OTN where you posted the exact same question, it appears
you think Oracle is SQL Server ... it is not.
-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org