Re: SQL question

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Mon, 24 Feb 2003 13:52:44 -0800
Message-ID: <F001.00557911.20030224135244@fatcity.com>

Rick Stephenson wrote:
>
> Sorry, I guess I could have been a little more clear.
>
> Another example:
>
> Table Employee:
> Emp_id number primary key -- generated with a sequence
> Emp_name varchar2(20) unique
>
> Table Employee_log:
> Emp_id number primary key
> Time_stamp date primary key
> Emp_stats varchar2(50)
>
> A process receives the employee name, and other information that needs to be
> stored in the table employee_log. The process needs to retrieve the emp_id
> from the employee table, so it does a lookup. If the employee exists, the
> emp_id is retrieved and the information is then inserted into the
> employee_log table. If the employee does not currently exist, a new
> employee is added to the table employee.
>
> We run into problems when we have many concurrent processes running and more
> than one process receives the same employee name. They both do a lookup and
> they both conclude the employee does not exist. Thus, they both try and do
> an insert into the employee table. One will succeed and the other will
> fail.
>
> Is there away to avoid this scenario?
>
> I hope I made this a little clearer.
>
> Thanks,
>
> Rick Stephenson
>
> -----Original Message-----
> Sent: Monday, February 24, 2003 1:05 PM
> To: Multiple recipients of list ORACLE-L
>
> Rick - What about selecting the primary key for your table from a sequence?
> Oracle will ensure each session receives a unique number.
>
> What is your overall goal?
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Monday, February 24, 2003 1:50 PM
> To: Multiple recipients of list ORACLE-L
>
> OS: Solaris 2.8
>
> Database: Oracle 9.2.0.2
>
>
>
> Situation in chronological order
>
> Connection A: select * from table A where id = 1; Result: no rows returned
> -- This means I need to insert the row, as it does not exists yet.
>
> Connection B: select * from table A where id = 1; Result: no rows returned
> -- This means I need to insert the row, as it does not exists yet.
>
> Connection A: insert into table A(id) values = 1; Result: 1 row inserted
>
> Connection B: insert into table A(id) values = 1; Result: Unique constraint
> violated -- This is the problem. How do I avoid this happening?
>
>
>
> Question: How can I force connection B to wait for connection A to insert
> the new row before it does the select?
>
>
>
> If I were updating the row, I could use the "for update" clause to force the
> wait. Is there a clean way to do that for an insert?
>
>
>
> Thanks for your help,
>
>
>
> Rick Stephenson

Rick,

 Given what I currently know of the state of the economy, I guess that the insert will be a fairly rare occurrence ? I think that therefore locking the employee table in exclusive mode is acceptable ?

I'd rather code something along the following lines :

   done := false;
   while not done
   loop

     insert into employee_log
     select emp_id, sysdate, your_data_here
     from employee
     where emp_name = input_name;
     if (sql%rowcount = 0)
     then
       begin
         lock table employee in exclusive mode nowait;
         insert into employee yadda yadda
         done :=true;
       exception
         when table_already_locked then null;
       end;
     else
       done := true;
     end if;

    end loop;
    commit;

   May be a bit hard on CPU; perhaps that adding a short pause when the 'table already locked by another session' exception is hit would be the thing to do. Depends on how intensive all this is.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Feb 24 2003 - 15:52:44 CST