Wednesday, December 13, 2006

Row Locks When Inserting - Oracle

My co-worker told me his application hanging while inserting a row into a table. By looking at the Oracle Enterprise Manager, I noticed it's caused by foreign key. Here I show an example.

Open a SQL Plus, create the tables and foreign key.
    create table tmp_test 
    ( id         number(5) not null, 
      test_type  char(1), 
      comments varchar2(100),
      constraint pk_tmp_test primary key (id) 
    );

    create table tmp_test_type 
    ( test_type char(1), 
      description  varchar2(100),
      constraint pk_tmp_test_type primary key (test_type) );

    alter table tmp_test add constraint fk1_tmp_test foreign key ( test_type )
      references tmp_test_type;


Now try to insert a row without referring type. Expecting an error:
    insert into tmp_test values( 1, 'A', 'Just a test' );

    ORA-02291: integrity constraint (EAS_DEMO.FK1_TMP_TEST) violated - parent key not found


Open another session using SQL Plus, insert a row in test_test_type table but don't commit:
    insert into tmp_test_type values( 'A', 'Test type A' );


Run the insert again using the first SQL Plus, you'll find it hangs. In Oracle Enterprise Manager, you can see "enq: TX - row lock contention".

Commit the changes and cleanup:
    drop table tmp_test;
    drop table tmp_test_type;