Take the 2-minute tour ×

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.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
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 Jun 7 '13 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 Jun 7 '13 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 Jun 7 '13 at 21:21
add comment

Your Answer


