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 <[email protected]> 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 <[email protected]> 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 > > >
