Deadlock on single row update(only session)

From: <alan_abbo_at_my-deja.com>
Date: Mon, 27 Dec 1999 05:25:15 GMT
Message-ID: <846suf$h3u$1@nnrp1.deja.com>

I am getting a deadlock on a single row update for the only session connected. This has been occuring for three days for most tables within a particular schema. The instance has been restarted. What is causing it?

SQL and trace file follow.

SQL> update admin.block
  2 set blk_to_code = 'ES'
  3 where str_idx = 23058.791
  4 and blk_idx = 6;
update admin.block

             *
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object ADMIN.BLOCK

SQL> select * from v$lock;

ADDR     KADDR          SID TY       ID1       ID2     LMODE   REQUEST
CTIME     BLOCK

-------- -------- --------- -- --------- --------- --------- --------- --------- ---------

C7080700 C7080710         4 RT         1         0         6         0
0         0
C70806C4 C70806D4         2 MR        23         0         4         0
0         0
C708064C C708065C         2 MR         1         0         4         0
0         0
C7080610 C7080620         2 MR         2         0         4         0
0         0
C70805D4 C70805E4         2 MR         3         0         4         0
0         0
C7080598 C70805A8         2 MR         4         0         4         0
0         0
C708055C C708056C         2 MR         5         0         4         0
0         0
C7080520 C7080530         2 MR         6         0         4         0
0         0
C70804E4 C70804F4         2 MR         7         0         4         0
0         0
C70804A8 C70804B8         2 MR         8         0         4         0
0         0
C708046C C708047C         2 MR         9         0         4         0
0         0
C7080430 C7080440         2 MR        10         0         4         0
0         0
C70803F4 C7080404         2 MR        11         0         4         0
0         0
C70803B8 C70803C8         2 MR        12         0         4         0
0         0
C708037C C708038C         2 MR        13         0         4         0
0         0
C7080340 C7080350         2 MR        14         0         4         0
0         0
C7080304 C7080314         2 MR        15         0         4         0
0         0
C70802C8 C70802D8         2 MR        16         0         4         0
0         0
C708028C C708029C         2 MR        17         0         4         0
0         0
C7080250 C7080260         2 MR        18         0         4         0
0         0
C7080214 C7080224         2 MR        19         0         4         0
0         0
C70801D8 C70801E8         2 MR        20         0         4         0
0         0
C708019C C70801AC         2 MR        21         0         4         0
0         0
C7080160 C7080170         2 MR        22         0         4         0
0         0

24 rows selected.

SQL> select * from v$session;

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND
-------- --------- --------- --------- -------- ---------

------------------------------ ---------
  OWNERID TADDR    LOCKWAIT STATUS   SERVER      SCHEMA# SCHEMANAME

--------- -------- -------- -------- --------- ---------

---

OSUSER PROCESS MACHINE
--------------- ---------

---

TERMINAL   PROGRAM                                          TYPE

SQL_ADDR SQL_HASH_VALUE

---------- ------------------------------------------------ ----------

-------- --------------
PREV_SQL PREV_HASH_VALUE MODULE
MODULE_HASH
-------- ---------------

------------------------------------------------ -----------
ACTION                           ACTION_HASH
-------------------------------- -----------

CLIENT_INFO
FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ#

---

• ------------- ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET PDM FAILOVER_TYPE FAILOVER_M FAI -------------- --------------- ------------- --------- ------------ --- ------------- ---------- --- C6FC41F8 1 1 0 C6F9F8D0 0 0 2.147E+09 ACTIVE DEDICATED 0 SYS oracle 5394 allstar ? oracle_at_allstar (PMON) BACKGROUND 00 0 00 0 0 0

0            -1
             0               0             0 25-DEC-99       227173 NO
NONE          NONE       NO

C6FC4A78         2         1         0 C6F9FBC0         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5396      allstar
?          oracle_at_allstar (DBW0)                            BACKGROUND
00                    0
00                     0
0
                                           0

0            -1
             0               0             0 25-DEC-99       227173 NO
NONE          NONE       NO

C6FC52F8         3         1         0 C6F9FEB0         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5400      allstar
?          oracle_at_allstar (ARCH)                            BACKGROUND
00                    0
00                     0
0
                                           0

0            -1
             0               0             0 25-DEC-99       227173 NO
NONE          NONE       NO

C6FC5B78         4         1         0 C6FA01A0         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5402      allstar
?          oracle_at_allstar (LGWR)                            BACKGROUND
00                    0
00                     0
0
                                           0

0            -1
             0               0             0 25-DEC-99       227173 NO
NONE          NONE       NO

C6FC63F8         5         1         0 C6FA0490         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5405      allstar
?          oracle_at_allstar (CKPT)                            BACKGROUND
00                    0
00                     0
0
                                           0

0            -1
             0               0             0 25-DEC-99       227173 NO
NONE          NONE       NO

C6FC6C78         6         1         0 C6FA0780         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5407      allstar
?          oracle_at_allstar (SMON)                            BACKGROUND
C69CC638      2.688E+09
C69CC638       2.688E+09
0
                                           0

0            -1
             0               0             0 25-DEC-99       227172 NO
NONE          NONE       NO

C6FC74F8         7         1         0 C6FA0A70         0
0
2.147E+09                   ACTIVE   DEDICATED         0 SYS
oracle          5409      allstar
?          oracle_at_allstar (RECO)                            BACKGROUND
C69D1778      3.626E+09
C69D1778       3.626E+09
0
                                           0

0            -1
             0               0             0 25-DEC-99       227172 NO
NONE          NONE       NO

C6FC85F8         9        36    150398 C6FA1340        32 ALAN
3
2.147E+09                   ACTIVE   DEDICATED        32 ALAN
alan            124:97    WORKGROUP\00105A5CE2E6
00105A5CE2 C:\WINNT\Profiles\All Users\Start Menu\Programs\ USER
C685F020      3.983E+09
C685F020       3.983E+09 SQL*Plus
3.670E+09
                                   4.030E+09

16            -1
             0               0             0 27-DEC-99            0 NO
NONE          NONE       NO

8 rows selected.
Dump file /u3/app/oracle/admin/ORA1/udump/ora_13442.trc Oracle8 Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
ORACLE_HOME = /u3/app/oracle/product/8.0.6

System name:	HP-UX
Node name:	allstar
Release:	B.10.20
Version:	U
Machine:	9000/879

Instance name: ORA1
Redo thread mounted by this instance: 1 Oracle process number: 11
Unix process pid: 13442, image: oracleORA1

• 1999-12-27 15:07:45.011
• SESSION ID:(9.36) 1999-12-27 15:07:45.010 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 ADMIN.BLOCK

  ---

  object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- c688a088 c6fc85f8 c7327ae8 X c6fc85f8 c7327d68 S

  ---

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

    ---

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

      ---

      SO: c7327ae8, type: 23, owner: c7254ec8, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=c7327ae8 handle=c688a088 request=X call pin=0 session pin=0 user=c6fc85f8 session=c6fc96f8 count=0 flags=[00] savepoint=0 LIBRARY OBJECT HANDLE: handle=c688a088 name=ADMIN.BLOCK hash=5e014417 timestamp=10-05-1999 10:36:27 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-071d-071d lock=S pin=S latch=0 lwt=c688a0a0[c7327af8,c7327af8] ltm=c688a0a8[c688a0a8,c688a0a8] pwt=c688a0b8[c688a0b8,c688a0b8] ptm=c688a110[c688a110,c688a110] ref=c688a090[c68097d0,c6881280] LIBRARY OBJECT: object=c6889e00 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 c688a010 c6889e88 I/P/A 0 NONE 2 c6889a40 0 -/P/- 0 NONE 3 c68899e0 c6881338 I/P/A 1 NONE 4 c6889aa0 c6875c80 I/-/A 0 NONE 8 c6889f98 c6889158 I/P/A 1 NONE 9 c6889b00 c6807f08 I/P/A 1 NONE 10 c6889980 c6889618 I/P/A 1 NONE
    • BLOCKING LOCK ------------

      ---

      SO: c7327d68, type: 23, owner: c703c068, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=c7327d68 handle=c688a088 mode=S call pin=c7328d88 session pin=0 user=c6fc85f8 session=c6fc85f8 count=1 flags=PNC/[04] savepoint=248 LIBRARY OBJECT HANDLE: handle=c688a088 name=ADMIN.BLOCK hash=5e014417 timestamp=10-05-1999 10:36:27 namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000] kkkk-dddd-llll=0000-071d-071d lock=S pin=S latch=0 lwt=c688a0a0[c7327af8,c7327af8] ltm=c688a0a8[c688a0a8,c688a0a8] pwt=c688a0b8[c688a0b8,c688a0b8] ptm=c688a110[c688a110,c688a110] ref=c688a090[c68097d0,c6881280] LIBRARY OBJECT: object=c6889e00 type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0 DATA BLOCKS: data# heap pointer status pins change
      • -------- -------- ------ ---- ------ 0 c688a010 c6889e88 I/P/A 0 NONE 2 c6889a40 0 -/P/- 0 NONE 3 c68899e0 c6881338 I/P/A 1 NONE 4 c6889aa0 c6875c80 I/-/A 0 NONE 8 c6889f98 c6889158 I/P/A 1 NONE 9 c6889b00 c6807f08 I/P/A 1 NONE 10 c6889980 c6889618 I/P/A 1 NONE

        ---

        This lock request was aborted. 

SQL> quit

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Dec 26 1999 - 23:25:15 CST