Re: Inserting rows into master table from multiple threads

On Dec 18, 5:42 pm, 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

As Daniel remarked the design is not appropriate for an Oracle database. First any task design that requires a lock on the entire table will not scale. Just insert the row using a sequence for the Id and a returning clause to return the ID to the application for use in inserting children.

A PK constraint, UK constraint, or just a unique index on the name would prevent duplicates.

Also unless the name value is subject to change you do not need the ID column but can just use the natural key, name (depending on what this actually represents).

HTH -- Mark D Powell -- Received on Wed Dec 19 2007 - 08:52:37 CST