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.r...@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 <r...@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.g...@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" <r...@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>
>>>>
>>>>
>
