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