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 >
