Re: strange dead lock on clob column

From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 25 Nov 2007 08:34:58 -0800 (PST)
Message-ID: <c21f785a-0b77-450b-9121-8424521a8cb8@e6g2000prf.googlegroups.com>

Hi All,

I met a strange dead lock on clob column.

The following are conent

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 Received on Sun Nov 25 2007 - 10:34:58 CST