On Dec 19, 1:01 am, 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
I'd opt for option 2. Option 1 is going to lock the table every time
there's an insert. Option 2 is only going to cause delays when two
processes try to insert the same name at the same time.
Received on Wed Dec 19 2007 - 09:58:54 CST
