0

I have a table T1 with a virtual column V1 which is generated automatically based on some condition and also a foreign key is created with Column C1 of table T2.

Now when table T1 is locked and I try to delete record from table T2 then it is not working and the sessions waits for lock acquire for limitless time EVEN THOUGH NO REFERENCE DATA AVAILABLE IN T1.

ALTER TABLE T1 ADD (V1 INVISIBLE generated always as (decode (C11, 20,C12)) 
   virtual constraint vfk REFERENCES T2 (C1))

Even I tried by making the FK as ON DELETE CASCADE, and not luck.

Please help.

It only helps if I remove the FK completely but we need them to validate the data in case any match found.

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
user3174910
  • 1
  • 2
    Why is `T1` locked, and what happens when it unlocked? How does the delete on `T2` know that the session locking `T1` isn't in the process of inserting a value into `T1` for the key you're deleting - which would leave an orphan? It has to wait until it knows that isn't the case, i.e. for the lock to be removed. What do you expect to happen? Do you want it to error rather than waiting for the lock? It isn't clear what help you want... – Alex Poole Jul 24 '17 at 11:08
  • Have you created an index on `t1(V1)` ? – krokodilko Jul 24 '17 at 17:55

0 Answers0