Mohamed Houri’s Oracle Notes

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

About these ads

1 Comment »

  1. I am sure this article has touched all the internet users, its really really
    nice piece of writing on building up new webpage.

    Comment by woolrich spaccio — December 12, 2012 @ 9:34 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

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.

EU Careers info

Your career in the European Union

Oracle SQL Tuning Tools and Tips

SQL Tuning, eAdam, eDB360, SQLT, SQLHC

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

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

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: