Re: why perl dbi deadlock oracle?

This may help:

http://osi.oracle.com/~tkyte/unindex/index.html

<dominica_at_secondhat.com> wrote in message news:aetp2v$amo$1_at_secondhat.secondhat.com...
> Hi All,
>
> Very curious why the following code cause oracle to
> deadlock every 2 days.
> This database is very very active like 500 + transaction per
> second.
>
> Thanks in advance.
>
> Dom
>
> ============
>
> sub deleteEventsByInsignificance {
> my $over_count_by_type = shift;
> my $under_msgs_impacted = shift;
> &checkEventsDB ();
>
> deadlock is here-->

$deleteEventsByInsignificance->execute($over_count_by_type, $under_msgs_impacted) || &$errorHandler ( $DBI::errstr );
> }
>
> $deleteEventsByInsignificance = $main::dbhe->prepare("
> update event
> set iid = iid * -1
> where exists (
> select
> null
> from
> (
> select
> e2.iid as prg_iid
> , e2.code as prg_code
> from
> event e2
> group by
> e2.iid
> , e2.code
> having
> count(*) > ?
> ) prg
> , event e1
> where
> event.iid = e1.iid
> and e1.iid = prg_iid
> and e1.code = prg_code
> and (nvl(e1.block,0) + nvl(e1.blackhole,0) + nvl(e1.spam,0)) < ?
> and nvl(e1.spool,0) = 0
> and end < ((sysdate - to_date('01-JAN-1970')) * 86400)
> )
> and event.iid > 0"
> ) || &$errorHandler ( 'DB Fail::prepare failed' );
>
>
>
Received on Fri Jun 21 2002 - 10:07:35 CDT