On Dec 20, 6:48 am, Brian Tkatch <N/A> wrote:
> On Tue, 18 Dec 2007 14:42:23 -0800 (PST), samee..._at_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
>
> To sum it up, there are two cases. It is there, and it isn't there.
>
> Actions taken:
>
> It isn't there: 1) INSERT it. 2) Get the id.
> It is there: 1) Get the id.
>
> If it isn't there yet, you want to INSERT it. In both cases, you want
> to get the id.
>
> So:
>
> -- INSERT it if it isn't there.
> INSERT INTO master_table SELECT record FROM DUAL
> WHERE NOT EXISTS(SELECT * FROM master_table WHERE record = 'record');
>
> -- Now get the id, whether it was already there, or we just added it.
> SELECT Id FROM master_table WHERE record = 'record';
>
> B.
Thanks to all for your replies. After posting to oracle.server I
realized that this group is specific to server related question, and
my question is related to PL/SQL code, hence posted it to oracle.misc.
Received on Thu Dec 20 2007 - 12:29:34 CST
