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 (
PRIMARY KEY (C1));
INSERT INTO T1 SELECT ROWNUM, ROWNUM*10 FROM DUAL CONNECT BY LEVEL<=1000000;
First session issues following update;
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.