Hit ORA-00060: deadlock detected while waiting

[Jin Zhang]

Hi Ranchers,

Need your kindly help urgently.

I hit the above erroe when stress test my DB class. That class have 4 methods to update one table.

Method A updates column 1, 2 and last_mod_dt where column X = "xxx";
Method B updates column 1, 3 and last_mod_dt where column X = "xxx";
Method C updates column 4, 5 and last_mod_dt where column X = "xxx" and column Y = "xxx";
Method D updates column 4, 6 and last_mod_dt where column X = "xxx" and column Y = "xxx";

Column X + Y is the primary key of the table.

I inserted 10000 records with same value for X but different values for Y and started 4 process at the same time, each calles one method 10000 times.

After a while, method A hit the ora-00060 first and quits. Then later method C hit the same error and qutes. Method B and D completed successfully.

Repeated the test with 10000 recrods but no more than two records with same column X, for example:
Column X Column Y, Other columns
ABC XYZ xxxx
ABC UVW xxxx
DEF XYZ xxxx
DEF UVW xxxx
...
Then all the 4 methods completed successfully.

Any idea? According to my knowledge, if method A and B runs together, they could hit ora-00060, because maybe method A updated record 1 and is waiting for record 2, while method B updated record 2 and is waiting for record 1. But after method A hits the exception and quits, method B, C, D should not hit this problem. Am I correct?

Because I tested this with Oracle 8.1.5 and Oracle thin driver, not sure whether this is a bug of that version or not.

BTW, my code is something like below.

Thanks.

Regards,
Zhang Jin

public synchronized methodA(Record rec) throws Exception {
  try {
    if (st1 == null) {
      st1 = conn.prepareStatement("update table set "
          + "1 = ?, "
          + "2 = ?, "
          + "last_updated_dt = sysdate "
          + "where X = ? ");
    }         
    
    st1.setString(1, cntr.get1());
    st1.setString(2, cntr.get2());
    st1.setString(3, cntr.getX());
    st1.executeUpdate();
 
    conn.commit();
  } catch (SQLException sqle) {
    try {
      conn.rollback();
    } catch (SQLException sqle2) {
    }
    throw new Exception(sqle.getMessage());
  }
}

[edited to add code tags]
[ November 27, 2004: Message edited by: Jeanne Boyarsky ]

[Jeanne Boyarsky]

Jin,
It is still possible to get a deadlock. If each process writes at least one before hitting the deadlock. It is not possible for them to continue to write because of dirty reads. This has to do with the transaction settings.

[Jin Zhang]

Hi Jeanne,

Thanks for your help, are you refering to my second test? How about in the first testing, after method A hits the exception and quits (so only method B, C and D are running) the method C still can hit the exception? Do you think this is a bug of Oracle 815?

Actually the biggest problem here is that when only B, C and D are running they still hit exception. Because in the real case, method A and B will not be running at the same time.

You mentioned that I should change the transaction settings, can add more light to it?

Really appreciate your help.

Regards,
Zhang Jin

[Jeanne Boyarsky]

I was referrring to the failed test. If A quits after C has read data, you hit the dirty read. I don't think it's a bug.

Note that C & D reference the same data, so they are equivalent to A & B. Transaction rollback problems occur somewhat randomly because they depend on the order of access.

There are two ways to solve the problem:
1) Lower the transaction setting to read committed if not already there (this is the default so it might already be set.) The developer guide explains the transaction settings. Note that this does not eliminate the chance of a problem, just decreases it.
2) Make the transactions smaller. If you commit more often, there is less chance of a dirty read.

[Dilshad Marikar]

could you avoid the ora-00060 if you got a row level lock with a SELECT FOR UPDATE clause, before you did the update itself?

[Adeel Ansari]

But after method A hits the exception and quits, method B, C, D should not hit this problem. Am I correct?

yes A got this exception because of B. so if A got it first it means B will continue with it.

then C got this exception because of D. so if C got it first it means D will continue with it.

isn't it simple. if we see the conditions then we get that the condition is the same for A & B and C & D.

and my opinion is if there is even 0.1 percent chance of a deadlock then you must not run those things in paralell. In your case try to run A & C simultaneously, and B & D simultaneously.

[Dilshad Marikar]

this is really interesting... looking at this problem,i was under the wrong impression that a deadlock can occur if you try to do an update on the same row simultaneously from different sessions. But in fact an oracle deadlock error occurs when two sessions hold a resource that both want. But in this case we are dealing with only one table, so there should be only blocking, not locking...

Jin, for the table you are updating, are you updating the primary/unique key? And are there child tables that have foreign key references to it? And are those foreign key indexes unindexed? If the answers are all yes, you probably need to create an index on your foreign keys as explained in this Tom Kyte article unindexed foreign keys

[Jin Zhang]

Hi Adeel,

then C got this exception because of D

Can C and D hit deadlock? Both of them only update ONE record. I thought they will only block each other, but not deadlock.

Thanks.

Regards,
Zhang Jin

[Jin Zhang]

Hi Mohammed,

No, this is a stand-alone table. No child/parent table. No update of primary key (column X + Y is primary key). No index either (except the default index of the primary key).

The more strange thing is I cannot re-produce it on Monday. Really strange. I will pass the trace file to one Oracle guru from my company after he come back from holiday.

Thanks.

Regards,
Zhang Jin

[Adeel Ansari]

Didnt know how many rows they are updating.
But according to my knowledge if there is some deadlock detected the transaction of both the process rollbacks. It means if there is some deadlock between A & B transaction of A and B both rollbacks, this is not just only for the process A.

may be there is some record which is already updated by B but not committed yet. thus, C gets the deadlock. because a record may qualify for both the conditions in B & C.

[Dilshad Marikar]

Adeel, oracle does not rollback in the case of a deadlock. One of the 2 sessions will have its statement aborted by the ora-00060 error. The other session will block until you rollback or commit.

may be there is some record which is already updated by B but not committed yet. thus, C gets the deadlock. because a record may qualify for both the conditions in B & C.

Its not possible to get a deadlock in Oracle in the case where there is contention for the same row. In Oracle, C and any other guy trying to lock the same record will be blocked until B does rollback or commit.


There must have been some other reason .. do let me know if you find out why you got your deadlock Jin.

Thanks
Dilshad
[ November 30, 2004: Message edited by: Mohammed Dilsard ]

[Dilshad Marikar]

A separate issue is whether its important for you that updates to the same row do not wipe out each others changes - but thats a separate issue

[Adeel Ansari]

Originally posted by Mohammed Dilsard:
Adeel, oracle does not rollback in the case of a deadlock. One of the 2 sessions will have its statement aborted by the ora-00060 error. The other session will block until you rollback or commit.

i have heard of it somewhere, dont know. but after your reply i have tried. and you were right.

Its not possible to get a deadlock in Oracle in the case where there is contention for the same row. In Oracle, C and any other guy trying to lock the same record will be blocked until B does rollback or commit.

I am talking about the deadlock not the lock contention. as the both B and C may go for same records. then it is possible that C updates some record first and B updates some other records first, then become vice versa and one of it gets the deadlock statement. Because you can see with only two same records all is running good.

what you people say??

thanks. and thanks dilshad for correcting me about the deadlock result.
[ November 30, 2004: Message edited by: Adeel Ansari ]