I had a deadlock issue which was easy to fix by changing application logic, but I was under impression Oracle would behave differently, and the deadlock would never happen. I have two tables, for example
table1( table1_id (PK), num1, num2); table2(table2_id(PK), table1_id(FK to table1,indexed), low_cardinality_column, num3, num4);
Table2 also has row level
after update trigger which updates
table1.num1 = table1.num1 + :NEW.num3 where table1.table1_id = :NEW.table1_id).
First process executes
UPDATE table2 set num3 =1 where low_cardinality_column =:bind_var (no index on
low_cardinality_column , typically couple thousands records affected).
Second process updates table2 and table1 in one transaction with
UPDATE table2 SET num4 = :bind_var4 WHERE table2_id = :bind_var_id RETURNING table1_id INTO :out_var UPDATE table1 SET num2 = :new_num2_val WHERE table1_id = :out_var
Trace showed quite a few deadlocks between those 2 processes if they happen to run at the same time, and I'm a bit confused by that. I understand deadlocks must have happened in case second process updated tables in reverse order (
table2), but in this particular case I thought the engine won't actually begin update until it gets RX lock on each record which has to be updated (thus one process would wait for another to finish) . If it's not guaranteed, then explanation of the deadlock is obvious : Process1 locks records in undefined order, and Process 2 happened to update the row in
table2 not yet locked and tries to update the row in
table1 already updated by Process 1. In case of SQLServer I would be 100% sure that's the case, but I'm still quite new to Oracle...
I wonder if someone could clarify the issue. Thank you. I use 10g if that matters.