Re: looking for blocking sql statement(s)

fitzjarrell_at_cox.net schrieb am 12.02.2008 in <c9082900-95b0-4139-a32d-be7ddf13fdba_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;

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