Re: Inserting rows into master table from multiple threads

sameergn_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

This is an Oracle usenet group. Nothing you've written ha anything to do with Oracle: Especially the "lock master_table." Oracle is not a Microsoft product.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org