Re: Inserting rows into master table from multiple threads

On Dec 19, 8:45 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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
>
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel,

This code is for Oracle 10g database. I meant varchar2, but wrote varchar just to keep it generic, pseudo-code like, to indicate a character columns. Anyway, that was just an example and not real columns anyway.

I have seen "select ... for update" construct when you want to atomically get and update a row, but was not sure what is the recommended approach for inserting a new master row in a multithreaded  environment.

The key will be generated by a sequence object.

Thanks,
Sameer Received on Wed Dec 19 2007 - 23:20:45 CST