Re: strange dead lock on clob column

This is a case where I would be suspicious of the information in the rowid listings.

You have the two SQL statements from the colliding sessions and they are both inserts on the same table.

The deadlock involves exclusive (X) and share (S) mode for TX locks in the "holds" and "waits" columns. The most likely reasons for the S mode revolve around problems with uniqueness and/or referential integrity.

Given the problems are inserts, the first guess would be that the two sessions have inserted some rows into the table successfully without committing, and then each has tried to insert a row with a primary key that the other has already used.

(There are other variants of primary key, pk/fk issues - for example one session deletes a primary key then the other tries to insert rows for that primary in the child table - if two sessions cross over in this activity then you can get the same deadlock graphs. The primary key collision is just the simplest version of the problem).

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"lsllcm" <lsllcm_at_gmail.com> wrote in message 
news:681f2224-e862-40d3-a6b1-b2dd1673d074_at_s8g2000prg.googlegroups.com...


> Hi All,

>

> I met a strange dead lock on clob column.

>

> Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

> With the Partitioning, OLAP and Oracle Data Mining options

> JServer Release 9.2.0.8.0 - Production

> ORACLE_HOME = /oracle/ora92

> System name: Linux

> Node name: localhost.localdomain

> Release: 2.6.9-5.ELsmp

> Deadlock graph:

>                       ---------Blocker(s)--------  ---------

> Waiter(s)---------

> Resource Name          process session holds waits  process session

> holds waits

> TX-0018000f-0000000b       139     145     X            126

> 108           S

> TX-00030029-00003048       126     108     X            139

> 145           S

> session 145: DID 0001-008B-00000002 session 108: DID

> 0001-007E-00000006

> session 108: DID 0001-007E-00000006 session 145: DID

> 0001-008B-00000002

> Rows waited on:

> Session 108: obj - rowid = 00009039 - AAAJA5AAoAAAAAAAAA

>  (dictionary objn - 36921, file - 40, block - 0, slot - 0)

> Session 145: obj - rowid = 00009039 - AAAJA5AAoAAAAAAAAA

>  (dictionary objn - 36921, file - 40, block - 0, slot - 0)

> Information on the OTHER waiting sessions:

> Session 108:

>  pid=126 serial=48721 audsid=45507 user: 51/ACCELAPRE

>  O/S info: user: , term: , ospid: 1234, machine: test02-server

>            program:

>  Current SQL Statement:

>  INSERT INTO BVALUATN

> (SERV_PROV_CODE,B1_PER_ID1,B1_PER_ID2,B1_PER_ID3,B1_VALUATN_PERIOD,

> G3_VALUE_TTL,G3_PC_VALUE_TTL,G3_CALC_VALUE,G3_FEE_FACTOR_FLG,REC_DATE,REC_FUL_NAM,REC_STATUS)

> VALUES(:1,:2,:3,:4,:5,  :6,:7,:8,:9,:10,  :11,:12)

> End of information on OTHER waiting sessions.

> Current SQL statement for this session:

> INSERT INTO BVALUATN

> (SERV_PROV_CODE,B1_PER_ID1,B1_PER_ID2,B1_PER_ID3,B1_VALUATN_PERIOD,

> G3_VALUE_TTL,G3_PC_VALUE_TTL,G3_CALC_VALUE,G3_FEE_FACTOR_FLG,REC_DATE,REC_FUL_NAM,REC_STATUS)

> VALUES(:1,:2,:3,:4,:5,  :6,:7,:8,:9,:10,  :11,:12)

>

>

> I checked object with data_object_id=36921, it is clob segment.

>

> The segment name is SYS_LOB0000036920C00004$$, and it is a column

> REVT_AGENCY_SCRIPT.SCRIPT_TEXT

>

> But the locked sql is:

> INSERT INTO BVALUATN

> (SERV_PROV_CODE,B1_PER_ID1,B1_PER_ID2,B1_PER_ID3,B1_VALUATN_PERIOD,

> G3_VALUE_TTL,G3_PC_VALUE_TTL,G3_CALC_VALUE,G3_FEE_FACTOR_FLG,REC_DATE,REC_FUL_NAM,REC_STATUS)

> VALUES(:1,:2,:3,:4,:5,  :6,:7,:8,:9,:10,  :11,:12)

>

> It is very strange, can anyone help on this one

>

> Thanks in advance