| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Deadlock when rebulding indices
I'm gettimg a deadlock situation when I update a table and then try to rebuild indices on a couple of its columns, all within a PL/SQL procedure.
I'm using Oracle8i Release 8.1.6.0.0.
Rebuilding the indices directly from the SQL*Plus command line is no problem; even a PL/SQL "rebuild-the-indices" procedure called from the command line works fine. But I need to do it all from within PL/SQL, and I keep getting the error
ORA-04020: deadlock detected while trying to lock object JOHN.INFO
I've tried COMMIT statements everywhere, even putting the table-update code into an AUTONOMOUS_TRANSACTION procedure, but nothing has helped.
I have (I think) pin-pointed the program statements that are causing the problem. One column of my table is a CLOB. The last two statements of my table-update code access this column using DBMS_LOB procedures:
dbms_lob.trim(pDest, 0);
dbms_lob.copy(pDest, temp_CLOB, dbms_lob.getlength(temp_CLOB), 1, 1);
If I comment these lines out, then no deadlock occurs. Leave either one in, and I still have the problem.
I'm fairly new to Oracle programming, and I'm at my wits end trying to figure out how to do this. I'm hoping some of you old hands can help me out here. A pared-down version of the pertinent code is given below.
CREATE TABLE Info (
Id NUMBER(6), Title VARCHAR2(80), Text CLOB,
storage (INITIAL 57600K next 1280K minextents 1 pctincrease 0)
chunk 1k pctversion 10 nocache logging) ;
/* Create indices for InterMedia text searching (these are the indices I
have to rebuild when I update my table)
*/
create index Info_text_ctxsys on Info(Text)
indextype is ctxsys.context;
create index Info_header_ctxsys on Info(Header)
indextype is ctxsys.context;
/* I tried doing this in a trigger (below) but got the same "deadlock"
error as with a procedure.
*/
/*
CREATE TRIGGER Update_Info_Search_Indices
AFTER INSERT OR DELETE OR UPDATE ON Info
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'alter index Info_header_ctxsys rebuild';
EXECUTE IMMEDIATE 'alter index Info_text_ctxsys rebuild';
END;
/
*/
CREATE OR REPLACE PACKAGE foo
AS
PROCEDURE Write_New_Info (title_str IN VARCHAR2);
END foo;
/
CREATE OR REPLACE PACKAGE BODY foo
AS
/*----- Private "Global" Variables -----*/
temp_CLOB CLOB := NULL;
/********************************************************************* * WRITE_NEW_INFO * * * * Inserts a new row into table INFO, and copies stored text to the * * CLOB "Text" field. * * * * When this function is called, the text to be written to the * * "Text" column has already been stored in a temporary CLOB named * * "temp_CLOB". * *********************************************************************/
PROCEDURE Write_New_Info (title_str IN VARCHAR2) AS
pDest CLOB;
new_id NUMBER(6);
BEGIN
/* Since we are creating a new row, we must initialize the CLOB
Text column with a call to empty_clob()
*/
SELECT InfoID_seq.NEXTVAL INTO new_id FROM dual;
INSERT INTO Info (Id, Title, Text)
VALUES (new_id, title_str, empty_clob());
/* Now we copy the temporary CLOB to the table's CLOB */
SELECT Text INTO pDest -- Get the locator from the new row
FROM Info
WHERE id = new_id
FOR UPDATE;
/* If I omit the following two lines, I do NOT get the "deadlock"
error:
*/
dbms_lob.trim(pDest, 0); -- Truncate any existing text
dbms_lob.copy(pDest, temp_CLOB, dbms_lob.getlength(temp_CLOB), 1, 1);
dbms_lob.freetemporary(temp_CLOB); -- Release the temporary CLOB
/* End the transaction */
COMMIT;
/* Update the indices: either of the following statements will result
in the "deadlock" error message.
*/
END Write_New_Info;
END foo;
/
Received on Wed Jul 18 2001 - 11:28:10 CDT
 |
 |