+1. Feel free to investigate and create when applicable.
Best regards,
Pierre
On Friday, February 17, 2017, Jacques Le Roux <jacques.le.r...@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 <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.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/
