Inserting rows into master table from multiple threads

From: <sameergn_at_gmail.com>
Date: Tue, 18 Dec 2007 14:42:23 -0800 (PST)
Message-ID: <4151ccc2-f92d-4c47-916d-b254dc3e6b23@b40g2000prf.googlegroups.com>

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 Received on Tue Dec 18 2007 - 16:42:23 CST