Does Insert wait on Locks ? Or hold Locks ?

On February 25, 2019April 3, 2019 By taliphakanozturkIn SQL-PL/SQL

Hi,

Does Insert wait on Locks ? Or hold Locks ? Last day my developer friend asked me. Yes it waits on lock. If there is PK or unique constraint on a table then it can wait.

Let’s do an example test.

CREATE TABLE T1 (
C1 NUMBER(10),
C2 NUMBER(10),
PRIMARY KEY (C1));

INSERT INTO T1 SELECT ROWNUM, ROWNUM*10 FROM DUAL CONNECT BY LEVEL<=1000000;

COMMIT;

First session issues following update;

UPDATE T1
SET C1=-C1
WHERE C1<=100;

Without issuing COMMIT, the second session issues following insert statement.

INSERT INTO T1 VALUES (-10,10);

So Session 2 hangs. It waits on lock. First session is updating all values with minus less than 100. And in second session I inserting -10 to primary key field. Pk constraint will check value existence and will wait on lock.

If we don’t have a PK or Unique constraint it won’t wait.