Ode process stalling randomly

[Pratik Bhatt <bh...@gmail.com>]

Hi,

As i mentioned in my earlier query, We have deployed ode on jboss 5 with MS
SQL Server 2005 using hibernate as the persistence manger. The deadlock
issues seem to have resolved by creating various indices on the tables, but
a new issue has turned up.

The processes seem to be stalling completely at random. i.e. many processes
are progressing simultanously and then suddenly everything comes to a halt.
We dont loose data, but time as the system does seem to began reprocessing
after 15 mins.

Looking throught sql server, we observe that some of the sessions are unable
to commit for a very long about of time, which blocks up every thing else.
The last query that is run on that session is as follows.

(@P0 bigint)select properties0_.MEX as MEX0_, properties0_.VALUE as VALUE0_,
properties0_.NAME as NAME0_ from BPEL_MEX_PROPS properties0_ where
properties0_.MEX= @P0

Which seems to indicate that the processing for some process is over and its
trying to clear off but is unable to commit.

Also one thing we noticed was that ODE is having database level locks?

Thanks for everyones help in advance.

Regards
- Pratik Bhatt

Re: Ode process stalling randomly

[Rafal Rusin <ra...@gmail.com>]

Well, this information is not sufficient for diagnosing problem.
Could you provide stacktraces of blocked sql queries?

You can do it this way:
- make some traffic until block happens
- reduce traffic
- do kill -3 on PID or attach debugger
- provide us queries along with stacktraces of blocked threads

2009/12/18 Pratik Bhatt <bh...@gmail.com>:
> Hi,
>
> As i mentioned in my earlier query, We have deployed ode on jboss 5 with MS
> SQL Server 2005 using hibernate as the persistence manger. The deadlock
> issues seem to have resolved by creating various indices on the tables, but
> a new issue has turned up.
>
> The processes seem to be stalling completely at random. i.e. many processes
> are progressing simultanously and then suddenly everything comes to a halt.
> We dont loose data, but time as the system does seem to began reprocessing
> after 15 mins.
>
> Looking throught sql server, we observe that some of the sessions are unable
> to commit for a very long about of time, which blocks up every thing else.
> The last query that is run on that session is as follows.
>
> (@P0 bigint)select properties0_.MEX as MEX0_, properties0_.VALUE as VALUE0_,
> properties0_.NAME as NAME0_ from BPEL_MEX_PROPS properties0_ where
> properties0_.MEX= @P0
>
> Which seems to indicate that the processing for some process is over and its
> trying to clear off but is unable to commit.
>
> Also one thing we noticed was that ODE is having database level locks?
>
> Thanks for everyones help in advance.
>
> Regards
> - Pratik Bhatt
>


Regards,
-- 
Rafał Rusin
http://rrusin.blogspot.com
http://www.touk.pl
http://top.touk.pl

Re: Ode process stalling randomly

[Pratik Bhatt <bh...@gmail.com>]

Ciaran, Thank you for the suggestion. We did try that earlier but we would
still get random stalling. It looks like the ODE_JOB table is the final
hurdle, as there is always a lot of things blocking on it. We are
investigating indices on it, hopefully it should resolve the problem.

What seems to be working though is setting the transaction isolation to READ
UNCOMMITTED. This way nothing ever blocks, and there doesn't seem to be any
more blocking even when running a 1000 processes/threads simultaneously. I
still need to do more testing with this setting, to be sure that it is not
messing anything up. But from our initial tests from yesterday, it seems
everything is working fine.

So this could be the final answer if we are unable to resolve the issues
using Indices/READ COMMITTED SNAPSHOT.

Regards
- Pratik

On Sat, Dec 19, 2009 at 2:48 AM, Ciaran <ci...@gmail.com> wrote:

> Just a general hint with SQL Server 2005, take a look at Snapshot
> isolation  ( http://www.mssqltips.com/tip.asp?tip=1081 )
>
> I discovered in one of my products that enabling these settings gave
> greatly
> improved performance around the locking that occurs within SQL server, it
> may
> help you, just a thought (and one you may already be aware of) .  This
> setting
> is not on by default
>
> Many thanks
> - cj.
>
>
> On Fri, Dec 18, 2009 at 3:23 AM, Pratik Bhatt <bh...@gmail.com> wrote:
> > Hi,
> >
> > As i mentioned in my earlier query, We have deployed ode on jboss 5 with
> MS
> > SQL Server 2005 using hibernate as the persistence manger. The deadlock
> > issues seem to have resolved by creating various indices on the tables,
> but
> > a new issue has turned up.
> >
> > The processes seem to be stalling completely at random. i.e. many
> processes
> > are progressing simultanously and then suddenly everything comes to a
> halt.
> > We dont loose data, but time as the system does seem to began
> reprocessing
> > after 15 mins.
> >
> > Looking throught sql server, we observe that some of the sessions are
> unable
> > to commit for a very long about of time, which blocks up every thing
> else.
> > The last query that is run on that session is as follows.
> >
> > (@P0 bigint)select properties0_.MEX as MEX0_, properties0_.VALUE as
> VALUE0_,
> > properties0_.NAME as NAME0_ from BPEL_MEX_PROPS properties0_ where
> > properties0_.MEX= @P0
> >
> > Which seems to indicate that the processing for some process is over and
> its
> > trying to clear off but is unable to commit.
> >
> > Also one thing we noticed was that ODE is having database level locks?
> >
> > Thanks for everyones help in advance.
> >
> > Regards
> > - Pratik Bhatt
> >
>

Re: Ode process stalling randomly

[Ciaran <ci...@gmail.com>]

Just a general hint with SQL Server 2005, take a look at Snapshot
isolation  ( http://www.mssqltips.com/tip.asp?tip=1081 )

I discovered in one of my products that enabling these settings gave greatly
improved performance around the locking that occurs within SQL server, it may
help you, just a thought (and one you may already be aware of) .  This setting
is not on by default

Many thanks
- cj.


On Fri, Dec 18, 2009 at 3:23 AM, Pratik Bhatt <bh...@gmail.com> wrote:
> Hi,
>
> As i mentioned in my earlier query, We have deployed ode on jboss 5 with MS
> SQL Server 2005 using hibernate as the persistence manger. The deadlock
> issues seem to have resolved by creating various indices on the tables, but
> a new issue has turned up.
>
> The processes seem to be stalling completely at random. i.e. many processes
> are progressing simultanously and then suddenly everything comes to a halt.
> We dont loose data, but time as the system does seem to began reprocessing
> after 15 mins.
>
> Looking throught sql server, we observe that some of the sessions are unable
> to commit for a very long about of time, which blocks up every thing else.
> The last query that is run on that session is as follows.
>
> (@P0 bigint)select properties0_.MEX as MEX0_, properties0_.VALUE as VALUE0_,
> properties0_.NAME as NAME0_ from BPEL_MEX_PROPS properties0_ where
> properties0_.MEX= @P0
>
> Which seems to indicate that the processing for some process is over and its
> trying to clear off but is unable to commit.
>
> Also one thing we noticed was that ODE is having database level locks?
>
> Thanks for everyones help in advance.
>
> Regards
> - Pratik Bhatt
>