Deadlocks after upgrade

  • From: "cichomitiko gmail" <cichomitiko@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 12 May 2005 12:47:39 +0200

Hi all,
after upgrading an Oracle instance from 8.1.7.4 to 9.2.0.5 it began to log a 
lot of deadlocks. It's was a critical production system so we have to find a 
rapid solution, there was no time to analyze the problem appropriately(... I 
know ... )
The current statement in the trace files was:

SELECT S.ROWID RWD FROM (SELECT ROWID RWD FROM VSCO_OA_SMS_CHMO WHERE VSMS_TIME 
BETWEEN (:B2 - 1) AND :B2 AND VSMS_STATUS = :B1 O
RDER BY VSMS_TIME, VSMS_ID ) L, VSCO_OA_SMS_CHMO S WHERE S.ROWID = L.RWD AND 
ROWNUM <= :B3 FOR UPDATE OF VSMS_STATUS

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000d001f-00018a68        20     225     X             22     209           X
TX-0010000f-0000440c        22     209     X             20     225           X
session 225: DID 0001-0014-0000029E     session 209: DID 0001-0016-0000030F
session 209: DID 0001-0016-0000030F     session 225: DID 0001-0014-0000029E


My first idea was: CBO/access path/performance issue - may be the first session 
is not able to finish the work and deadlocks with the subsequent that is trying 
to do the same work in different order ....I was not convinced, but, in that 
circumstances, all that I needed was an immediate solution.
All I did was "analyze table VSCO_OA_SMS_CHMO compute statistics for table for 
all indexes for all columns size 254" (before the upgrade the table was 
analyzed via the "analyze table ... estimate statistics sample 10 percent" 
command) and ... that was the end of all those deadlocks .... 

The problem is that I didn't check the access path before the analyze that I 
did and I don't know how it changed. Because of the bind variables I had to 
trace it first(event 10046) and then check the execution plan with the values. 

I didn't understand what happened. Before I write this email I tried to do a 
little test to verify if the locking mechanism of the "select for update" 
statement is changing with the access path - FULL vs. INDEX/BY ROWID and I saw 
that it remains the same: TM   Row Exclusive, no metter how you access the 
table(FULL vs. INDEX/BY ROWID).

Could someone explain this situation?


Kind Regards
Dimitre

--
//www.freelists.org/webpage/oracle-l

Other related posts: