Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Ora-00060 Deadlock Problem. Select for update behavior.

Re: Ora-00060 Deadlock Problem. Select for update behavior.

From: Madhu Konda <madhu.konda_at_sun.com>
Date: Thu, 14 Jun 2001 16:59:50 -0700
Message-ID: <3B294FF6.80F51C59@sun.com>

Hi Frank:

You are right that session B at T3 actually executes after T5 which is exactly what we intend to happen. But any idead as why it throws a deadlock once in a while when we run this design under stress. If you run it manually as mentioned above it works as expected. SessionB should wait but oracle server is detecting a deadlock condition between Session A and Session B when run under stress.

Your example given below is a perfect example of deadlock condition but the design we have do not do updates or atleast is different. I think Oracle server has a bug when it comes to handling "select ... for update" under load.

Thanks,
Madhu Konda
Oracle DBA

Frank wrote:

> Hi!
>
> I think it is because although T3 is the time that you _issue_ your
> statement, the database finds that it cannot execute it because rows in the
> potential resultset are locked. The execution is therefore postponed until
> no rows in the potential resultsset are locked. The resultset is not
> constructed in sessionB by T3 because the database is not able to execute
> statement.
> At T5 the locked are removed from the potential resultset for SessionB and
> the statement performs; it now builds the resultset based on all currently
> commited transaction in the database by T5 (that is eg. T3 and T4). Only
> id=4 is visible then.
>
> In short: the time the statement in SessionB builds its resultset is T5 and
> not T3.
> Im not sure what you mean by "deadlock"; this is a lock that is ok because
> the logic we run are able to perform because we commit when appropriate. A
> deadlock, occures when two sessions asks for one resource each, and they
> "coinsidally" must wait for eachothers commit(rollback) to get it. Something
> along the lines of:
>
> SessionA:T1
> UPDATE table
> SET col=lkjlkj
> WHERE ID=7
>
> SessionB:T2
> UPDATE table
> SET col=lkjlkj
> WHERE ID=8
>
> SessionA:T3
> UPDATE table
> SET col=lkjlkj
> WHERE ID=8
>
> SessionB:T4
> UPDATE table
> SET col=lkjlkj
> WHERE ID=7
>
> After T3 SessionA waits for SessionB (because of T2), and after T4 SessionB
> waits for SessionA (because of T1).
> Frank
Received on Thu Jun 14 2001 - 18:59:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US