1

I've got two Oracle queries running in different sessions, which are deadlocking, and I'm having trouble seeing why that's happening.

The query in session 1 is this:

UPDATE REFS R SET R.REFS_NAME = :B2 WHERE R.REFS_CODE = :B1

The query in session 2 is this:

UPDATE REFS R SET R.STATUS_CODE = :B3, R.STATUS_TYPE = :B2 WHERE R.REFS_CODE = :B1

Each is surrounded by a cursor that loops through a selection of primary key values. When these queries are run at the same time, they deadlock. REFS_CODE is the primary key, and the Oracle trace shows that they're updating different rowids. The primary key is indexed, obviously, and there are some foreign key constraints, which are supported by indexes as this has been a problem for us in the past.

Moving into the realm of desperation, I've tried disabling triggers on the table, and that didn't help. Also tried using autonomous transactions, but that made things much worse.

Is there something I'm missing? Thanks for any help!

Kieran
  • 718
  • 1
  • 16
  • 33
  • 1
    Only tangential: but why are you using cursors instead of just updating the range of rows you want (perhaps using something like the second answer here: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join)? – siride Apr 25 '13 at 22:12
  • What kind of transactions do you have? – Marvo Apr 25 '13 at 22:13
  • @Marvo not sure how to answer that! The usual kind? They're not AUTONOMOUS_TRANSACTIONs, if that's what you're asking :) – Kieran Apr 25 '13 at 22:14
  • @siride ah, sorry, I didn't make that clear - they were more along the lines of updates with subqueries in them, but a previous attempt to fix this problem has left them with cursors, like above. – Kieran Apr 25 '13 at 22:15
  • Might them be updating the same registers in the other tables because of the foreign keys? – drewich Apr 25 '13 at 22:20
  • 5
    A deadlock wouldn't necessarily have both sessions updating the same rowid at the same time, if that's what your trace reference means; just that the each rowid has already been updated by the other session, maybe much earlier. Do you expect both cursors to pick the same keys at some point, and in a different order? Or are you saying the same key should never be picked by both cursors? – Alex Poole Apr 25 '13 at 22:43
  • @AlexPoole the same key should never be used by both; I hadn't thought of that, that might be the cause of the issue here. :) – Kieran Apr 25 '13 at 23:13

1 Answers1

3

If a commit is happening after the entire cursor batch is updated, then it may just be a straight forward deadlocking scenario where the two cursors are operating on the same rows but in a different order.

  • Assume session 1 has cursor set 1 and is updating refs_code 1 and refs_code 2 in that order before attempting a commit.
  • Assume session 2 has cursor set 2 and is updating refs_code 2 and refs_code 1 in that order before attempting a commit.

Then, interleaving the updates:

time  cursor set 1    cursor set 2
====  ============    ============

t1    refs_code 1     -
t2    -               refs_code 2
t3    refs_code 2     -
t4    -               refs_code 1

at t3, cursor set 1 is waiting on cursor set 2 to commit refs_code 2 at t4, cursor set 2 is waiting on cursor set 1 to commit refs_code 1

The two transactions are waiting on different rowids. If that is the case, you may be able to add an order by (in the same direction) to both cursors to help avoid this.

Glenn
  • 8,932
  • 2
  • 41
  • 54
  • Thanks, this was the problem! A bug was causing session 1 and session 2 to update the same records. Now they're separate lists, the deadlock vanishes. – Kieran Apr 26 '13 at 14:03