why perl dbi deadlock oracle?

From: <dominica_at_secondhat.com>
Date: 20 Jun 2002 23:39:25 GMT
Message-ID: <aetp2v$amo$1@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 Thu Jun 20 2002 - 18:39:25 CDT