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
[edited to add code tags] [ November 27, 2004: Message edited by: Jeanne Boyarsky ]
Jeanne Boyarsky
,
author & internet detective
staff
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.
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
,
author & internet detective
staff
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.
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.
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
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.
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.
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 ]
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 ]
Post by:autobot
Always! Wait. Never. Shut up. Look at this tiny ad.
a bit of art, as a gift, that will fit in a stocking