Re: Deadlock in stored procedure

Kenneth wrote:
> On 21 Feb 2005 06:28:33 -0800, "sigdock" <sigdock_at_hotmail.com> wrote:
>
> >Hi all,
> >
> >I wrote a stored procedure to upload files to a table in my
database. I
> >also added a line to sync the context-index on the table. However if
i
> >try to run the procedure i get a deadlock error. Does anybody have
any
> >idea why this happens en how to prevent it? The procedure looks lik
> >this:
> >
> >
> >CREATE OR REPLACE PROCEDURE load_file_to_my_docs (p_file_name IN
> >my_docs.name%TYPE) AS
> > v_bfile BFILE;
> > v_blob BLOB;
> > v_statement varchar2(200) := 'ALTER INDEX my_docs_doc_idx REBUILD
> >online PARAMETERS (''SYNC'')';
> >BEGIN
> > INSERT INTO my_docs (id, name, doc)
> > VALUES (my_docs_seq.NEXTVAL, p_file_name, empty_blob())
> > RETURN doc INTO v_blob;
> >
> > v_bfile := BFILENAME('DOCUMENTS', p_file_name);
> > Dbms_Lob.Fileopen(v_bfile, Dbms_Lob.File_Readonly);
> > Dbms_Lob.Loadfromfile(v_blob, v_bfile,
Dbms_Lob.Getlength(v_bfile));
> > Dbms_Lob.Fileclose(v_bfile);
> > COMMIT;
> > execute immediate v_statement;
> >END;
> >/
> >
> >the table my_docs looks like:
> >
> >(ID number(10), name varchar2(200), doc blob)
> >
> >tia,
> >Bert Jan Meinders
> >
>
> Hi Bert,
>
> Please provide the following info if you want us to help you:
>
> 1) Oracle version and platform ( *must ALWAYS* be provided)
> 2) At which statement in your code does the Deadlock actually occur ?
> 3) The exact error message/error stack.
>
>
> - Kenneth Koenraadt

Hi Kenneth,

sorry for the missing information, I forgot that these things are rather important. I'm running Oracle 9.2.0.4 on a Windows XP SP1 desktop. The message stack that I get looks like:

BEGIN LOAD_FILE_TO_MY_DOCS('InstallSolaris.pdf'); END;

*
ERROR at line 1:

ORA-04020: deadlock detected while trying to lock object TEST.MY_DOCS
ORA-06512: at "TEST.LOAD_FILE_TO_MY_DOCS", line 15
ORA-06512: at line 1

Looks like the error occurs while the 'alter index' statement is being processed. Received on Wed Feb 23 2005 - 06:07:40 CST