Mailing-List: contact user-help@ofbiz.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ofbiz.apache.org
MIME-Version: 1.0
In-Reply-To: <8a82eea1-06e2-ae47-1fa0-12ce91546e8f@les7arts.com>
References: <CAOWifkFu0EeDvgiKB2cy-t6NM74Rw9tebjBQpY=6Ow9+BcKRGA@mail.gmail.com>
 <CABJb0-s__904-v8Ctvo=7a8KPiN_58_0sptWoBi0KuGwkG4TwQ@mail.gmail.com>
 <CAOWifkHkD8UOuaX6ZaXmrVG3rtSMz7-3Ryeabd75MpDOq7G2_Q@mail.gmail.com>
 <CABJb0-u6nh1QwC1c_jKpqvtorv9trcoAL7eaApmZyuaoQmVgVw@mail.gmail.com> <8a82eea1-06e2-ae47-1fa0-12ce91546e8f@les7arts.com>
From: Scott Gray <scott.gray@hotwaxsystems.com>
Date: Sat, 18 Feb 2017 20:06:21 +1300
Message-ID: <CABJb0-sySswFSnfk8OcryWEgJpb8h_C-c70+4PepSRxzpfsE3A@mail.gmail.com>
Subject: Re: MySQL Deadlocks in OfBiz 12.04
To: user@ofbiz.apache.org
Content-Type: multipart/alternative; boundary=001a113e4b92eb26f70548c8aac4
archived-at: Sat, 18 Feb 2017 07:06:34 -0000

--001a113e4b92eb26f70548c8aac4
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 =C3=A0 04:04, Scott Gray a =C3=A9crit :
>
>> 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 onc=
e
>> 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.co=
m
>>> >
>>> wrote:
>>>
>>> Hi Robb,
>>>
>>> I've encountered similar deadlocks in the past and have simply issued a=
n
>>> update statement on the main row before attempting to work with any chi=
ld
>>> 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 transaction=
s.
>>>>
>>>> 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=3D"localmysql"
>>>>              helper-class=3D"org.ofbiz.entit
>>>> y.datasource.GenericHelperDAO"
>>>>              field-type-name=3D"mysql"
>>>>              check-on-start=3D"true"
>>>>              add-missing-on-start=3D"true"
>>>>              check-pks-on-start=3D"false"
>>>>              check-indices-on-start=3D"false"
>>>>              use-foreign-keys=3D"true"
>>>>              join-style=3D"ansi-no-parenthesis"
>>>>              alias-view-columns=3D"false"
>>>>              drop-fk-use-foreign-key-keyword=3D"true"
>>>>              table-type=3D"InnoDB"
>>>>              character-set=3D"utf8"
>>>>              collate=3D"utf8_general_ci">
>>>>          <read-data reader-name=3D"tenant"/>
>>>>          <read-data reader-name=3D"seed"/>
>>>>          <read-data reader-name=3D"seed-initial"/>
>>>>          <read-data reader-name=3D"demo"/>
>>>>          <read-data reader-name=3D"ext"/>
>>>>          <inline-jdbc
>>>>                  jdbc-driver=3D"com.mysql.jdbc.Driver"
>>>>                  jdbc-uri=3D"jdbc:mysql://
>>>> ofbiz-db.example.com/ofbiz?autoReconnect=3Dtrue"
>>>>                  jdbc-username=3D"ofbiz"
>>>>                  jdbc-password=3D"XXXXpasswordXXXX"
>>>>                  isolation-level=3D"ReadCommitted"
>>>>                  pool-minsize=3D"2"
>>>>                  pool-maxsize=3D"250"
>>>>                  time-between-eviction-runs-millis=3D"600000"/><!--
>>>> Please
>>>> note that at least one person has experienced a problem with this valu=
e
>>>> 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=3D"localjndi"
>>>> jndi-name=3D"java:/MySqlDataSource" isolation-level=3D"Serializable"/>=
 -->
>>>>      </datasource>
>>>>
>>>>
>

--001a113e4b92eb26f70548c8aac4--