Hi Scott,

I was referring to the bug which was initially reported in this thread. I understand that we should rather refactor FinAccount services and I have opened a Jira for that

https://issues.apache.org/jira/browse/OFBIZ-9221

Jacques


Le 18/02/2017 à 08:06, Scott Gray a écrit :
Which bug are you referring to Jacques? FinAccount services certainly need
to be refactored but the use case that I mentioned was unusual so it
probably isn't worth fixing unless a more common scenario comes along. It
just happened to be a simple example I had on hand.

Regards
Scott



On 18/02/2017 01:52, "Jacques Le Roux" <jacques.le.r...@les7arts.com> wrote:

Hi Scott,

Should we not open a Jira and try to fix this bug?

Jacques


Le 16/02/2017 à 04:04, Scott Gray a écrit :

For example, a current project has some heavily used FinAccount rows and
the OFBiz implementation has a tendency to modify the child rows
(FinAccountAuth/FinAccountTrans) before modifying the parent FinAccount
row.  This very easily results in a deadlock as soon as one of the
competing transactions attempts to perform an update on the FinAccount
record.  This happens because the child row modifications result in a
shared lock on the parent row due to the foreign key constraint, but once
multiple transactions both have that shared lock, neither can update the
FinAccount without causing a deadlock exception.

So my quick fix solution is to acquire a lock on the FinAccount record
*before* any child rows are modified:
delegator.storeByCondition("FinAccount", UtilMisc.toMap("lastUpdatedSta
mp",
UtilDateTime.nowTimestamp()), EntityCondition.makeCondition(
"finAccountId",
finAccountId));

Assuming all code paths relating to modifying FinAccount data are
following
this strategy then each transaction will have to queue on this update
statement until their turn comes up.  Voila, deadlocks are gone.

Regards
Scott


On 16 February 2017 at 09:29, Robb Wagoner <r...@pandastrike.com> wrote:

Thanks Scott. Since I am new to OfBiz can you provide a code example? I.e.
by what means are you issuing an arbitrary no-op update statement on a
parent row?

On Wed, Feb 15, 2017 at 1:02 PM Scott Gray <scott.g...@hotwaxsystems.com
wrote:

Hi Robb,

I've encountered similar deadlocks in the past and have simply issued an
update statement on the main row before attempting to work with any child
rows. That has the same effect as the FOR UPDATE statement but without
needing to enhance the framework.

Regards
Scott

On 16/02/2017 08:03, "Robb Wagoner" <r...@pandastrike.com> wrote:

We are running OfBiz 12.04 and are using MySQL 5.6 as the RDBMS. We are
experiencing database deadlocks and would like to be able to add
MySQL-specific statements to deal with the deadlocks. For example:

`SELECT

... FOR UPDATE` on PRIMARY KEYs and/or FOREIGN KEYs in the transactions.

Being new to OfBiz, I've been reviewing the ofbiz-user and ofbiz-dev
archives, OfBiz Wiki, Packt's *Apache OfBiz Cookbook*, and other

resources

to learn how we can achieve this.
I see mentions of deadlocks in the archives and in JIRA issues (
https://issues.apache.org/jira/browse/OFBIZ-5065). Nothing conclusive
or
directly actionable on managing/dealing with deadlocks is mentioned.

What is the OfBiz-way of dealing with RDBMS deadlocks?


*Deadlocking statements:*
UPDATE SHIPMENT
UPDATE INVENTORY_ITEM
UPDATE GL_ACCOUNT_ORGANIZATION
INSERT INTO ACCTG_TRANS_ENTRY


*entityengine.xml datasource element:*
<datasource name="localmysql"
              helper-class="org.ofbiz.entit
y.datasource.GenericHelperDAO"
              field-type-name="mysql"
              check-on-start="true"
              add-missing-on-start="true"
              check-pks-on-start="false"
              check-indices-on-start="false"
              use-foreign-keys="true"
              join-style="ansi-no-parenthesis"
              alias-view-columns="false"
              drop-fk-use-foreign-key-keyword="true"
              table-type="InnoDB"
              character-set="utf8"
              collate="utf8_general_ci">
          <read-data reader-name="tenant"/>
          <read-data reader-name="seed"/>
          <read-data reader-name="seed-initial"/>
          <read-data reader-name="demo"/>
          <read-data reader-name="ext"/>
          <inline-jdbc
                  jdbc-driver="com.mysql.jdbc.Driver"
                  jdbc-uri="jdbc:mysql://
ofbiz-db.example.com/ofbiz?autoReconnect=true"
                  jdbc-username="ofbiz"
                  jdbc-password="XXXXpasswordXXXX"
                  isolation-level="ReadCommitted"
                  pool-minsize="2"
                  pool-maxsize="250"
                  time-between-eviction-runs-millis="600000"/><!--
Please
note that at least one person has experienced a problem with this value
with MySQL
                  and had to set it to -1 in order to avoid this issue.
                  For more look at http://markmail.org/thread/
5sivpykv7xkl66px
and http://commons.apache.org/dbcp/configuration.html-->
          <!-- <jndi-jdbc jndi-server-name="localjndi"
jndi-name="java:/MySqlDataSource" isolation-level="Serializable"/> -->
      </datasource>




Reply via email to