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 Sat, 18 Feb 2017 10:08:02 GMT
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.roux@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 <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.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>
>>>>>
>>>>>
>


Mime
View raw message