Re: Deadlock on INSERT...RETURNING with trigger

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/04/21
Message-ID: <8dph7c$42a$1@nnrp1.deja.com>#1/1

In article <Pine.LNX.4.20.0004201520230.5972-100000_at_claudius.mgmtinc. com>,
  Jim Cain <xxxmexxx_at_jimcain.net> wrote:
> All,
>
> I have an application with a statement like the following:
>
> INSERT INTO mytable (col1, col2)
> VALUES (value1, value2)
> RETURNING col3 INTO :host_var;
>
> This table has a BEFORE INSERT trigger that updates the value of col3
 when
> it's inserted as a NULL:
>
> BEGIN
> IF :new.col3 IS NULL THEN
> SELECT mysequence.nextval
> INTO :new.col3
> FROM DUAL;
> END IF;
> END
>
> Now and then I get a deadlock error:
>
> ORA-04020: deadlock detected while trying to lock object MYTABLE
>
> I suspect it has something to do with the RETURNING clause trying to
 grab
> the value that's updated by the trigger. I've never used a RETURNING
> clause before, and this is inherited code. Actually I'd rather use a
> stored procedure instead of the trigger, but that's another issue.
>
> What I'm hoping someone will say is fairly simple: "Oh yeah, it's a
 bad
> idea to use an INSERT...RETURNING with a BEFORE INSERT trigger."
>
> Any help will be much appreciated.
>
> Cheers,
> Jim
>

No, that will not cause a deadlock -- use the returning clause all of the time, never causes it (and there is nothing in its implementation that could cause it).

Do you by any chance have some declaritive RI (foreign keys) and they are unindexed? that is the major #1 cause of deadlocks -- see http://osi.oracle.com/~tkyte/unindex/index.html for some more info on that.

> +-------------------+-------------------------+-----------------------
 -----+
> | Jim Cain | http://www.jimcain.net | Oracle

 Developer/DBA       |


> | Miami, FL USA     | http://www.mgmt-inc.com | Linux System

 Administrator |
> | me at jimcain.net | http://www.charlug.org | Apache/Zope
 Webmaster |
> +-------------------+-------------------------+-----------------------
 -----+
>
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Apr 21 2000 - 00:00:00 CDT