Re: Inserting rows into master table from multiple threads

On Tue, 18 Dec 2007 14:42:23 -0800 (PST), 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

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. Received on Thu Dec 20 2007 - 08:48:13 CST