Virtual column as a foreign key

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