Re: Deadlock on single row update(only session)

Hello Alan,

Firstly, these are library cache locks, not enqueue locks, so V$LOCK will not help. It only shows enqueue locks. To investigate library cache locking issues, you need to use DBA_KGLLOCK which is created by the $ORACLE_HOME/rdbms/admin/catblock.sql script. However, the trace file that you have posted contains the same information.

The trace shows that this is a self-deadlock. Note that the blocking user session is c6fc85f8, and so is the waiting user session. A shared lock has been taken by a call in the user session part of the way into its transaction (savepoint 248), and an exclusive lock is required by the recursive session c6fc96f8. Recursive sessions like this are used for recursive transactions. However, I cannot tell you from this trace what that recursive transaction was trying to do, and how to fix it.

To get the fuller information required, could you please reproduce the error after having set the following event in the session.   alter session set events '4020 trace name systemstate forever, level 10';
When you get the trace file, edit a copy leaving just the section for the pseudo process, which owns the recursive sessions, and the section for your user process. Everything else is not needed. That should give you a file small enough to either post here or zip up and email to me directly.

Regards,
Steve Adams

http://www.ixora.com.au/

http://www.oreilly.com/catalog/orinternals/

http://www.christianity.com.au/

On Mon, 27 Dec 1999 05:25:15 GMT, alan_abbo_at_my-deja.com wrote:

>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
>
>
>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 Mon Dec 27 1999 - 19:41:29 CST