Technical Note on Blocking Using INSERT

Just a short technical note on how to get a contention lock on an INSERT statement.

The secret is to have a unique constraint on at least one column, and to have multiple uncommitted INSERT statements with the same new value for the column with the unique constraint.

For example, say that I have the following table:

CREATE TABLE EMP
(
  ID NUMBER NOT NULL
, NAME VARCHAR2(20)
, DEPT NUMBER
, CONSTRAINT EMP_PK PRIMARY KEY
  (
    ID
  )
  ENABLE
);

Now in session (SID=145), I run the following SQL statement without doing either a COMMIT or a ROLLBACK:

insert into emp(id, name, dept) values(10, 'First', 4);

And in another session (SID=22), I run the following SQL statement:

insert into emp(id, name, dept) values(10, 'Second', 5);

This second session is waiting as confirmed by DBA_WAITERS:

WAITING_SESSION	HOLDING_SESSION	LOCK_TYPE	MODE_HELD	MODE_REQUESTED	LOCK_ID1	LOCK_ID2
22	145	Transaction	Exclusive	Share	589833	13198

Now I rollback of these transactions. And I try to see if I can get a deadlock using only INSERT statements.

In the first session (SID=145), I run the following SQL statement without doing either a COMMIT or a ROLLBACK:

insert into emp(id, name, dept) values(10, 'First', 4);

In the second session (SID=22), I run the following SQL statement without doing either a COMMIT or a ROLLBACK:

insert into emp(id, name, dept) values(11, 'Second', 5);

In the first session (SID=145), I run the following SQL statement, and get blocked:

insert into emp(id, name, dept) values(11, 'First', 4);

In the second session (SID=22), I run the following SQL statement, and get blocked:

insert into emp(id, name, dept) values(10, 'Second', 5);

Eventually, it is the second session (SID=22) that gets the following message:

insert into emp(id, name, dept) values(10, 'Second', 5)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

The deadlock graph produced is:

Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00060001-00003314 36 22 X 35 145 S
TX-00050020-000033e8 35 145 X 36 22 S

session 22: DID 0001-0024-000000CC session 145: DID 0001-0023-000005EA
session 145: DID 0001-0023-000005EA session 22: DID 0001-0024-000000CC

Rows waited on:
Session 22: obj – rowid = 00000000 – D/////AADAAAAFyAAA
(dictionary objn – 0, file – 3, block – 370, slot – 0)
Session 145: no row
{/sourcecode]

Despite what Jonathan Lewis wrote in Deadlocks:

Both sessions are holding X and waiting S on a TX lock. There are several well-known reasons why you might see a share (mode 4) lock on a transaction table, collisions on bitmap indexes, unique indexes, and index organized tables, are the commonest “external” examples, and problems with interested transaction lists (ITLs) or freelists are the common “internal” ones – so the presence of the waits in share mode aren’t, of themselves, a source of confusion.

I was certainly confused until I read his post: how could an INSERT be blocked? Once I understood what was happening under the surface, it makes logical sense.

And I now know not to trust the information in the deadlock graph regarding the rows being waited on.