11.2.0.4
select from T1 for update; -- takes 1 second
T1 has a couple of foreign keys. 1 of these is on a PK of another table T2. This PK is indexed.
Couple times a day the select for update can take several minutes, when we look at dba_hist_active_sess_history, we can see the blocking session is causing enq: tx row lock contention for our select for update.
The blocking session is running a select (not for update) from a view which has a join to T2, this select takes 3-4 minutes and the block on the select for update will last as long as the query against the view is running.
Im tuning the select on the view but how would that select only against T2 cause that lock on the select for update on T1? I can guess its related to the FK but cant see how.