Re: looking for blocking sql statement(s)

On Feb 12, 12:05 pm, Andreas Mosmann <mosm..._at_expires-29-02-2008.news- group.org> wrote:
> fitzjarr..._at_cox.net schrieb am 12.02.2008 in
> <c9082900-95b0-4139-a32d-be7ddf13f..._at_i72g2000hsd.googlegroups.com>:
>
> > likely  you can; if it's a commit or rollback then most likely you
> > cannot.
>
> Thats a pity
>
> >> Another case is that
> >> there are more than 1 statements, f.e.
>
> >> update tableA set ColumnA=1 where ColumnA=2;
> >> update tableA set ColumnA=2 where ColumnA=3;
> >> .
> >> update TableA set ColumnA=n-1 where ColumnA=n;
> > A session will be blocked by one other session as shown in the V
> > $SESSION view in the BLOCKING_SESSION column.  Thus you really can't
> > have more than one blocking SQL statement for a single blocked
> > session.  Of course you may have more than one blocked session; you
>
> why not? I can do many updates before I commit. So more than 1 statement
> can cause a blocking.
>
> > Apparently the bottleneck is the application.  But, that's simply a
> > guess.
>
> I think so. I suppose something like that:
>
> Session A:
> 1)  Update TableX ...
> 2)  Update TableY ...
> 3)  Commit;
> Session B:
> 1)  Update TableY ...
> 2)  Update TableX ...
> 3)  Commit;
>
> if both sessions run step 1 both wait for step 2 unless kill session.
> Is there a possibility to give the deadlock- detection a timeout, maybe
> after 1 minute?
>
> Thank you
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

I am not sure how you tie the blocking session to the SQL statement that is responsible for another session being blocked when it is not the blocking sessions current SQL statement or maybe its prior statement both of which can be referenced from v$session column information, but I expect that it will be one of the SQL statements referenced by v$open_cursor for the blocking session sid.

Maybe this view will be helpful.

In the case of a deadlock the ORA-00060 trace file usually contains enough information to allow you to see the two SQL statements when the problem is a straightforward two sessions want the same row for update.

HTH -- Mark D Powell -- Received on Tue Feb 12 2008 - 11:17:04 CST