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.

About these ads

2 thoughts on “Technical Note on Blocking Using INSERT

  1. Hi,

    Impressed with your OCM learning of 10g and 11g. I thought to complete but, it my dream, which will never can be done. But really your work of learning OCM planning is impressive.

    • Pavan,

      The reason I started blogging back in 2006 was to goad me into completing the goal of achieving the OCM. It was to be a public record of what I was trying to achieve.

      It took me almost two (2) years to achieve.

      As I skim through the old posts, there were gaps when I did nothing. Maybe I bit off too much.

      I think the main benefit of doing my own study was that I learnt to solve difficult problems similar to what I faced in the OCM. In fact, I managed to get an ORA-600 error when I tried to create my physical standby database. But the main thing was to persevere in the face of difficulties. That was the main benefit of my self-study.

      I cannot give you the motivation to go for an OCM. That has to come from within yourself. You have to find the reason to keep going when you cannot get the RAC installed correctly, or there is a weird Linux error, or you have configured an o/s parameter wrong. It is too easy to just give up. I know. I did it at times.

      It is up to you. The path is difficult. The cost is expensive in money and time.

      I suppose the only reason I got the OCM in the end was that I was brash enough to believe I should give it a go. It would only be then that I would find out if I was good as I thought I was.

      Douglas

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s