MS SQL and OFBiz, database locks on transactions

[mayo]

Unfortunately right now I don't have time to make a patch that would isolate this issue, but I would like to in the near future.  Also with the release of OFBiz 9.04, I am not sure if there would anyone would have this problem in the future since I am running 4.0.  Would you want this patch from an unmodified 4.0 branch?

Scott Gray-2 wrote

This looks very interesting and I would love to dig into it more, is  
there any way you could send me a patch that would enable this issue  
to be recreated on an unmodified install?

Thanks
Scott

BJ,
Thanks for looking that up for me.

[Tim Ruppert]

The 4.0 release is still being supported by the community, so it's worth a shot.

Cheers,
Tim
--
Tim Ruppert
HotWax Media
http://www.hotwaxmedia.com

o:801.649.6594
f:801.649.6595

----- "mayo" <[hidden email]> wrote:

> Unfortunately right now I don't have time to make a patch that would
> isolate
> this issue, but I would like to in the near future.  Also with the
> release
> of OFBiz 9.04, I am not sure if there would anyone would have this
> problem
> in the future since I am running 4.0.  Would you want this patch from
> an
> unmodified 4.0 branch?
>
>
>
> Scott Gray-2 wrote:
> >
> > This looks very interesting and I would love to dig into it more, is
>  
> > there any way you could send me a patch that would enable this issue
>  
> > to be recreated on an unmodified install?
> >
> > Thanks
> > Scott
> >
>
>
> BJ,
> Thanks for looking that up for me.
> --
> View this message in context:
> http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23277562.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

[mayo]

Ok.  I will attempt this after I get my project finished.  Also some good news: My first tests show using jTDS and ReadCommitted with READ_COMMITTED_SNAPSHOT set ON work through the process I described before with no deadlocks.

 

Tim Ruppert wrote

The 4.0 release is still being supported by the community, so it's worth a shot.

Cheers,
Tim

[mayo]

I have been running OFBiz through normal usage tests for a client for almost a month. Employees are working around 300 orders throughout an 8 hour day with 2-3 warehouse users and 1-2 customer service users.  I am getting deadlocks throughout the day with the OFBiz 4.0 code that do not have any custom modifications.  

I switched to SQLServerDriver, set lockTimeout to 3 minutes, set the isolation level to ReadUncommitted, and enhanced the performance of the DB server, but I still get locks and deadlocks.  This wouldn't be a problem because the user could just re-submit their request, but it becomes a problem with submitting authorizations/captures/refunds to the payment service.  If the request is successful but writing the successful response to the DB is deadlocked, the order is stuck--another request to the payment service will fail.

Has anyone had these problems with OFBiz 4.0 and SQLServer 2005?  Also, would it be possible to give a kick-start on integrating the OFBiz trunk database pool logic into OFBiz 4.0?

[mayo]

Just to update this issue if anyone else is having the same problem:

It turns out David E Jones-3 was right that my poor transactional programming in the custom import was causing deadlocks of other processes running at the same time.  I followed OFBiz's example by creating an entity list of toBeStored and running a transaction only around the creation of that list and the createOrder function.  Users say the system is running noticeably faster and with fewer-to-no deadlocks.

mayo wrote

I have been running OFBiz through normal usage tests for a client for almost a month. Employees are working around 300 orders throughout an 8 hour day with 2-3 warehouse users and 1-2 customer service users.  I am getting deadlocks throughout the day with the OFBiz 4.0 code that do not have any custom modifications.  

I switched to SQLServerDriver, set lockTimeout to 3 minutes, set the isolation level to ReadUncommitted, and enhanced the performance of the DB server, but I still get locks and deadlocks.  This wouldn't be a problem because the user could just re-submit their request, but it becomes a problem with submitting authorizations/captures/refunds to the payment service.  If the request is successful but writing the successful response to the DB is deadlocked, the order is stuck--another request to the payment service will fail.

Has anyone had these problems with OFBiz 4.0 and SQLServer 2005?  Also, would it be possible to give a kick-start on integrating the OFBiz trunk database pool logic into OFBiz 4.0?

[Abhishake Agarwal]

Hi,

We had the same issue with postgres 7.14 (runAsync service for email, when
quich ship order is clicked) but when we upgraded the Postgres to 8.x it
started working fine.


Regards,
Abhishake

On Fri, Jun 26, 2009 at 11:12 PM, mayo <[hidden email]> wrote:

>
> Just to update this issue if anyone else is having the same problem:
>
> It turns out David E Jones-3 was right that my poor transactional
> programming in the custom import was causing deadlocks of other processes
> running at the same time.  I followed OFBiz's example by creating an entity
> list of toBeStored and running a transaction only around the creation of
> that list and the createOrder function.  Users say the system is running
> noticeably faster and with fewer-to-no deadlocks.
>
>
>
> mayo wrote:
> >
> > I have been running OFBiz through normal usage tests for a client for
> > almost a month. Employees are working around 300 orders throughout an 8
> > hour day with 2-3 warehouse users and 1-2 customer service users.  I am
> > getting deadlocks throughout the day with the OFBiz 4.0 code that do not
> > have any custom modifications.
> >
> > I switched to SQLServerDriver, set lockTimeout to 3 minutes, set the
> > isolation level to ReadUncommitted, and enhanced the performance of the
> DB
> > server, but I still get locks and deadlocks.  This wouldn't be a problem
> > because the user could just re-submit their request, but it becomes a
> > problem with submitting authorizations/captures/refunds to the payment
> > service.  If the request is successful but writing the successful
> response
> > to the DB is deadlocked, the order is stuck--another request to the
> > payment service will fail.
> >
> > Has anyone had these problems with OFBiz 4.0 and SQLServer 2005?  Also,
> > would it be possible to give a kick-start on integrating the OFBiz trunk
> > database pool logic into OFBiz 4.0?
> >
>
> --
> View this message in context:
> http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p24224517.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
>