You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by mayo <al...@hotmail.com> on 2009/04/24 23:55:01 UTC
MS SQL and OFBiz, database locks on transactions
I am having problems getting OFBiz to work with MS SQL. OFBiz works with MS
SQL during the setup (creating tables, importing all seed data), but it
seems to be having database locks when I import an order. This import
service is a custom service I developed and it works on MySQL with no
problems.
Without getting into detail of the custom code, it basically creates a new
transaction and creates an order. The code freezes when I call
dispatcher.runAsync("sendOrderConfirmation"). I've parsed deep into this
code and it specifically freezes at the point where it attempts to run this
SQL: SELECT RUNTIME_INFO, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
CREATED_STAMP, CREATED_TX_STAMP FROM dbo.RUNTIME_DATA WHERE
RUNTIME_DATA_ID=?
It looks like the Async call creates it's own new transaction and is blocked
because the previous transaction also accessed RUNTIME_DATA.
The database shows 2 processes are blocked from a different, single process.
It looks like this "blocker" process is the main transaction I open when I
create the order. I am sure one blocked processes is the transaction that
is opened when I call sendOrderConfirmation. The profiler shows the
"blocker" process last executes "sp_cursorclose".
I have only found 1 related posting that is of no help:
http://lists.ofbiz.org/pipermail/users/2004-January/002817.html
We are using OFBiz 4.0 and jTDS v.1.2.2 for the driver. I have
useCursors=true set in the entityengine jdbc-uri config. We have followed
the directions for jTDS to install the XA procedures. We have tried MS
SQL's driver and have the same exact problem.
Does anyone have any ideas as to where we messed up? I am told the XA
features are suppose to handle this. Is there any way we can verify that we
installed XA correctly? Am I using OFBiz transactions wrong?
// begin a transaction
boolean beganTransaction = false;
beganTransaction = TransactionUtil.begin(transactionTimeout);
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23225473.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
I noticed that we were missing steps on enabling XA transactions for MS SQL.
I followed the official install steps
(http://msdn.microsoft.com/en-us/library/aa342335(SQL.90).aspx) and still
having the same error. I am now using the MSSQL JDBC 1.1 driver.
Should the profiler show the use of the XA procedures? Do I need to set the
pool-xa-wrapper-class in <inline-jdbc>?
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23226212.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
I have my services use-transaction set to false and manually create a
transaction before importing an order. In fact, I copied all my code from
existing OFBiz processes. The difference (and problem) is I am creating the
whole order at once (customer, address, payment methods). Each step is
hooked into existing OFBiz methods and services.
This specific deadlock is happening when I call OFBiz services through
runSync. One process is kicked off when I call
dispatcher.runSync("createPerson") and another is kicked off in the OFBiz
method where a payment response is created (maybe checkout.processPayment).
If executions are their own processes, then it's not using my main
transaction and they are creating their own transactions, right? Should
runSync be shooting off a new process? Is this something that is working
differently in the trunk version?
I think it's deadlocking because the "side process" with its own transaction
is trying to access something the main process has accessed (a common table
like runtime_data or sequence_value_item) or visa versa. Since it's locking
up with the main process is calling runAsync, I would say the latter.
If what I say is true, then the solution isn't so simple--I would need to
modify my code to not call any services synchronously. I would need to copy
all the code in the needed service into my main function that creates the
order.
I will continue to try to create a small example that exposes the error so
that my custom code will not be in question.
David E Jones-3 wrote:
>
>
> It's fairly simple, the code you have written (including configuration
> settings for service defs, etc) has caused a deadlock. To fix it,
> change your code.
>
> As for MySQL versus MSSQL... well... it is an oft-lamented fact that
> MySQL is not as strict as other databases about pesky things like
> "transactions" and "foreign keys". Of course, in this case it may
> simply be different transaction isolation levels you are using for the
> different databases. It is certainly the case the a deadlock in one tx
> isolation level will have no problem in a less strict one.
>
> -David
>
>
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23235233.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
BJ Freeman wrote:
>
> I have a similar routine that imports realtime orders into ofbiz.
> it has no problems, though I am using postgresql.
> it processes up to 15 persec.
>
It would be interesting for me to see if it works on postgresql. It has
worked on MySQL with no problems for 4-5 months. What do you have your
isolation level set to on postgresql?
BJ Freeman wrote:
>
> Are you synchronous all the way through the process before starting a
> new process? This means that no other process, you are doing is starting
> before the last one finishes.
>
Yes my process is completely synchronous except for when I call
sendConfirmation. Other than that, I use existing OFBiz services (called
synchronously) and existing OFBiz methods (that sometimes call their own
services synchronously).
BJ Freeman wrote:
>
>> If what I say is true, then the solution isn't so simple--I would need to
>> modify my code to not call any services synchronously. I would need to
>> copy
>> all the code in the needed service into my main function that creates the
>> order.
>>
> do you mean Asynchronous?
>
No, I actually mean synchronously because from my current findings it looks
as though my synchronous calls are, for some reason, spawning separate
database threads. I am assuming these new threads have their own
transactions, thus creating the deadlock. One way to solve this is to ensure
my process does not use runSync. This is still an assumption though,
because I need to verify that the runSync is spawning its own thread with
its own transaction.
BJ Freeman wrote:
>
> as thought how about going into the database and create some triggers to
> help the deadlocks, as a last resort.
>
This is a very good suggestion. I will be doing more research on the exact
problem and will probably try a different isolation level, ReadUncommited.
If that doesn't work and my research doesn't lead me elsewhere, I will
attempt to work some triggers in.
Thanks BJ.
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23238192.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by Abhishake Agarwal <ab...@gmail.com>.
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 <al...@hotmail.com> 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.
>
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
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.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
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-tp23225473p23768812.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
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
>
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23281546.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by Tim Ruppert <ti...@hotwaxmedia.com>.
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" <al...@hotmail.com> 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.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
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.
Re: MS SQL and OFBiz, database locks on transactions
Posted by Scott Gray <sc...@hotwaxmedia.com>.
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
HotWax Media
http://www.hotwaxmedia.com
On 28/04/2009, at 7:56 AM, mayo wrote:
>
> I found out more about how the deadlock is happening, but first let
> me say
> that setting the isolation level to read-uncommitted worked. Does
> anyone
> have any experience using read-uncommitted with OFBiz 4.0 on MSSQL? I
> cannot figure out the logic behind the process threads in the
> database, but
> I've meticulously stepped through the code to find out when they are
> created. Can anyone tell me if this is characteristic of the 4.0
> thread
> logic and is different/fixed in the trunk logic?
>
> ---------------
>
> The first thread, lets say thread-A, is used to start the creation
> of the
> order. It looks like a second thread, thread-B, is created to handle
> "side
> jobs" like updating and selecting from the JOB_SANDBOX and to get the
> SEQUENCE_VALUE for an entity. I was incorrect in saying synchronous
> executions start a new thread because thread-A continues to handle
> almost
> every command in the main process flow.
>
> The first block comes in the createOrder method. When
> "addSuggestionsToShoppingList" is triggered, Thread-A inserts a row
> into
> JOB_SANDBOX. When Thread-B continues its "side jobs" and tries to
> run an
> update on JOB_SANDBOX, but it cannot because Thread-A has an open
> transaction. This block doesn't freeze the application, so if the
> next
> event didn't happen, everything would be fine.
>
> The second block (and deadlock) happens when I try to call
> runAsync("sendEmailConfirmation"). The main process flow begins to
> enter in
> new RuntimeData and JobSandbox entities with storeAll(). The
> GenericDelegator goes to locate the primary key for the RuntimeData
> with a
> select statement. For some reason a third thread, Thread-C, is used
> to do
> this select statement--maybe because Thread-B is blocked. When
> Thread-C
> executes the select on RUNTIME_DATA, the deadlock occurs because
> Thread-B
> previously inserted on RUNTIME_DATA, but can't finish.
>
> To summarize the confusion, Thread-A is waiting on Thread-C, who is
> waiting
> on Thread-B, who is waiting on Thread-A. According to the code,
> everything
> is using the same transaction. The only time I see the main
> transaction is
> suspended is to run the payment services, but this code executes
> fine. I can
> see how my code facilitates the problem by executing all the methods
> and
> services to create a full order, but I this it is the pool/thread
> logic that
> creates the deadlock.
>
> ---------------
>
> Does this happen to spark anyone's memory about a problem they've
> had with
> OFBiz 4.0? Even though ReadUncommitted may work, I want information
> to
> decide if I will need to backport the trunk pool logic.
> --
> View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23263811.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
I found out more about how the deadlock is happening, but first let me say
that setting the isolation level to read-uncommitted worked. Does anyone
have any experience using read-uncommitted with OFBiz 4.0 on MSSQL? I
cannot figure out the logic behind the process threads in the database, but
I've meticulously stepped through the code to find out when they are
created. Can anyone tell me if this is characteristic of the 4.0 thread
logic and is different/fixed in the trunk logic?
---------------
The first thread, lets say thread-A, is used to start the creation of the
order. It looks like a second thread, thread-B, is created to handle "side
jobs" like updating and selecting from the JOB_SANDBOX and to get the
SEQUENCE_VALUE for an entity. I was incorrect in saying synchronous
executions start a new thread because thread-A continues to handle almost
every command in the main process flow.
The first block comes in the createOrder method. When
"addSuggestionsToShoppingList" is triggered, Thread-A inserts a row into
JOB_SANDBOX. When Thread-B continues its "side jobs" and tries to run an
update on JOB_SANDBOX, but it cannot because Thread-A has an open
transaction. This block doesn't freeze the application, so if the next
event didn't happen, everything would be fine.
The second block (and deadlock) happens when I try to call
runAsync("sendEmailConfirmation"). The main process flow begins to enter in
new RuntimeData and JobSandbox entities with storeAll(). The
GenericDelegator goes to locate the primary key for the RuntimeData with a
select statement. For some reason a third thread, Thread-C, is used to do
this select statement--maybe because Thread-B is blocked. When Thread-C
executes the select on RUNTIME_DATA, the deadlock occurs because Thread-B
previously inserted on RUNTIME_DATA, but can't finish.
To summarize the confusion, Thread-A is waiting on Thread-C, who is waiting
on Thread-B, who is waiting on Thread-A. According to the code, everything
is using the same transaction. The only time I see the main transaction is
suspended is to run the payment services, but this code executes fine. I can
see how my code facilitates the problem by executing all the methods and
services to create a full order, but I this it is the pool/thread logic that
creates the deadlock.
---------------
Does this happen to spark anyone's memory about a problem they've had with
OFBiz 4.0? Even though ReadUncommitted may work, I want information to
decide if I will need to backport the trunk pool logic.
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23263811.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by BJ Freeman <bj...@free-man.net>.
couple of links you might be interested in
http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://support.microsoft.com/kb/237686
mayo sent the following on 4/27/2009 12:56 PM:
> I found out more about how the deadlock is happening, but first let me say
> that setting the isolation level to read-uncommitted worked. Does anyone
> have any experience using read-uncommitted with OFBiz 4.0 on MSSQL? I
> cannot figure out the logic behind the process threads in the database, but
> I've meticulously stepped through the code to find out when they are
> created. Can anyone tell me if this is characteristic of the 4.0 thread
> logic and is different/fixed in the trunk logic?
>
> ---------------
>
> The first thread, lets say thread-A, is used to start the creation of the
> order. It looks like a second thread, thread-B, is created to handle "side
> jobs" like updating and selecting from the JOB_SANDBOX and to get the
> SEQUENCE_VALUE for an entity. I was incorrect in saying synchronous
> executions start a new thread because thread-A continues to handle almost
> every command in the main process flow.
>
> The first block comes in the createOrder method. When
> "addSuggestionsToShoppingList" is triggered, Thread-A inserts a row into
> JOB_SANDBOX. When Thread-B continues its "side jobs" and tries to run an
> update on JOB_SANDBOX, but it cannot because Thread-A has an open
> transaction. This block doesn't freeze the application, so if the next
> event didn't happen, everything would be fine.
>
> The second block (and deadlock) happens when I try to call
> runAsync("sendEmailConfirmation"). The main process flow begins to enter in
> new RuntimeData and JobSandbox entities with storeAll(). The
> GenericDelegator goes to locate the primary key for the RuntimeData with a
> select statement. For some reason a third thread, Thread-C, is used to do
> this select statement--maybe because Thread-B is blocked. When Thread-C
> executes the select on RUNTIME_DATA, the deadlock occurs because Thread-B
> previously inserted on RUNTIME_DATA, but can't finish.
>
> To summarize the confusion, Thread-A is waiting on Thread-C, who is waiting
> on Thread-B, who is waiting on Thread-A. According to the code, everything
> is using the same transaction. The only time I see the main transaction is
> suspended is to run the payment services, but this code executes fine. I can
> see how my code facilitates the problem by executing all the methods and
> services to create a full order, but I this it is the pool/thread logic that
> creates the deadlock.
>
> ---------------
>
> Does this happen to spark anyone's memory about a problem they've had with
> OFBiz 4.0? Even though ReadUncommitted may work, I want information to
> decide if I will need to backport the trunk pool logic.
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.
Re: MS SQL and OFBiz, database locks on transactions
Posted by BJ Freeman <bj...@free-man.net>.
using the one set in the entityengine.xml for isolation level.
mayo sent the following on 4/25/2009 6:17 PM:
>
> BJ Freeman wrote:
>> I have a similar routine that imports realtime orders into ofbiz.
>> it has no problems, though I am using postgresql.
>> it processes up to 15 persec.
>>
>
> It would be interesting for me to see if it works on postgresql. It has
> worked on MySQL with no problems for 4-5 months. What do you have your
> isolation level set to on postgresql?
>
>
> BJ Freeman wrote:
>> Are you synchronous all the way through the process before starting a
>> new process? This means that no other process, you are doing is starting
>> before the last one finishes.
>>
>
> Yes my process is completely synchronous except for when I call
> sendConfirmation. Other than that, I use existing OFBiz services (called
> synchronously) and existing OFBiz methods (that sometimes call their own
> services synchronously).
>
>
> BJ Freeman wrote:
>>> If what I say is true, then the solution isn't so simple--I would need to
>>> modify my code to not call any services synchronously. I would need to
>>> copy
>>> all the code in the needed service into my main function that creates the
>>> order.
>>>
>> do you mean Asynchronous?
>>
>
> No, I actually mean synchronously because from my current findings it looks
> as though my synchronous calls are, for some reason, spawning separate
> database threads. I am assuming these new threads have their own
> transactions, thus creating the deadlock. One way to solve this is to ensure
> my process does not use runSync. This is still an assumption though,
> because I need to verify that the runSync is spawning its own thread with
> its own transaction.
>
>
>
> BJ Freeman wrote:
>> as thought how about going into the database and create some triggers to
>> help the deadlocks, as a last resort.
>>
>
> This is a very good suggestion. I will be doing more research on the exact
> problem and will probably try a different isolation level, ReadUncommited.
> If that doesn't work and my research doesn't lead me elsewhere, I will
> attempt to work some triggers in.
>
> Thanks BJ.
>
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.
Re: MS SQL and OFBiz, database locks on transactions
Posted by BJ Freeman <bj...@free-man.net>.
see inline
mayo sent the following on 4/25/2009 11:49 AM:
> I have my services use-transaction set to false and manually create a
> transaction before importing an order. In fact, I copied all my code from
> existing OFBiz processes. The difference (and problem) is I am creating the
> whole order at once (customer, address, payment methods). Each step is
> hooked into existing OFBiz methods and services.
>
I have a similar routine that imports realtime orders into ofbiz.
it has no problems, though I am using postgresql.
it processes up to 15 persec.
> This specific deadlock is happening when I call OFBiz services through
> runSync. One process is kicked off when I call
> dispatcher.runSync("createPerson") and another is kicked off in the OFBiz
> method where a payment response is created (maybe checkout.processPayment).
> If executions are their own processes, then it's not using my main
> transaction and they are creating their own transactions, right? Should
> runSync be shooting off a new process? Is this something that is working
> differently in the trunk version?
>
Are you synchronous all the way through the process before starting a
new process? This means that no other process, you are doing is starting
before the last one finishes.
> I think it's deadlocking because the "side process" with its own transaction
> is trying to access something the main process has accessed (a common table
> like runtime_data or sequence_value_item) or visa versa. Since it's locking
> up with the main process is calling runAsync, I would say the latter.
The flow is important.
>
> If what I say is true, then the solution isn't so simple--I would need to
> modify my code to not call any services synchronously. I would need to copy
> all the code in the needed service into my main function that creates the
> order.
>
do you mean Asynchronous?
> I will continue to try to create a small example that exposes the error so
> that my custom code will not be in question.
as thought how about going into the database and create some triggers to
help the deadlocks, as a last resort.
>
>
>
>
> David E Jones-3 wrote:
>>
>> It's fairly simple, the code you have written (including configuration
>> settings for service defs, etc) has caused a deadlock. To fix it,
>> change your code.
>>
>> As for MySQL versus MSSQL... well... it is an oft-lamented fact that
>> MySQL is not as strict as other databases about pesky things like
>> "transactions" and "foreign keys". Of course, in this case it may
>> simply be different transaction isolation levels you are using for the
>> different databases. It is certainly the case the a deadlock in one tx
>> isolation level will have no problem in a less strict one.
>>
>> -David
>>
>>
>
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.
Re: MS SQL and OFBiz, database locks on transactions
Posted by David E Jones <da...@hotwaxmedia.com>.
It's fairly simple, the code you have written (including configuration
settings for service defs, etc) has caused a deadlock. To fix it,
change your code.
As for MySQL versus MSSQL... well... it is an oft-lamented fact that
MySQL is not as strict as other databases about pesky things like
"transactions" and "foreign keys". Of course, in this case it may
simply be different transaction isolation levels you are using for the
different databases. It is certainly the case the a deadlock in one tx
isolation level will have no problem in a less strict one.
-David
On Apr 25, 2009, at 7:19 AM, mayo wrote:
>
> The reason I think it's deadlock is because when I kill the
> "blocker" process
> in the database, OFBiz becomes unfrozen. I will run a profiler to
> make sure
> that both processes are hitting the RUNTIME_DATA table, but other
> than that
> how would I verify even more that it is the problem? What do you
> think the
> problem is? Since it does not happen in MySQL, does this mean a
> setting in
> MSSQL or the driver would make it work and does it still mean the
> pool is
> the problem?
>
>
> David E Jones-3 wrote:
>>
>>
>>>> I would HIGHLY recommend verifying that is the problem before
>>>> trying
>>>> to fix it (mainly because I don't think that is the problem).
>>
>>
>
> --
> View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23232044.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
Also, I am very surprised no one else has had this error. This tells me that
it is some other setting on the server or the driver, but what I've found
are definitely facts:
- OFBiz freezes in wait when it tries to execute RUNTIME_DATA through the
path of executing dispatcher.runAsync
- the database shows 2 blocked processes, the last of which was created by
runAsync
- the problem happens with 2 separate MSSQL jdbc drivers
- the blocker process last executes "sp_cursorclose"
- kill the blocker process and OFBiz unfreezes
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23232107.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
Here is the OFBiz log at the point of failure. It freezes at "Got bank of
sequenced IDs for [JobSandbox]". This is actually the first time I've seen
it freeze here. Usually it freezes 1 line above at the "ASync service
started... sendOrderConfirmation". The rest is the error that is thrown
when I manually kill the blocking process.
OFBiz log wrote:
>
> 2009-04-25 11:14:37,209 (http-0.0.0.0-8443-Processor4) [
> ServiceEcaRule.java:137:INFO ] Running Service ECA Service:
> checkDigitalItemFulfillment, triggered by rule on Service:
> changeOrderItemStatus
> 2009-04-25 11:14:37,225 (http-0.0.0.0-8443-Processor4) [
> ServiceDispatcher.java:467:DEBUG] Sync service
> [webtools/changeOrderItemStatus] finished in [94] milliseconds
> 2009-04-25 11:14:37,225 (http-0.0.0.0-8443-Processor4) [
> ServiceDispatcher.java:488:DEBUG] [[ASync service started...-
> total:0.0,since last(Begin):0.0]] - 'webtools / sendOrderConfirmation'
> 2009-04-25 11:14:37,240 (http-0.0.0.0-8443-Processor4) [
> SequenceUtil.java:254:INFO ] Got bank of sequenced IDs for [JobSandbox];
> curSeqId=10150, maxSeqId=10160, bankSize=10
> 2009-04-25 11:15:00,256
> (ContainerBackgroundProcessor[StandardEngine[default-server]])
> [ControlEventListener.java:97 :INFO ] Destroying session:
> 2C3F46EDC00F6F516878249E75C24293.jvm1
> 2009-04-25 11:15:00,287
> (ContainerBackgroundProcessor[StandardEngine[default-server]])
> [ControlEventListener.java:97 :INFO ] Destroying session:
> B31A341622F933C52554BA197D72BF69.jvm1
> 2009-04-25 11:15:00,287
> (ContainerBackgroundProcessor[StandardEngine[default-server]])
> [ControlEventListener.java:97 :INFO ] Destroying session:
> E7E6FFA61774C38A6000E74704AE29D9.jvm1
> 2009-04-25 11:15:00,319
> (ContainerBackgroundProcessor[StandardEngine[default-server]])
> [ControlEventListener.java:97 :INFO ] Destroying session:
> 0A40D774996DBBC5C55E393409629017.jvm1
> 2009-04-25 11:16:17,554 (org.ofbiz.service.job.JobPoller@6fa9fc)
> [InheritableTransactionContext.java:311:ERROR] Unable to roll back
> transaction
> java.lang.IllegalStateException: Status is STATUS_NO_TRANSACTION
> at
> org.apache.geronimo.transaction.manager.TransactionImpl.rollback(TransactionImpl.java:438)
> at
> org.apache.geronimo.transaction.context.InheritableTransactionContext.rollbackAndThrow(InheritableTransactionContext.java:308)
> at
> org.apache.geronimo.transaction.context.InheritableTransactionContext.complete(InheritableTransactionContext.java:199)
> at
> org.apache.geronimo.transaction.context.InheritableTransactionContext.commit(InheritableTransactionContext.java:146)
> at
> org.apache.geronimo.transaction.context.GeronimoTransactionManager.commit(GeronimoTransactionManager.java:81)
> at
> org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:179)
> at
> org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:165)
> at org.ofbiz.service.job.JobManager.poll(JobManager.java:184)
> at org.ofbiz.service.job.JobPoller.run(JobPoller.java:85)
> at java.lang.Thread.run(Thread.java:595)
> 2009-04-25 11:16:17,570 (org.ofbiz.service.job.JobPoller@6fa9fc) [
> TransactionUtil.java:647:WARN ]
> ---- exception report
> ----------------------------------------------------------
> WARNING: In getSetRollbackOnlyCause no stack placeholder was in place,
> here is the current location:
> Exception: java.lang.Exception
> Message: Current Stack Trace
> ---- stack trace
> ---------------------------------------------------------------
> java.lang.Exception: Current Stack Trace
> org.ofbiz.entity.transaction.TransactionUtil.getSetRollbackOnlyCause(TransactionUtil.java:646)
> org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:192)
> org.ofbiz.entity.transaction.TransactionUtil.commit(TransactionUtil.java:165)
> org.ofbiz.service.job.JobManager.poll(JobManager.java:184)
> org.ofbiz.service.job.JobPoller.run(JobPoller.java:85)
> java.lang.Thread.run(Thread.java:595)
> --------------------------------------------------------------------------------
>
The database shows 3 processes, 2 blocked (SPID 56, 57) and 1 awaiting a
command (SPID 52). Here are the last things that each process tries to
execute.
SQL Profiler, last commands for each process wrote:
>
> ------------------------------------------ SPID 52
> -------------------------------
> set @p1=1073742071
> set @p2=180150423
> exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 nvarchar(4000),@P1
> nvarchar(4000)',N'SELECT FROM dbo.WORK_EFFORT WHERE (CURRENT_STATUS_ID =
> @P0 AND SOURCE_REFERENCE_ID = @P1) ',4112,8193,@p7
> output,N'WF_SUSPENDED',N'800001'
>
> exec sp_cursorfetch 180150423,2,0,128
> sp_cursorclose 180150423
> exec sp_cursorunprepare 1073742071
>
> ------------------------------------------ SPID 56
> -------------------------------
>
> UPDATE dbo.SEQUENCE_VALUE_ITEM SET SEQ_ID=SEQ_ID+10 WHERE
> SEQ_NAME='JobSandbox'
>
> set @p1=180150037
> exec sp_cursoropen @p1 output,N'SELECT SEQ_ID FROM dbo.SEQUENCE_VALUE_ITEM
> WHERE SEQ_NAME=''JobSandbox''',16,8193,@p5 output
>
> exec sp_cursorfetch 180150037,2,0,128
> sp_cursorclose 180150037
> IF @@TRANCOUNT > 0 COMMIT TRAN
> set transaction isolation level read committed
>
>
> ------------------------------------------ SPID 57
> -------------------------------
> UPDATE dbo.SEQUENCE_VALUE_ITEM SET SEQ_ID=SEQ_ID+10 WHERE
> SEQ_NAME='RuntimeData'
>
> set @p1=180150063
> exec sp_cursoropen @p1 output,N'SELECT SEQ_ID FROM dbo.SEQUENCE_VALUE_ITEM
> WHERE SEQ_NAME=''RuntimeData''',16,8193,@p5 output
>
> exec sp_cursorfetch 180150063,2,0,128
> sp_cursorclose 180150063
> IF @@TRANCOUNT > 0 COMMIT TRAN
>
It looks like both blocked processes are updating and selecting on
SEQUENCE_VALUE_ITEM. I found very early in the profiler that the "blocker"
process (52) does an update on the same table.
SQL Profiler, process 52 wrote:
>
> ------------------------------------------ SPID 52
> -------------------------------
> UPDATE dbo.SEQUENCE_VALUE_ITEM SET SEQ_ID=SEQ_ID+10 WHERE
> SEQ_NAME='ImportedXml'
> set @p1=180150119
> exec sp_cursoropen @p1 output,N'SELECT SEQ_ID FROM dbo.SEQUENCE_VALUE_ITEM
> WHERE SEQ_NAME=''ImportedXml''',16,8193,@p5 output
>
Here is what the 2 blocked processes execute immediately after I kill
process 52
SQL Profiler, process execute after kill wrote:
>
> ------------------------------------------ SPID 57 AFTER KILL
> -------------------------------
> exec sp_prepexec @p1 output,N'@P0 nvarchar(4000),@P1
> nvarchar(4000)',N'UPDATE dbo.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = @P0,
> STATUS_ID = @P1 WHERE ((RUN_TIME <= ''2009-04-25 11:14:36.928'' AND
> START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND
> RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = ''pool''))
> ',N'ofbiz1',N'SERVICE_QUEUED'
>
> set @p1=1073741848
> set @p2=180150065
> exec sp_cursorprepexec @p1 output,@p2 output,N'@P0 nvarchar(4000),@P1
> nvarchar(4000)',N'SELECT JOB_ID, JOB_NAME, RUN_TIME, POOL_ID, STATUS_ID,
> PARENT_JOB_ID, PREVIOUS_JOB_ID, SERVICE_NAME, LOADER_NAME,
> MAX_RETRY, AUTH_USER_LOGIN_ID, RUN_AS_USER, RUNTIME_DATA_ID,
> RECURRENCE_INFO_ID, RUN_BY_INSTANCE_ID, START_DATE_TIME, FINISH_DATE_TIME,
> CANCEL_DATE_TIME, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,
> CREATED_STAMP, CREATED_TX_STAMP FROM dbo.JOB_SANDBOX WHERE
> (RUN_BY_INSTANCE_ID = @P0 AND STATUS_ID = @P1) ORDER BY RUN_TIME ASC
> ',4112,8193,@p7 output,N'ofbiz1',N'SERVICE_QUEUED'
>
> ------------------------------------------ SPID 56 AFTER KILL
> -------------------------------
> set @p1=1073741841
> set @p2=180150039
> exec sp_cursorprepexec @p1 output,@p2 output,N'@P0
> nvarchar(4000)',N'SELECT RUNTIME_INFO, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP FROM
> dbo.RUNTIME_DATA WHERE RUNTIME_DATA_ID=@P0 ',4112,8193,@p7
> output,N'10131'
>
> exec sp_cursorfetch 180150039,2,0,128
>
I will try to create a small focused service that will duplicate the error.
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23233666.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by BJ Freeman <bj...@free-man.net>.
I use to use MS-SQL but stopped at ver 7.
I still run them. I use the jtds drivers.
I now use postgressql exclusively, so I am not much help as to quirks of
2005.
on of the ways I tested if it was the SQL server is I made the SP
permanent under a different name then wrote code to call them, from the
SQL server it self. This let me know if the server had a problem with
the procedure.
I have a feeling that the problem is MS-SQL and/or the jbc driver.
mayo sent the following on 4/25/2009 6:28 AM:
> Also, I am very surprised no one else has had this error. This tells me that
> it is some other setting on the server or the driver, but what I've found
> are definitely facts:
> - OFBiz freezes in wait when it tries to execute RUNTIME_DATA through the
> path of executing dispatcher.runAsync
> - the database shows 2 blocked processes, the last of which was created by
> runAsync
> - the problem happens with 2 separate MSSQL jdbc drivers
> - the blocker process last executes "sp_cursorclose"
> - kill the blocker process and OFBiz unfreezes
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro
Systems Integrator.
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
The reason I think it's deadlock is because when I kill the "blocker" process
in the database, OFBiz becomes unfrozen. I will run a profiler to make sure
that both processes are hitting the RUNTIME_DATA table, but other than that
how would I verify even more that it is the problem? What do you think the
problem is? Since it does not happen in MySQL, does this mean a setting in
MSSQL or the driver would make it work and does it still mean the pool is
the problem?
David E Jones-3 wrote:
>
>
>>> I would HIGHLY recommend verifying that is the problem before trying
>>> to fix it (mainly because I don't think that is the problem).
>
>
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23232044.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by Jacques Le Roux <ja...@les7arts.com>.
BTW the changes I backported was on a Neogia instance based on release 4.0 (or near) and was on a CVS repository...
Jacques
From: "Jacques Le Roux" <ja...@les7arts.com>
>I backported DPCP changes for a client and could create a FAQ wiht a patch for that (I guess I'm not the only one who did that ;o)
> But as suggested David it would be better to be sure before that it's the cause of your problem.
>
> Jacques
>
> From: "David E Jones" <da...@hotwaxmedia.com>
>>
>> On Apr 24, 2009, at 6:02 PM, mayo wrote:
>>
>>>
>>> Deadlock is exactly the problem. I started with the 4.0 branch back in
>>> November because I thought it would more tested and would have less
>>> errors
>>> from all the new updates on trunk. Since then, I have made many
>>> customizations and have a nearing deadline that will make it hard
>>> for me to
>>> start from the trunk. I would love to simply use MySQL, but other
>>> factors
>>> are requiring I use MSSQL.
>>>
>>> So I guess I have to integrate the trunk pool logic......
>>
>> I would HIGHLY recommend verifying that is the problem before trying
>> to fix it (mainly because I don't think that is the problem).
>>
>>> Jacques (or anyone for that matter), if you could get me pointed in
>>> the
>>> right direction I am pretty comfortable with OFBiz that I think I
>>> can get it
>>> integrated. Maybe I can help out the community by documenting my
>>> changes
>>> and making a patch for the current 4.0 release.
>>
>> If you find the issue and a fix for it a patch for the release4.0
>> branch would be great. This is related to your thoughts above about it
>> having less errors... keep in mind that the one that has the most
>> users is the one that will typically have the most errors found and
>> fixed, and somewhat ironically that often means the trunk.
>>
>> -David
>>
>>
>>> David E Jones-3 wrote:
>>>>
>>>> This particular problems sounds like a good old deadlock with a
>>>> thread
>>>> A waiting on thread B and thread B is trying to get read something
>>>> thread A has a lock on. With custom code it's impossible to tell
>>>> without reviewing the code...
>>>>
>>>> On a side note, there have also been some improvements to service
>>>> engine job concurrency support in the trunk that may address these.
>>>> Is
>>>> there a reason you are using the old release4.0 branch?
>>>>
>>>> -David
>>>>
>>>>
>>>
>>> --
>>> View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23226753.html
>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>>
>>
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by Jacques Le Roux <ja...@les7arts.com>.
I backported DPCP changes for a client and could create a FAQ wiht a patch for that (I guess I'm not the only one who did that ;o)
But as suggested David it would be better to be sure before that it's the cause of your problem.
Jacques
From: "David E Jones" <da...@hotwaxmedia.com>
>
> On Apr 24, 2009, at 6:02 PM, mayo wrote:
>
>>
>> Deadlock is exactly the problem. I started with the 4.0 branch back in
>> November because I thought it would more tested and would have less
>> errors
>> from all the new updates on trunk. Since then, I have made many
>> customizations and have a nearing deadline that will make it hard
>> for me to
>> start from the trunk. I would love to simply use MySQL, but other
>> factors
>> are requiring I use MSSQL.
>>
>> So I guess I have to integrate the trunk pool logic......
>
> I would HIGHLY recommend verifying that is the problem before trying
> to fix it (mainly because I don't think that is the problem).
>
>> Jacques (or anyone for that matter), if you could get me pointed in
>> the
>> right direction I am pretty comfortable with OFBiz that I think I
>> can get it
>> integrated. Maybe I can help out the community by documenting my
>> changes
>> and making a patch for the current 4.0 release.
>
> If you find the issue and a fix for it a patch for the release4.0
> branch would be great. This is related to your thoughts above about it
> having less errors... keep in mind that the one that has the most
> users is the one that will typically have the most errors found and
> fixed, and somewhat ironically that often means the trunk.
>
> -David
>
>
>> David E Jones-3 wrote:
>>>
>>> This particular problems sounds like a good old deadlock with a
>>> thread
>>> A waiting on thread B and thread B is trying to get read something
>>> thread A has a lock on. With custom code it's impossible to tell
>>> without reviewing the code...
>>>
>>> On a side note, there have also been some improvements to service
>>> engine job concurrency support in the trunk that may address these.
>>> Is
>>> there a reason you are using the old release4.0 branch?
>>>
>>> -David
>>>
>>>
>>
>> --
>> View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23226753.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by David E Jones <da...@hotwaxmedia.com>.
On Apr 24, 2009, at 6:02 PM, mayo wrote:
>
> Deadlock is exactly the problem. I started with the 4.0 branch back in
> November because I thought it would more tested and would have less
> errors
> from all the new updates on trunk. Since then, I have made many
> customizations and have a nearing deadline that will make it hard
> for me to
> start from the trunk. I would love to simply use MySQL, but other
> factors
> are requiring I use MSSQL.
>
> So I guess I have to integrate the trunk pool logic......
I would HIGHLY recommend verifying that is the problem before trying
to fix it (mainly because I don't think that is the problem).
> Jacques (or anyone for that matter), if you could get me pointed in
> the
> right direction I am pretty comfortable with OFBiz that I think I
> can get it
> integrated. Maybe I can help out the community by documenting my
> changes
> and making a patch for the current 4.0 release.
If you find the issue and a fix for it a patch for the release4.0
branch would be great. This is related to your thoughts above about it
having less errors... keep in mind that the one that has the most
users is the one that will typically have the most errors found and
fixed, and somewhat ironically that often means the trunk.
-David
> David E Jones-3 wrote:
>>
>> This particular problems sounds like a good old deadlock with a
>> thread
>> A waiting on thread B and thread B is trying to get read something
>> thread A has a lock on. With custom code it's impossible to tell
>> without reviewing the code...
>>
>> On a side note, there have also been some improvements to service
>> engine job concurrency support in the trunk that may address these.
>> Is
>> there a reason you are using the old release4.0 branch?
>>
>> -David
>>
>>
>
> --
> View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23226753.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by mayo <al...@hotmail.com>.
Deadlock is exactly the problem. I started with the 4.0 branch back in
November because I thought it would more tested and would have less errors
from all the new updates on trunk. Since then, I have made many
customizations and have a nearing deadline that will make it hard for me to
start from the trunk. I would love to simply use MySQL, but other factors
are requiring I use MSSQL.
So I guess I have to integrate the trunk pool logic......
Jacques (or anyone for that matter), if you could get me pointed in the
right direction I am pretty comfortable with OFBiz that I think I can get it
integrated. Maybe I can help out the community by documenting my changes
and making a patch for the current 4.0 release.
David E Jones-3 wrote:
>
> This particular problems sounds like a good old deadlock with a thread
> A waiting on thread B and thread B is trying to get read something
> thread A has a lock on. With custom code it's impossible to tell
> without reviewing the code...
>
> On a side note, there have also been some improvements to service
> engine job concurrency support in the trunk that may address these. Is
> there a reason you are using the old release4.0 branch?
>
> -David
>
>
--
View this message in context: http://www.nabble.com/MS-SQL-and-OFBiz%2C-database-locks-on-transactions-tp23225473p23226753.html
Sent from the OFBiz - User mailing list archive at Nabble.com.
Re: MS SQL and OFBiz, database locks on transactions
Posted by David E Jones <da...@hotwaxmedia.com>.
This may or may not be related to the connection pool. That is
certainly issue that has been improved in the trunk, but the behavior
(if I remember right) was not something you would typically see in
testing, only stale transactions and such on servers that have been
live for a while.
This particular problems sounds like a good old deadlock with a thread
A waiting on thread B and thread B is trying to get read something
thread A has a lock on. With custom code it's impossible to tell
without reviewing the code...
On a side note, there have also been some improvements to service
engine job concurrency support in the trunk that may address these. Is
there a reason you are using the old release4.0 branch?
-David
On Apr 24, 2009, at 5:11 PM, BJ Freeman wrote:
> need to upgrade the pool to what the trunck uses.
> not an easy task.
> can't find the fAQ on it
> hopefully Jacques will jump in since he supplied the info last time.
>
> mayo sent the following on 4/24/2009 4:04 PM:
>> I noticed that we were missing steps on enabling XA transactions
>> for MS SQL.
>> I followed the official install steps
>> (http://msdn.microsoft.com/en-us/library/aa342335(SQL.90).aspx) and
>> still
>> having the same error. I am now using the MSSQL JDBC 1.1 driver.
>>
>> Should the profiler show the use of the XA procedures? Do I need
>> to set the
>> pool-xa-wrapper-class in <inline-jdbc>?
>>
>
> --
> BJ Freeman
> http://www.businessesnetwork.com/automation
> http://bjfreeman.elance.com
> Systems Integrator.
>
Re: MS SQL and OFBiz, database locks on transactions
Posted by BJ Freeman <bj...@free-man.net>.
need to upgrade the pool to what the trunck uses.
not an easy task.
can't find the fAQ on it
hopefully Jacques will jump in since he supplied the info last time.
mayo sent the following on 4/24/2009 4:04 PM:
> I noticed that we were missing steps on enabling XA transactions for MS SQL.
> I followed the official install steps
> (http://msdn.microsoft.com/en-us/library/aa342335(SQL.90).aspx) and still
> having the same error. I am now using the MSSQL JDBC 1.1 driver.
>
> Should the profiler show the use of the XA procedures? Do I need to set the
> pool-xa-wrapper-class in <inline-jdbc>?
>
--
BJ Freeman
http://www.businessesnetwork.com/automation
http://bjfreeman.elance.com
Systems Integrator.