Mohamed Houri’s Oracle Notes

March 17, 2014

Indexing Foreign Key: the proof of the FK index use

Filed under: Deadlock — hourim @ 1:51 pm

If you have been looking for a material proof showing Oracle using the foreign key index created on the child table while deleting from a parent table then here it is

drop table t2;
drop table t1;

create table t1
(col1 number primary key);

create table t2
(col1    number primary key
,status  varchar2(12) not null
,col2    number
,col2v   number generated always as (case when status = 'ACTIVE' then col2 end) VIRTUAL
,constraint t2_fk foreign key (col2v) references t1(col1)
,constraint t2_ck check (status in ('ACTIVE','INACTIVE') and (status = 'INACTIVE' or col2 is not null))
);

create index t2_ind_fk on t2(col2v);

insert into t1
 select rownum
from dual
connect by level <=100;

commit;

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);

alter session set skip_unusable_indexes = false;

alter index t2_ind_fk unusable; -- implicit commit

I have created a pair of parent-child table (t1 and t2), an index on the foreign key on the t2 child table, set this index into an unusable state and changed the default skip_unusable_indexes parameter to false so that unusable indexes will not be skipped.

Now, I am going in the next PL/SQL anonymous block, to simulate a delete from a parent table using an autonomous transaction in order to mimic a different session (in fact a different transaction within the same session)

declare
 pragma autonomous_transaction;
begin
 delete from t1 –- deleting from the parent table
 where col1 = 99;
 commit;
end;
/

declare
*
ERROR at line 1:
ORA-01502: index 'XXX.T2_IND_FK' or partition of such index is in unusable state
ORA-06512: at line 4

See how deleting from the parent table (t1) triggered an error on the index of the foreign key constraint created on the child table (t2). This is a simple way to show the mechanism used by Oracle in order to avoid a child table lock (before eventually a deadlock situation) simply by using the index on the foreign key.

July 16, 2012

Virtual column as a foreign key

Filed under: Deadlock — hourim @ 7:30 am

Recently a thread on the otn forum prompted me to review what I have already learned about the deadlock threat an unindexed foreign key can put in a mutli-user concurrent OLTP application. Particularly when the foreign key is represented by a virtual column.

In a comment of Charles ‘Hooper blog article I wrote (a) first that a function based index cannot cover the deadlock threat induced by a foreign key constraint (b) and then, a month or so after, I enriched my first comment saying that if the function based index starts by the foreign key column then it can cover that deadlock threat. How a about a foreign key represented by a virtual column? Will an index on that virtual column help?

The data model proposed by Dominic Brooks is re-used as follows:

create table t1
 (col1 number primary key);

create table t2
 (col1    number primary key
 ,status  varchar2(12) not null
 ,col2    number
 ,col2v   number generated always as (case when status = 'ACTIVE' then col2 end) VIRTUAL
  ,constraint t2_fk foreign key (col2v) references t1(col1)
  ,constraint t2_ck check (status in ('ACTIVE','INACTIVE') and (status = 'INACTIVE' or col2 is not null))
 );

insert into t1
select rownum
from dual 
connect by level <=100;

commit;

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/

Error report:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
00060. 00000 -  "deadlock detected while waiting for resource";
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000137b4-00000000        19     138    SX             19     138           S
 
session 138: DID 0001-0013-00000018	session 138: DID 0001-0013-00000018 
 
Rows waited on:
  Session 138: obj - rowid = 000137B4 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 79796, file - 0, block - 0, slot - 0)
 
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=0tqwpunag33fh) -----
DELETE FROM T1 WHERE COL1 = 99
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
28F75504         4  anonymous block

I didn’t created any index on the foreign key virtual column (col2v) and this is why when I tried to delete from the parent table having an unindexed foreign key, I have been deadlocked.

Let’s now create an index on col2v virtual column and repeat the same operations

create index t2_col2v_fk on t2(col2v);

index T2_COL2V_FK created.

select index_name, index_type
from user_indexes
where table_name = 'T2';

INDEX_NAME                     INDEX_TYPE
------------------------------ ----------------------
T2_COL2V_FK                    FUNCTION-BASED NORMAL
SYS_C0013917                   NORMAL

select index_name, column_expression
from user_ind_expressions
where index_name = 'T2_COL2V_FK';

INDEX_NAME    COLUMN_EXPRESSION
------------  ------------------ ----------------------
T2_COL2V_FK	CASE "STATUS" WHEN 'ACTIVE' THEN "COL2" END 

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);
1 rows inserted.

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/
anonymous block completed

Thanks to this new function based index created on the virtual column I succeed to avoid the deadlock threat.

Bottom line: there are two conclusions I can made from this blog
 When a virtual column is declared as a foreign key, indexing it (function based index) will cover the corresponding deadlock threat.
 Whatever this function based index expression is if the corresponding index starts with the foreign virtual column then it will cover the deadlock threat

January 19, 2012

Bitmap Indexes and Foreign key

Filed under: Deadlock — hourim @ 12:09 pm

1.     Introduction

Several months ago I have published a sql script that checks the existence or the absence of an index covering the deadlock threat of unindexed foreign key constraint when DML are issued against the parent table. That script doesn’t bother about the index type. If an index of a bitmap type exists it will tell you that it is not necessary to create an extra index for your FK constraint. Let’s see that in action

2.     Bitmap Index and FK constraint

I will start by creating a classical parent/child table relation ship

houri> create table t_parent as select * from all_users;
Table created.
mhouri> alter table t_parent add constraint t_p_pk primary key (user_id);
Table altered.
mhouri> create table t_child (user_id references t_parent, data varchar2(10));
Table created.                 

Then I will create a bitmap index to cover my Foreign Key:

mhouri > create bitmap index t_bitmap_fk_i on t_child(user_id);
Index created.

Using my traditional script to check if I need to create an index to cover my FK or not, gives me the following situation

mhouri> start index_fk.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS
------------------------- ------------------------------ ---------- ----------
T_CHILD                   T_BITMAP_FK_I                  USER_ID             1

Oh! Yes; it is telling me that I don’t need to create an extra index because there is already one index starting with the foreign key column. If I execute Tom Kyte script,  it is also not pointing out the need to index my user_id FK column

mhouri> start tkyte.sql
TABLE_NAME                     CONSTRAINT_NAME                COLUMNS
------------------------------ ------------------------------ -------------------
MY_XXX_TAB                   C_FK                           ID1,ID2

My T_CHILD table is not returned by Tom Kyte script as a table having an unindexed foreign key. So, according to both scripts it is safe to do DML operations on T_PARENT table. Let’s then submit our parent table to a classical OLTP operations

mhouri> select min(user_id), max(user_id) from t_parent;
MIN(USER_ID) MAX(USER_ID)
------------ ------------
0           73
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
2   pragma autonomous_transaction;
3   begin
4   delete from t_parent where user_id = 73;
5   commit;
6   end;
7  /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

mhouri> rollback;
Rollback complete.

Ooops!!!  The existing bitmap index does not cover the deadlock threat induced by a DML operation on a parent table having a child table with an unindexed foreign key.  The bitmap index type has to be excluded from my script. The new script index_fk when executed against the t-child table gives this:

mhouri> start index_fk_2.sql
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

And now, it’s OK. The script is telling me that I need to index my user_id FK columns because the existing bitmap index will not cover the deadlock threat.

          2.1 Remark

It would have been very bizarre that a bitmap index covers the deadlock threat induced by an unindexed foreign key because the bitmap index itself is responsible of several deadlocks situations when it is used in an OLTP application and this is why Tom Kyte script and my initial script has not foresee to exclude this type of indexes from the verification script

3.     Function Base Index and FK constraint

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child relationship it needs:

  1. to start with the FK column(or columns for a composite key)
  2. to not be of a bitmap type

 Looking to the above two conditions, there is nothing that impeaches a function based index to cover the FK threat. Let’s work with few examples:

mhouri> create index t_fbi_fk_i on t_child(user_id desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'test');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

My Function based index has only one column in it and it is a hidden column (SYS_NC00003$ for example). This is why it didn’t succeed to cover the above deadlock situation. Let’s check this with my new script:

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
no rows selected

 My new updated script is working well in this case as far as it is telling me that I need to create an extra index.  Let’s now drop and change a little bit this function based index

mhouri> drop index t_fbi_fk_i;
Index dropped.
mhouri> create index t_fbi_fk_i on t_child(user_id, data desc);
Index created.
mhouri> insert into t_child(user_id,data) values (0,'testfbi');
1 row created.
mhouri> declare
  2       pragma autonomous_transaction;
  3      begin
  4      delete from t_parent where user_id = 73;
  5      commit;
  6      end;
  7    /

PL/SQL procedure successfully completed.

The newly created function based index is covering very well the FK on the child table. My new script is also working well in this case as it is telling me that I don’t need to create an extra index

mhouri> start index_fk_2
Enter value for m_table: t_child
Enter value for m_column: user_id
Enter value for m_column2: none
Enter value for m_column3: none
<pre>TABLE_NAME                INDEX_NAME                     COLUMN_NAM COLUMN_POS

------------------------- ------------------------------ ---------- ----------

T_CHILD                   T_FBI_FK_I                     USER_ID             1

4.     Conclusion

In order for an index to successfully cover the deadlock threat induced by a DML on a parent/child table relationship it needs:

  1. to start with the FK column(or columns for a composite key in any order)
  2. to not be of a bitmap type

Even a function based index when it starts with the FK columns will play the same role as a b-tree index will do when it will be asked to cover the deadlock threats induced by a DML operation on a parent/child relationship

September 7, 2011

Deadlock – Part 3: Non Indexed Foreign key in action

Filed under: Deadlock — hourim @ 12:46 pm

A recent question on otn about deadlock came up last week. The deadlock graph was as such that I was wondering if I have already encountered it before.

Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-001a0002-0002a0fe       196     197     X            166    1835           S
TM-0000c800-00000000       166    1835    SX            196     197    SX   SSX

Albeit it is an incomplete deadlock graph because it doesn’t present the type of sql session 197 and 1835 were executing before they deadlocked, we can point out that this deadlock involves two types of locks Transaction (TX) and DML (TM) locks. I have never seen yet both type of locks within the same deadlock graph.

Knowing that TM enqueue can almost always be due to a DML operation on a parent–child table where the foreign key is not indexed, I asked then the Original Poster (OP) to check using Tom Kyte script if his OLTP application contains non indexed foreign keys. But I was a little bit struggled when the OP said that the deadlock occurs on an insert statement. Thought that he was unable to feed us with the sql the other session was doing before the deadlock occurred. In a follow up to the same thread, Jonathan Lewis gave a possible scenario for this kind of deadlock.  I decided to implement this scenario and to analyze the generated trace file. I opened two sessions (session3 is just to select from v$lock) and followed the steps described below:

session1 > select distinct sid from v$mystat;
SID
----------
1074

session1 > create table parent (x number primary key);
Table created.

session1 > create table child (y number references parent);
Table created.

session1 > insert into parent values (1);
1 row created.

session1 > insert into parent values (2);
1 row created.

session1 > insert into parent values (3);
1 row created.

session1 > insert into child values (1);
1 row created.

session1 > insert into child values (3);
1 row created.

session1 > commit;
Commit complete.

session1 > delete from child where y = 1;
1 row deleted.

session1 > delete from parent where x = 1;
1 row deleted.

At this step there are still no locks acquired as it can be verified via a simple query against v$session and v$lock

session3 > start getlocks.sql
no rows selected

Now, open a new session (session2) and issue the following insert

session2 > select distinct sid from v$mystat;
SID
-----
333

session2 > insert into child values (1);

session2 (SID 333) starts hanging!!! From session3, we can see that a lock has been acquired

session3 > start getlocks.sql
SID       WSID LOCK_TYPE           MODE_HELD          MODE_REQUESTED
---------- ---------- ------------------- ------------- ---------------------
1074      333 Transaction         Exclusive           Share

Then, go back to session 1 (SID 1074) and issue the following delete

session1 > delete from parent where x = 2;

The last delete hangs at about 3 seconds before it successfully complete.  If you manage to check the new lock situation before the deadlock happens you will see the following picture:

session3 > start getlocks.sql
SID       WSID LOCK_TYPE           MODE_HELD              MODE_REQUESTED
---------- ---------- ------------------- ----------------------------------------
333       1074 DML                 Row-X (SX)             S/ROW-X (SSX)
1074       333 Transaction          Exclusive              Share

And finally after about 3 seconds of wait, session2 with SID 333 has been deadlocked and its transaction stopped by Oracle

insert into child values (1)
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

The corresponding generated deadlock graph looks like:

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00011fe5-00000000        28      333    SX             27     1074    SX   SSX  --> the session timed out
TX-000a0013-0000039d        27     1074     X             28      333           S  --> the persistent session
session 333: DID 0001-001C-00000006      session 1074: DID 0001-001B-00000030
session 1074: DID 0001-001B-00000030     session 333: DID 0001-001C-00000006
Rows waited on:
Session 333: no row
Session 1074: no row
----- Information for the OTHER waiting sessions -----
Session 1074:
sid: 1074 ser: 93 audsid: 540090 user: 84/MOHAMED flags: 0x8000045
pid: 27 O/S info: user: SYSTEM, term: FSC401216102802, ospid: 3708
image: ORACLE.EXE (SHAD)
client details:
O/S info: user: FSC401216102802\Mohamed, term: FSC401216102802, ospid: 2816:748
machine: WORKGROUP\FSC401216102802 program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
delete from parent where x = 2
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=6cnrmyxw03k6f) -----
insert into child values (1)
===================================================

Since then, I know that an unindexed foreign key can present a deadlock situation with a graph showing both TX and TM enqueue.

March 20, 2011

Deadlock – Part 2: Unindexed Foreign keys

Filed under: Deadlock — hourim @ 8:43 am

I the Part-1 of the deadlock graph interpretation, I have introduced the deadlock graph that have been generated by the use of bitmap indexes in a heavy concurrent OLTP application. Part 2 of this series aims to show two points:

  1. How a session can deadlock itself
  2. How to know from the deadlock graph that this deadlock is due to un-indexed Foreign Keys

Let’s simulate this kind of deadlock via a simple demo:

SQL> create table p as select * from all_users;

Table créée.

SQL> select count(1) from p;

COUNT(1)
----------
31

SQL> alter table p add constraint p_pk primary key (user_id);

Table modifiée.

SQL> create table c (user_id references p, data varchar2(10));

Table créée.

SQL> select min(user_id), max(user_id) from p;

MIN(USER_ID) MAX(USER_ID)
------------ ------------
0             2147483638

SQL> insert into c(user_id,data) values (0,'test');

1 ligne créée.

SQL> declare
2  pragma autonomous_transaction;
3  begin
4  delete from p where user_id = 72;
5  commit;
6  end;
7  /

declare
*
ERREUR à la ligne 1 :
ORA-00060: détection d'interblocage pendant l'attente d'une ressource
ORA-06512: à ligne 4

SQL> rollback;

Annulation (rollback) effectuée.

So, here we are; I have simulated the ORA-00060 deadlock error when I was deleting from a parent table having a child table with un-indexed Foreign Key. Below is the corresponding deadlock graph generated internally by Oracle:

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
 ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000121c1-00000000        24      18    SX             24      18           S

session 18: DID 0001-0018-00000052    session 18: DID 0001-0018-00000052

Rows waited on:
 Session 18: obj - rowid = 000121C1 - AAAAAAAAAAAAAAAAAA
 (dictionary objn - 74177, file - 0, block - 0, slot - 0)

----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=6y61nc5zbc9w5) -----
DELETE FROM P WHERE USER_ID = 72
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
 object      line  object
 handle    number  name
23C9D90C         4  anonymous block
===================================================

Two remarks from the above deadlock graph should be emphasized:

  • (a) we have the same session 18 that is deadlocking itself and
  • (b) the deadlock is due to a TM-enqueue :
 ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000121c1-00000000        24      18    SX             24      18           S

And two conclusions can also be done from the above deadlock graph

  1. It is possible that a single session can deadlock itself when using autonomous transaction. This is why it is worth to mention that you have to use the autonomous transaction carrefully
  2. a TM-Enqueue in the deadlock graph is generally a clear indication that the deadlock is due to a DML on parent table with child table having a non-indexed Foreign Key

March 14, 2011

Deadlock – Part 1: bitmap Index

Filed under: Deadlock — hourim @ 7:40 pm

How many times I heard and read that it is absolutely not conceivable to create bitmap indexes into a heavy OnLine Transactional Process (OLTP) application into tables that are subject to concurrent DML operations. The main reason for that is because of the deadlock threat the bitmap indexes can throw into this kind of applications. The purpose of this article, in its first part, is mainly to show how to identify, via the deadlock graph that the deadlock you are experiencing is due to a bitmap index in an OLTP system.

Here below is a deadlock graph automatically generated from a real life OLTP production application I have been asked to have a close look to:


[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0017000b-0000a507        26     164     X             50     160           S
TX-0037000e-000081cc        50     160     X             26     164           S

This is the most fundamental and the first information that should retain your attention: we have two sessions 164 and 160 dead-locking each other. They are both Transaction Enqueues (TX) held on X mode and waited on S mode

Immediately when you see TX held on X mode and waited on S mode you can think of one of the following possibilities which triggered this deadlock

1.       Existence of Bitmap indexes into your OLTP application

2.       Primary key or unique key constraint overlapping during insert statement

The next bit of information within the deadlock graph will help you identifying the real cause as I did in this application. You need to look to the Sql statements done by the two sessions as shown below:


Information on the OTHER waiting sessions:
Session 160:
pid=50 serial=9778 audsid=49307085 user: 54/S102
O/S info: user: SYS_XXX-ZZZ, term: wyannhhF103, ospid: 6592:3660, machine: lkti\HJKULOO03
program: xye.exe
application name: xye.exe, hash value=2799981571

Current SQL Statement:
DELETE xxx_real_life_table WHERE xxx_ID = :B1
End of information on OTHER waiting sessions.

Current SQL statement for this session:
INSERT INTO yyy_real_life_table (yyy_ID ,DET_ID,xxx_ID) VALUES (yyy__SEQ.NEXTVAL ,0 ,:B23 )

----- PL/SQL Call Stack -----

As far as session 160 was doing a delete operation while session 164 was doing an insert operation it is impossible that for this deadlock to be caused by primary key or unique key overlapping values. It remains for me only to check the existence of the bitmap indexes into this application.


sql.world> SELECT count(1)
2    FROM all_indexes
3   WHERE index_type = 'BITMAP';

COUNT(1)
----------
10

I then have dropped those bitmap indexes making the deadlock disappear. I have also asked the local developer to explain me why they’ve created bitmap indexes into tables subject to heavy DML operation; their answer was that all those indexes are on a flag processed which have only two values : Y and N and they told me this is why they opted for this kind of bitmap indexes. My answer for them was this:

http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)