Re: Dealock on Rollback Segment (I think)

One of the 8 process get's an ora-00060. I have included the Deadlock graph from the trac file below:

• 2000-12-28 09:13:15.153
• SESSION ID:(27.30638) 2000-12-28 09:13:15.088 DEADLOCK DETECTED Current SQL statement for this session: UPDATE CUST SET CONTRIB_01=:b1,CONTRIB_02=:b2,CONTRIB_03=:b3,GROSS_CONTRIB=:b4,PROF=:b4, CACC_01=:b1,CACC_02= :b2,CACC_03=:b3,TOTAL_CACC=:b9,EQUITY_01=:b10,EQUITY_02=:b11,EQUITY_03=: b12,TOTAL_EQUITY=:b13,ROE_01=:b14,RO E_02=:b15,ROE_03=:b16,TOTAL_ROE=:b17,ROE_SEGMENT=:b18,PRI_DEP_BAL=:b19,P RI_LN_BAL=:b20 WHERE ROWID = :b21 ----- PL/SQL Call Stack ----- object line object handle number name b3730844 135 package body CPDEV.UPD_AGG_CUST b371b0f0 1 anonymous block The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock: Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00060026-0000a001 35 27 X 36 25 S TX-0004002b-00009e12 36 25 X 35 27 S session 27: DID 0001-0023-00000002 session 25: DID 0001-0024-00000002 session 25: DID 0001-0024-00000002 session 27: DID 0001-0023-00000002 Rows waited on: Session 25: no row Session 27: no row

  ---

Session 27 got rolled back. Session 25 then ran to completion.

According to OEM (Top Sessions), the objects locked were rollback segments. I don't think I entirely understand how the rollback segment is involved in this. Here is a scenario I imagine: If a row is being updated, does the entire block that the row is in get moved to a rollback segment? If process one has a lock on that rollback segment and then process two needs to update a different row in the same block, which would now be in the rollback segment, then process two would have to wait. Correct? If process one then needs a row from a block that is in a rollback segment that process two has locked, we would have a deadlock.

Is this a realistic scenario? If so, how do I avoid it? If not, can you shed some light or give me another direction to look into?

Again, thanks for your help.

Patrick

In article <92frts$iaq$1_at_nnrp1.deja.com>,   Luggy <dgh_consulting_at_my-deja.com> wrote:
> Hi Patrick.
>
> We might need a bit more info on this one.
>
> What makes you think it is a deadlock? Do any of the processes roll
> back? Do any of the processes complete? Do some/all appear to be
> hanging? If so, are you sure they are waiting on resource?
>
> Please post formatted output from v$lock (I can post a script to do
> this if required - there are many out there).
>
> Also, what are the differences between your test and production
> environments? Have you got less/more larger/smaller rollback segments
> on live?
>
> Dave.
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Dec 28 2000 - 11:32:57 CST