ORA-04020: deadlock detected while trying to lock object FIT

From: Jim Poe <jpoe_at_fulcrumit.com>
Date: 05 Nov 2001 16:35:01 GMT
Message-ID: <9s6f3l$hvq@dispatch.concentric.net>

This seems to be related to my original problem. I noticed that this error showed up in our trc files when we added the column to the table. I dropped and then added the column this morning and got the same error in the trc. How would I interpret this. If a lock is required to add the column and the lock attempt is aborted, why do I continue to see the column in the table?

ORA-04020: deadlock detected while trying to lock object FIT_DBA.OFFICE

  object   waiting  waiting       blocking blocking
  handle   session     lock mode   session     lock mode

-------- -------- -------- ---- -------- -------- ---- 54b6734 27328b0 32e1f6c X 27328b0 32e18dc X

DUMP OF WAITING AND BLOCKING LOCKS ----------
- WAITING LOCK -------------
  SO: 32e1f6c, type: 35, owner: 3255184, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=32e1f6c handle=54b6734 request=X call pin=0 session pin=0 user=27328b0 session=273311c count=0 flags=[00] savepoint=0 LIBRARY OBJECT HANDLE: handle=54b6734 name=FIT_DBA.OFFICE hash=4e819ede timestamp=11-02-2001 12:12:02 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000] kkkk-dddd-llll=0000-051d-051d lock=X pin=X latch=0 lwt=54b674c[32e1f7c,32e1f7c] ltm=54b6754[54b6754,54b6754] pwt=54b6764[54b6764,54b6764] ptm=54b67bc[54b67bc,54b67bc] ref=54b673c[54b673c,54b673c] lnd=54b67c8[54b6698,54c08bc] DEPENDENCY REFERENCES: reference latch flags
  - ----- ------------------- 5256d20 0 DEP/INV[05] 4f91fec 0 DEP/INV[05] 4d24370 0 DEP/INV[05] 4c6491c 0 DEP/INV[05] 4c60b3c 0 DEP/INV[05] 4be3004 0 DEP/INV[05] 4bd7078 0 DEP/INV[05] 4bd9f08 0 DEP/INV[05] 54b688c 0 DEP/INV[05] 54bb010 0 DEP/INV[05] 5404070 0 DEP/INV[05] 492dfdc 0 DEP[01] LOCK OWNERS: lock user session count mode flags
    - -------- -------- ----- ---- ------------------------ 32e18dc 27328b0 27328b0 1 X [00] 32d2c4c 27328b0 273311c 1 N PNC/[04] LOCK WAITERS: lock user session count mode
    - -------- -------- ----- ---- 32e1f6c 27328b0 273311c 0 X LIBRARY OBJECT: object=4921368 type=TABL flags=EXS/LOC/UPD[0905] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      - -------- -------- ------ ---- ------ 0 4922874 492150c I/P/A 0 NONE 2 493af7c 0 -/P/- 0 NONE 3 493af1c 49f7af8 I/P/A 1 NONE 4 49213fc 49b30b4 I/P/A 2 NONE 8 492145c 54d0d4c I/P/A 2 UPDATE 10 49214bc 4a4ad0c I/P/A 2 UPDATE
- BLOCKING LOCK ------------
  SO: 32e18dc, type: 35, owner: 3254e08, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=32e18dc handle=54b6734 mode=X call pin=0 session pin=0 user=27328b0 session=27328b0 count=1 flags=[00] savepoint=93996 LIBRARY OBJECT HANDLE: handle=54b6734 name=FIT_DBA.OFFICE hash=4e819ede timestamp=11-02-2001 12:12:02 namespace=TABL/PRCD/TYPE flags=KGHP/TIM/SML/[02000000] kkkk-dddd-llll=0000-051d-051d lock=X pin=X latch=0 lwt=54b674c[32e1f7c,32e1f7c] ltm=54b6754[54b6754,54b6754] pwt=54b6764[54b6764,54b6764] ptm=54b67bc[54b67bc,54b67bc] ref=54b673c[54b673c,54b673c] lnd=54b67c8[54b6698,54c08bc] DEPENDENCY REFERENCES: reference latch flags
  - ----- ------------------- 5256d20 0 DEP/INV[05] 4f91fec 0 DEP/INV[05] 4d24370 0 DEP/INV[05] 4c6491c 0 DEP/INV[05] 4c60b3c 0 DEP/INV[05] 4be3004 0 DEP/INV[05] 4bd7078 0 DEP/INV[05] 4bd9f08 0 DEP/INV[05] 54b688c 0 DEP/INV[05] 54bb010 0 DEP/INV[05] 5404070 0 DEP/INV[05] 492dfdc 0 DEP[01] LOCK OWNERS: lock user session count mode flags
    - -------- -------- ----- ---- ------------------------ 32e18dc 27328b0 27328b0 1 X [00] 32d2c4c 27328b0 273311c 1 N PNC/[04] LOCK WAITERS: lock user session count mode
    - -------- -------- ----- ---- 32e1f6c 27328b0 273311c 0 X LIBRARY OBJECT: object=4921368 type=TABL flags=EXS/LOC/UPD[0905] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      - -------- -------- ------ ---- ------ 0 4922874 492150c I/P/A 0 NONE 2 493af7c 0 -/P/- 0 NONE 3 493af1c 49f7af8 I/P/A 1 NONE 4 49213fc 49b30b4 I/P/A 2 NONE 8 492145c 54d0d4c I/P/A 2 UPDATE 10 49214bc 4a4ad0c I/P/A 2 UPDATE
        This lock request was aborted.

Thanks for any help.

--
Jim Poe (jpoe_at_fulcrumit.com)
"Jim Poe" <jpoe_at_fulcrumit.com> wrote in message
news:9s1lpi$87d_at_dispatch.concentric.net...
> We have just added two columns, AP_DUE_DAYS and AP_DUE_BASE,  to an
existing
> table.  I have a Delphi application that has explicit SQL to select,
update,
> and insert this table.  I modified this SQL to add these two columns.
When
> I run my application and attempt to update the table I get the following
> error.
>
> AMS
> --------------------------------------------------------------------------
-
> FIT Server Error
>    Changes canceled
>
> ORA-06550: line 31, column 53:
> PLS-00417: unable to resolve "AP_DUE_BASE" as a column
> ORA-06550: line 28, column 1:
> PL/SQL: SQL Statement ignored
> --------------------------------------------------------------------------
-
>
> In OracleMonitor, this is the SQL sent to the database:
>
> BEGIN
> UPDATE PDCR SET RETAIL_TYPE=:RETAIL_TYPE,
>                 CTSY_TYPE=:CTSY_TYPE,
>                 CSIG_TYPE=:CSIG_TYPE,
>                 WHSL_TYPE=:WHSL_TYPE,
>                 PROSP_TYPE=:PROSP_TYPE,
>                 CMPT=:CMPT,
>                 PDCR_TERR_ID=:PDCR_TERR_ID,
>                 AGR_SEND_DT=:AGR_SEND_DT,
>                 AGR_RECV_DT=:AGR_RECV_DT,
>                 MERGE_TO_ID=:MERGE_TO_ID,
>                 MERGE_DT=:MERGE_DT,
>                 CTSY_FEE_FLAT=:CTSY_FEE_FLAT,
>                 CTSY_FEE_RATE=:CTSY_FEE_RATE,
>                 CSIG_FEE_FLAT=:CSIG_FEE_FLAT,
>                 CSIG_FEE_RATE=:CSIG_FEE_RATE,
>                 STATUS=:STATUS,
>                 AWC_STATUS=:AWC_STATUS,
>                 AND_STATUS=:AND_STATUS,
>                 INACTIVE_STATUS=:INACTIVE_STATUS,
>                 RAH_STATUS=:RAH_STATUS,
>                 RCO_STATUS=:RCO_STATUS,
>                 RDR_STATUS=:RDR_STATUS,
>                 RRO_STATUS=:RRO_STATUS,
>                 RML_STATUS=:RML_STATUS
>             WHERE PDCR_ID=:PDCR_ID;
>
> UPDATE OFFICE SET NAME=:NAME, NAME_LEGAL=:NAME_LEGAL,
>                   ALPHA_CD=:ALPHA_CD, GRP_ID=:GRP_ID,
>                   WEB_URL=:WEB_URL, NOTE=EMPTY_CLOB(),
>                   HQ=:HQ, AP_DUE_DAYS=:AP_DUE_DAYS,
AP_DUE_BASE=:AP_DUE_BASE
>               WHERE OFFICE_ID=:OFFICE_ID
>               RETURNING NOTE INTO :NOTE;
> END;
>
>
> The same application selects the column without complaint.  The column
> exists in the OFFICE table.  The column and bind variable is spelled
> correctly.  If I remove AP_DUE_BASE from the update, it works correctly,
> even with the other new column, AP_DUE_DAYS.  AP_DUE_BASE is CHAR(1).
>
> I am stumped.
>
> Thanks for any help.
>
> --
> Jim Poe (jpoe_at_fulcrumit.com)
>
>

Received on Mon Nov 05 2001 - 10:35:01 CST