Re: looking for blocking sql statement(s)

From: Andreas Mosmann <>
Date: Tue, 12 Feb 2008 18:05:50 +0100
Message-ID: <> schrieb am 12.02.2008 in <>:

> 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
Received on Tue Feb 12 2008 - 11:05:50 CST

Original text of this message