Home » RDBMS Server » Server Administration » Deadlock detects (Oacle 10g)
Deadlock detects [message #329930] Fri, 27 June 2008 01:41 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Today morning i seen deadlock in the trace file.

Alert_log file details

Fri Jun 27 03:27:19 2008
Memory Notification: Library Cache Object loaded into SGA
Heap size 5118K exceeds notification threshold (2048K)
Details in trace file /home/oracle/admin/ora01/udump/ora01_ora_12327.trc
KGL object name :SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), KU$.OBJ_NUM FROM SYS.KU$_FHTABLE_VIEW KU$ 
               WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192) AND  NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0 
               AND  KU$.SCHEMA_OBJ.NAME=:NAME1 AND  KU$.SCHEMA_OBJ.OWNER_NAME=:SCHEMA2


ora01_ora_12327.trc file details.
I posted only those things which can be readable.

/home/oracle/admin/ora01/udump/ora01_ora_12327.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and Data Mining options
ORACLE_HOME = /home/oracle/product/10gR2
System name:	Linux
Node name:	
Release:	
Version:	
Machine:	
Instance name: 
Redo thread mounted by this instance: 1
Oracle process number: 
Unix process pid: 12327, image: 

*** 2008-03-17 09:03:15.181
*** SERVICE NAME:(SYS$USERS) 2008-03-17 09:03:15.164
*** SESSION ID:(148.8874) 2008-03-17 09:03:15.164
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
UPDATE FELTVALUES A SET A.VALUE = REPLACE(:B4 ,'"','"') WHERE A.DB_ID = :B3 AND A.LEAD_ID=:B2 AND A.FF_ID=:B1 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xa1651b00       396  package body P_LEAD
0xa17c4310      1762  package body RESPONSEHANDLER
0x8cb976f0         1  anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0036000d-0000002c        30     148     X             44      62           S
TX-00370010-0000002b        44      62     X             30     148           X
session 148: DID 0001-001E-00014A6F	session 62: DID 0001-002C-0000410E
session 62: DID 0001-002C-0000410E	session 148: DID 0001-001E-00014A6F
Rows waited on:
Session 62: obj - rowid = 00000000 - D/////AAjAAAABpAAA
  (dictionary objn - 0, file - 35, block - 105, slot - 0)
Session 148: obj - rowid = 000100F0 - AAAQDwAAWAADb6xABJ
  (dictionary objn - 65776, file - 22, block - 900785, slot - 73)
Information on the OTHER waiting sessions:
Session 62:
  pid=44 serial=26098 audsid=3222321 user: 1446/<none>
  O/S info: user: TEST_terminal, term: TEST, ospid: 3772:1732, machine: WORKGROUP\TEST
            program: dllhost.exe
  application name: dllhost.exe, hash value=0
  Current SQL Statement:
  UPDATE FELTVALUES A SET A.VALUE = REPLACE(:B4 ,'"','&quot;') WHERE A.DB_ID = :B3 AND A.LEAD_ID=:B2 AND A.FF_ID=:B1 
End of information on OTHER waiting sessions.
===================================================


What i understood is that from session with spid 12327 some sql statemens executed which caused the deadlock.

I want to know is that what exactly is the query and and for what values the deadlock occured.

Thanks,

[Updated on: Fri, 27 June 2008 01:43]

Report message to a moderator

Re: Deadlock detects [message #329934 is a reply to message #329930] Fri, 27 June 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All the information you can have is there.
By the way, there is the SQL statement.
In addition, you have the row id, it is easy to get the values from that.

Regards
Michel
Re: Deadlock detects [message #329941 is a reply to message #329930] Fri, 27 June 2008 02:03 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks,

So i understand is that the query is
UPDATE FELTVALUES A SET A.VALUE = REPLACE(:B4 ,'"','&quot;') WHERE A.DB_ID = :B3 AND A.LEAD_ID=:B2 AND A.FF_ID=:B1 

And it is given
package body P_LEAD
package body RESPONSEHANDLER
anonymous block

Does it mean the above query is executed from the package P_LEAD, RESPONSEHANDLER and anonymous block????

Rows waited on:
Session 62: obj - rowid = 00000000 - D/////AAjAAAABpAAA
  (dictionary objn - 0, file - 35, block - 105, slot - 0)
Session 148: obj - rowid = 000100F0 - AAAQDwAAWAADb6xABJ
  (dictionary objn - 65776, file - 22, block - 900785, slot - 73)


So id's are D/////AAjAAAABpAAA and AAAQDwAAWAADb6xABJ.

But i find records for rowid = AAAQDwAAWAADb6xABJ not for D/////AAjAAAABpAAA Confused

Please explain about it.

Thanks

[Updated on: Fri, 27 June 2008 02:03]

Report message to a moderator

Re: Deadlock detects [message #329959 is a reply to message #329941] Fri, 27 June 2008 02:30 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does it mean the above query is executed from the package P_LEAD, RESPONSEHANDLER and anonymous block????

At line 1 of anonymous block you called RESPONSEHANDLER package which called, at its line 1762, P_LEAD package which executed the update at its line 396.

Quote:
But i find records for rowid = AAAQDwAAWAADb6xABJ not for D/////AAjAAAABpAAA

The second one is not a valid rowid meaning this information was not available at dump time. Maybe because session 62 is holding a lock at table level.
Have a look at what dllhost.exe is doing.

Regards
Michel


Previous Topic: What do these parameter mean in the trace file?
Next Topic: why oracle doestn't start database after closing
Goto Forum:
  


Current Time: Fri Dec 02 14:26:49 CST 2016

Total time taken to generate the page: 0.42862 seconds