Hi Tom,
Thank you for the wealth of knowledge on this site. I have read many, many threads on this site describing situations similar to what I am about to describe. It's possible as well I am completely off here and this isn't related to FK at all. Just when I begin to get better at investigating this type of thing I realize how much more I have to learn. Thank you, in advance, for your help!

We are experiencing deadlocks. We do not have an index containing the FK column on the leading edge on the child table. The issue is, everything I have read on this site and elsewhere suggests that unless we are updating the PK on the parent table, we should not deadlock under these circumstances (due to missing FK index). We are not updating the PK. Here's some table and trace information (I have a couple of different trace files with slightly different updates, but same objects involved in the graph). I changed the table names.

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TM-000238c5-00000000 644 1272 SX 627 233 SSX
TM-000239cd-00000000 627 233 SX 644 1272 S

session 1272: DID 0001-0284-00002DD2 session 233: DID 0001-0273-000035AD
session 233: DID 0001-0273-000035AD session 1272: DID 0001-0284-00002DD2

Rows waited on:
Session 1272: obj - rowid = 000239CD - AAAAAAAAAAAAAAAAAA
(dictionary objn - 145869, file - 0, block - 0, slot - 0)
Session 233: obj - rowid = 000238C5 - AAAAAAAAAAAAAAAAAA
(dictionary objn - 145605, file - 0, block - 0, slot - 0)


Objects involved:
000238c5 - ID 145869 - Table name: FEE : Primary key on column USER_FEE_ID
000239cd - ID 145605 - Table name: CART_ITEM_FEE : Foreign key on USER_FEE_ID references fee.user_fee_id. No index on this table with USER_FEE_ID on leading edge.


Queries:
UPDATE FEE F SET USER_FEE_STATUS_ID = :B4 , SYSTEM_MODIFY_USER = :B3 , EXPIRATION_DT = :B2 WHERE USER_FEE_ID = :B1

and

UPDATE FEE SET USER_FEE_STATUS_ID = 15 , EFFECTIVE_DT = SYSDATE , SYSTEM_MODIFY_USER = :B2 WHERE USER_ID = :B1 AND USER_FEE_STATUS_ID = 14


Unfortunately, I do not have values for the bind variables. I have created the tables here:
https://livesql.oracle.com/apex/livesql/s/d9lboxg2q8ave9pngrj5r90r1

Thank you very much!

Update as requested: There is no other query present in the .trc file. These are it. I copy/pasted what you see above and change table names slightly. Interestingly enough, this issue was resolved by adding an index to CART_ITEM_FEE(USER_FEE_ID). This just reinforces to me that this was caused by a missing FK index, but again, since we weren't updating the PK I'm not sure why this would cause a deadlock. I'd be happy to upload the full .trc file if that will help.

Sincerely,
Brien

create table t1 (
  t1_id int primary key,
  status int
);
create table t2 (
  t2_id int primary key,
  t1_id int references t1 (t1_id) on delete cascade
);

insert into t1 values (1, 15);
commit;

delete t1 where t1_id = 2;

select * from dba_dml_locks;

SESSION_ID  OWNER  NAME  MODE_HELD   MODE_REQUESTED  LAST_CONVERT  BLOCKING_OTHERS  
93          CHRIS  T1    Row-X (SX)  None            1             Not Blocking     
93          CHRIS  T2    Row-X (SX)  None            1             Not Blocking