strange deadlock error from Perl DBI

Hi,

I am executing a Perl script that performs a bunch of inserts into several different tables via PL/SQL stored procedures. It gets in a deadlock "with itself".

The behavior is intermittent, but once it happens, it continues to happen until I do an insert by hand (or from another, slightly different Perl script) into the table causing the deadlock error.

The offending PL/SQL block is:

DECLARE

    p_chip_type_id chip_type.chip_type_id%type := '';
    p_chip_lot chips.lot_number%type := 'xxx';
    v_chip_ID chips.chip_ID%type;

• third, insert new rows to chips table commit; lock table chips in exclusive mode;

    select chip_seq.nextval into v_chip_ID from dual;     insert into chips

        (chip_ID, chip_type, lot_number, manuf)     values

        (v_chip_ID, p_chip_type_ID, p_chip_lot, 1);     ...

The insert into chips line causes the deadlock error. I added the commit and lock statements just for kicks, they don't change the behavior. I tried disabling all referential constraints on Chips as well.

Error inserting chip: $@='[Tue Oct 12 13:41:12 1999] upload-test.pl: DBD::Oracle::st execute failed: ORA-04020: deadlock detected while trying to lock object XDB.CHIPS
[Tue Oct 12 13:41:12 1999] upload-test.pl: ORA-06512: at line 16 (DBD ERROR: OCIStmtExecute) at
/usr/local/etc/httpd/cgi-bin/young/peter/upload-test.pl line 881. ', errstr=ORA-04020: deadlock detected while trying to lock object XDB.CHIPS
ORA-06512: at line 16 (DBD ERROR: OCIStmtExecute)

The trace file is below. I find the fact that the session IDs for both transactions are the same especially intriguing.

Any thoughts?

Regards,
Peter

trace:

• 1999.10.12.14.02.30.140
• SESSION ID:(21.4187) 1999.10.12.14.02.30.140 A deadlock among DDL and parse locks is detected. This deadlock is usually due to user errors in the design of an application or from issuing a set of concurrent statements which can cause a deadlock. This should not be reported to Oracle Support. The following information may aid in finding the errors which cause the deadlock: ORA-04020: deadlock detected while trying to lock object XDB.CHIPS

  ---

  object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- 29bd294 24d35d8 276cf14 X 24d35d8 276d478 S

  ---

  • DUMP OF WAITING AND BLOCKING LOCKS ----------

    ---

    • WAITING LOCK -------------

      ---

      SO: 276cf14, type: 23, owner: 27000e0, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=276cf14 handle=29bd294 request=X call pin=0 session pin=0 user=24d35d8 session=24d35d8 count=0 flags=[00] savepoint=147614 LIBRARY OBJECT HANDLE: handle=29bd294 name=XDB.CHIPS hash=c1228974 timestamp=08-31-1999 14:40:26 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-051d-051d lock=S pin=S latch=0 lwt=29bd2ac[276cf24,276cf24] ltm=29bd2b4[29bd2b4,29bd2b4] pwt=29bd2c4[29bd2c4,29bd2c4] ptm=29bd31c[29bd31c,29bd31c] ref=29bd29c[2af4a38,2fff00c] LIBRARY OBJECT: object=316c0bc type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 2afa240 316c140 I/P/A 0 NONE 2 2ecac54 2d6a498 I/P/A 1 NONE 3 2ecad14 2f5bf20 I/-/A 0 NONE 4 2ecabf4 2c6c1bc I/-/A 0 NONE 8 316c24c 2f32a74 I/-/A 0 NONE 10 2ecacb4 302445c I/-/A 0 NONE
    • BLOCKING LOCK ------------

      ---

      SO: 276d478, type: 23, owner: 253dec0, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=276d478 handle=29bd294 mode=S call pin=2aa1d1c session pin=0 user=24d35d8 session=24d35d8 count=4 flags=PNC/[04] savepoint=147312 LIBRARY OBJECT HANDLE: handle=29bd294 name=XDB.CHIPS hash=c1228974 timestamp=08-31-1999 14:40:26 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-051d-051d lock=S pin=S latch=0 lwt=29bd2ac[276cf24,276cf24] ltm=29bd2b4[29bd2b4,29bd2b4] pwt=29bd2c4[29bd2c4,29bd2c4] ptm=29bd31c[29bd31c,29bd31c] ref=29bd29c[2af4a38,2fff00c] LIBRARY OBJECT: object=316c0bc type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 2afa240 316c140 I/P/A 0 NONE 2 2ecac54 2d6a498 I/P/A 1 NONE 3 2ecad14 2f5bf20 I/-/A 0 NONE 4 2ecabf4 2c6c1bc I/-/A 0 NONE 8 316c24c 2f32a74 I/-/A 0 NONE 10 2ecacb4 302445c I/-/A 0 NONE

        ---

        This lock request was aborted.

System:

I am running Oracle 8.0.5.0.0 Enterprise on NT 4.0 servicepack 4, I am accessing the server from Perl DBI 1.13 and DBD::Oracle 1.03 on Solaris 5.6. Received on Tue Oct 12 1999 - 16:15:37 CDT