ofbiz-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pierre Smits <pierre.sm...@gmail.com>
Subject Re: MySQL Deadlocks in OfBiz 12.04
Date Fri, 17 Feb 2017 13:46:28 GMT
+1. Feel free to investigate and create when applicable.

Best regards,

Pierre

On Friday, February 17, 2017, Jacques Le Roux <jacques.le.roux@les7arts.com>
wrote:

> 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("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.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>
>>>>>
>>>>>
>>
>

-- 
Pierre Smits

ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services

OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message