3

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
3
  • Thanks for your answer, but I didn't expect the engine to know about future statements... I just thought no changes would be made by process 1 until it locks all records that satisfy condition. However, if I interpret deadlock culprit correctly, Process 1 (say 1000 rows should be updated) begins update, locks for instance 100 rows, updates them (which in turn fires trigger), and waits for locking 101st row...
    – a1ex07
    Commented Jun 7, 2013 at 13:10
  • @a1ex07 OK, I misunderstood you then. Yes, also in one UPDATE-statement, Oracle does not necessarily lock all rows at once.
    – Martin Schapendonk
    Commented Jun 7, 2013 at 13:24
  • A change to rows requires that a copy of the pre-change block is written to undo, and the block header is updated with locking information (the ITL -- interested transaction list) and a reference to the location in undo where the pre-change block is held. This suggests that all of the rows in a block that are to be modified are locked and then changed, so it is probably more correct to think of the "batch size" of row locking to be a block, not a particular number of rows. This is not to suggest that locking is at the block level though, just that the undo management is. Oracle only lock rows.
    – David Aldridge
    Commented Jun 7, 2013 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.