Locking records in Oracle during update

lockingoracleoracle-10g

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.num (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 (table1, then 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.

Best Answer

You are right: process 2 begins with update on Table2, but encounters deadlock with update on Table1 because the row is being updated by process 1 (and not yet committed).

Your assumption that the database "knows" it will encounter a deadlock in process 2 and will deny the transaction is wrong. The database is unaware of future statements in a transaction, therefore it can not avoid a deadlock.

Related Question