ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Scott Gray <scott.g...@hotwaxsystems.com>
Subject Re: MySQL Deadlocks in OfBiz 12.04
Date Sat, 18 Feb 2017 07:06:21 GMT
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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message