[SOLVED] Oracle 11g insert + update results in lock

Oracle 11g insert + update results in lock

I'm working with an Oracle 11g database and I'm having lock problems.

I have 2 sessions and I'm executing as follows:

(Session 1) INSERT into table1 with a known id
(Session 2) INSERT into table1 with another known id => No lock
(Session 1) UPDATE from table1 where id = id inserted in the first step => Lock

In both store procedures (insert and update) I include the following clause:

LOCK TABLE table1 IN ROW EXCLUSIVE MODE;

I think that the problem is that oracle is making a table lock in the insert, or it is just not getting which is the row I'm trying to update, although, isn`t that the objective of row level table lock?

Thanks in advance for your help!

I attach my scripts:

CREATE TABLE

    CREATE TABLE ARGOXP.T_AREA
    (
      ARE_ID      INTEGER,
      ARE_NOMBRE  VARCHAR2(80 BYTE)                 NOT NULL,
      ARE_DESCRI  VARCHAR2(1000 BYTE),
      ARE_ACTIVO  NUMBER(1)                         NOT NULL
    )
    TABLESPACE ARGOXP
    PCTUSED    0
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
    LOGGING 
    NOCOMPRESS 
    NOCACHE
    NOPARALLEL
    MONITORING;



CREATE UNIQUE INDEX ARGOXP.PK_T_AREA ON ARGOXP.T_AREA
(ARE_ID)
LOGGING
TABLESPACE ARGOXP
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX ARGOXP.UK_ARE_NOMBRE ON ARGOXP.T_AREA
(ARE_NOMBRE)
LOGGING
TABLESPACE ARGOXP
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE ARGOXP.T_AREA ADD (
  CONSTRAINT PK_T_AREA
 PRIMARY KEY
 (ARE_ID)
    USING INDEX 
    TABLESPACE ARGOXP
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ),
  CONSTRAINT UK_ARE_NOMBRE
 UNIQUE (ARE_NOMBRE)
    USING INDEX 
    TABLESPACE ARGOXP
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL    

  64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
           ));

STORE PROCEDURES

CREATE OR REPLACE PROCEDURE ARGOXP."P_T_AREA_I_PK" 
(
pARE_ID T_AREA.ARE_ID%TYPE,
pARE_NOMBRE T_AREA.ARE_NOMBRE%TYPE,
pARE_DESCRI T_AREA.ARE_DESCRI%TYPE := NULL,
pARE_ACTIVO T_AREA.ARE_ACTIVO%TYPE
)
AS
BEGIN
LOCK TABLE T_AREA IN ROW EXCLUSIVE MODE;

  INSERT INTO T_AREA
    ( ARE_ID,
      ARE_NOMBRE,
      ARE_DESCRI,
      ARE_ACTIVO
    )
  VALUES
    ( pARE_ID,
      pARE_NOMBRE,
      pARE_DESCRI,
      pARE_ACTIVO
    );
END;
/



CREATE OR REPLACE PROCEDURE ARGOXP."P_T_AREA_U_PK" 
(
pARE_ID T_AREA.ARE_ID%TYPE,
pARE_NOMBRE T_AREA.ARE_NOMBRE%TYPE,
pARE_DESCRI T_AREA.ARE_DESCRI%TYPE := NULL,
pARE_ACTIVO T_AREA.ARE_ACTIVO%TYPE
)
AS
BEGIN
LOCK TABLE T_AREA IN ROW EXCLUSIVE MODE;

  UPDATE
    T_AREA
  SET
    ARE_ID = pARE_ID,
      ARE_NOMBRE = pARE_NOMBRE,
      ARE_DESCRI = pARE_DESCRI,
      ARE_ACTIVO = pARE_ACTIVO
  WHERE
    ARE_ID = pARE_ID
;
END;
/

LATEST UPDATES

We have been working hard with this issue this last days, and we would like to share the latest updates. We execute the inserts and updates in the 2 sessions without using stored procedures, and this results in non locks. After that, we created new stored procedures for the insert and the update, without parameters. We just include the stored procedure definition and the script to execute the insert or the update. This resulted in non lock too! But when we call the stored procedure with the parameters, we get the locks.

I attach the execution steps followed to get the locks:

(FIRST) In session 1:

DECLARE 
  PARE_ID NUMBER;
  PARE_NOMBRE VARCHAR2(80);
  PARE_DESCRI VARCHAR2(1000);
  PARE_ACTIVO NUMBER;

BEGIN 
  PARE_ID := 70;
  PARE_NOMBRE := '70';
  PARE_DESCRI := '70';
  PARE_ACTIVO := 1;

  ARGOXP.P_T_AREA_I_PK ( PARE_ID, PARE_NOMBRE, PARE_DESCRI, PARE_ACTIVO );
END;

(SECOND) In session 2

DECLARE 
  PARE_ID NUMBER;
  PARE_NOMBRE VARCHAR2(80);
  PARE_DESCRI VARCHAR2(1000);
  PARE_ACTIVO NUMBER;

BEGIN 
  PARE_ID := 71;
  PARE_NOMBRE := '71';
  PARE_DESCRI := '71';
  PARE_ACTIVO := 1;

  ARGOXP.P_T_AREA_I_PK ( PARE_ID, PARE_NOMBRE, PARE_DESCRI, PARE_ACTIVO );
END;

(THIRD) In session 2

DECLARE 
  PARE_ID NUMBER;
  PARE_NOMBRE VARCHAR2(80);
  PARE_DESCRI VARCHAR2(1000);
  PARE_ACTIVO NUMBER;

BEGIN 
  PARE_ID := 1;
  PARE_NOMBRE := 'update number 1';
  PARE_DESCRI := 'update number 2';
  PARE_ACTIVO := 1;

  ARGOXP.P_T_AREA_U_PK ( PARE_ID, PARE_NOMBRE, PARE_DESCRI, PARE_ACTIVO );
END;

We get the locks in this step.

We also execute the following query with the sysdba user:

SELECT b.OBJECT_NAME, c.ROW_WAIT_OBJ#,c.ROW_WAIT_FILE#,c.ROW_WAIT_BLOCK#,c.ROW_WAIT_ROW#
   FROM v$locked_object a, dba_objects b, v$session c    
WHERE a.object_id = b.object_id    
    AND a.SESSION_ID = c.sid(+);

getting the following results:

Solution

We found the problem. We have another table, TABLE_2, that has a reference to the table T_AREA (Foreign Key field called AREA_ID). When we have a foreign key but we dont have an index in that table, oracle throws an automatic lock to the entire table.

So, the solution is to add an index in TABLE_2 for the FK field (ARE_ID in this case)