Quiz – answer

First of all I’ve to excuse myself for the delay of the answer. Unfortunately I was very, very busy the last couple of weeks and (fortunately) there were also two week of vacation between it. But now I’m back and here is the answer:

First of all: Yes, an Insert can block another one but of course just under special conditions. Thanks for the comments. They’re all right. The simplest way an insert can block another one is a RI violation.
For example:

We’ve one simple table with a primary/unique key on it:

SQL> create table simple (id number primary key);

Table created.

Now we’ve two sessions and both try to insert the same primary key:

Session A:

SQL> set timing on;
SQL> insert into simple (id) values (1);

1 row created.

Elapsed: 00:00:00.10

Session B:

SQL> set timing on;
SQL> insert into simple (id) values (1);

Session B is now waiting for session A. As soon as session A commits session B receives an error that the primary key already exists. As soon as session A would rollback session B would be successful with the insert.

The reason: As long as the first session hasn’t committed the transaction or rolled it back, the information that the primary key value is already used isn’t valid. If the first session would rollback the transaction the value of the primary key would be valid. But if it would commit the transaction the value would be already in use and the second session will fail because of RI violation. So session A would block session B as long as it doesn’t end the transaction and session B will wait with a row lock on the primary key index itself!

Looking to the trace file we see this:

Session A:

insert into simple
values
(1)

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        5      0.00       0.01          0          0          0           0
Execute      5      0.00       0.00          0          4         19           4
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total       10      0.01       0.01          0          4         19           4

Misses in library cache during parse: 1
Parsing user id: SYS

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
—————————————-   Waited  ———-  ————
SQL*Net message to client                       5        0.00          0.00
SQL*Net message from client                     5       45.39        108.93
log file sync                                   1        0.02          0.02
********************************************************************************

Session B:

insert into simple
values
(1)

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.02      64.27          0          5         16           1
Fetch        0      0.00       0.00          0          0          0           0
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.02      64.27          0          5         16           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
—————————————-   Waited  ———-  ————
SQL*Net message to client                       3        0.00          0.00
SQL*Net message from client                     3       20.10         42.92
enq: TX – row lock contention                  23        2.93         62.41
SQL*Net break/reset to client                   2        0.00          0.00
********************************************************************************

We see that we spent 64.27 seconds in a TX – row lock contention. That’s exactly the row lock on the primary key index itself. After session A commits we see following:

Session A:

SQL> commit;
Commit complete.

Elapsed: 00:00:00.13

Session B:

insert into simple (id) values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C005195) violated

Elapsed: 00:00:64.27

So that time (64.27 seconds) we waited in the row lock contention on the index because the other session didn’t end the transaction so far. This is one of the easiest way why an insert can be also blocked and not just a update or a delete!