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.
> [EMAIL PROTECTED]
>
> -----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
