Deadlock on INSERT...RETURNING with trigger

From: Jim Cain <xxxmexxx_at_jimcain.net>
Date: 2000/04/20
Message-ID: <Pine.LNX.4.20.0004201520230.5972-100000@claudius.mgmt-inc.com>#1/1

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

+-------------------+-------------------------+----------------------------+


| 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      |

+-------------------+-------------------------+----------------------------+

Received on Thu Apr 20 2000 - 00:00:00 CDT