MS SQL and OFBiz, database locks on transactions

[mayo]

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);

[mayo]

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]

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.

[David E Jones-3]

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.
>

[mayo]

Deadlock is exactly the problem. I started with the 4.0 branch back in November because I thought it would be better 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

[David E Jones-3]

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.
>

[Jacques Le Roux]

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" <[hidden email]>

>
> 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.
>>
>

[Jacques Le Roux]

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" <[hidden email]>

>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" <[hidden email]>
>>
>> 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.
>>>
>>
>

[mayo]

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).

[mayo]

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

I will do some more troubleshooting and get back to you.

[BJ Freeman]

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.

[mayo]

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.

[David E Jones-3]

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.
>

[mayo]

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

[BJ Freeman]

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.

[mayo]

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]

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.

[mayo]

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 think 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.

[Scott Gray-2]

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.

[BJ Freeman]

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.