ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jacques Le Roux <jacques.le.r...@les7arts.com>
Subject Re: MySQL Deadlocks in OfBiz 12.04
Date Fri, 17 Feb 2017 12:51:33 GMT
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("lastUpdatedStamp",
> 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 <robb@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.gray@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" <robb@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.entity.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>
>>>


Mime
View raw message