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:52:14 GMT
I mean if it still exists in trunk and R16.11


Le 17/02/2017 à 13:51, Jacques Le Roux a écrit :
> 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