Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Deadlock when rebulding indices

Re: Deadlock when rebulding indices

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Jul 2001 09:46:42 -0700
Message-ID: <9j4ehi02p2q@drn.newsguy.com>

In article <3B55B91A.ED25E077_at_pacbell.net>, John says...
>
>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.
>
>

well, I can see some reasons for the deadlock -- but its not really relevant.

Your goal seems to be "sync the intermedia index after insert or update in order to have newly added text in the index straight away"

However, your logic is:

insert into the table
  -> fires trigger, which would attempt to sync the index

THEN write the text.

The bottom line will be -- don't try to do it the way you are. If you want things to be indexed right away, you should enable the ctxsrv process. Then it'll just happen, no muss no fuss.

Else, call ctx_ddl.sync_index( 'index name' ) after the dbms_lob operations.

See

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1091633271464 http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:238814521769

for examples and more details and pointers to the relevant documentation.

>
>-----------------------------------------------------------------------
>------------- FILE TO CREATE TABLE ------------------------------------
>-----------------------------------------------------------------------
>
>CREATE TABLE Info (
> Id NUMBER(6),
> Title VARCHAR2(80),
> Text CLOB,
> constraint INFO_PK PRIMARY KEY (Id)
>)
>tablespace USERS
>storage (INITIAL 48K next 1K minextents 1 pctincrease 0)
>lob (Text) store AS
>(tablespace INFOTEXT
> 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;
>/
>*/
>
>
>-----------------------------------------------------------------------
>------------- PACKAGE SPEC --------------------------------------------
>-----------------------------------------------------------------------
>
>CREATE OR REPLACE PACKAGE foo
>AS
> PROCEDURE Write_New_Info (title_str IN VARCHAR2);
>
>END foo;
>/
>
>
>-----------------------------------------------------------------------
>------------- PACKAGE BODY --------------------------------------------
>-----------------------------------------------------------------------
>
>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.
> */
> EXECUTE IMMEDIATE 'alter index Info_header_ctxsys rebuild';
> EXECUTE IMMEDIATE 'alter index Info_text_ctxsys rebuild';
>
>END Write_New_Info;
>
>END foo;
>/

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Wed Jul 18 2001 - 11:46:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US