OFBiz › OFBiz - User

MySQL Deadlocks in OfBiz 12.04

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

17 messages Options

Robb Wagoner

MySQL Deadlocks in OfBiz 12.04

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"/>

</datasource>

Scott Gray-3

Re: MySQL Deadlocks in OfBiz 12.04

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" <[hidden email]> 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"/>
> 
> </datasource>
>

Robb Wagoner

Re: MySQL Deadlocks in OfBiz 12.04

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 <[hidden email]>
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" <[hidden email]> wrote:

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

In reply to this post by Robb Wagoner

Hi Robb,

Just to be sure, the MySQL version you use >= 5.6.4 ?

Jacques

Le 15/02/2017 à 20:03, Robb Wagoner a écrit :

> 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"/>
> 
> </datasource>
>

Robb Wagoner

Re: MySQL Deadlocks in OfBiz 12.04

It is AWS Aurora variant of MySQL v5.6.10.
$ mysql -hofbiz.example.com -e 'select version(), @@aurora_version'
+------------+------------------+
| version() | @@aurora_version |
+------------+------------------+
| 5.6.10-log | 1.10 |
+------------+------------------+

On Wed, Feb 15, 2017 at 3:28 PM Jacques Le Roux <
[hidden email]> wrote:

> Hi Robb,
>
> Just to be sure, the MySQL version you use >= 5.6.4 ?
>
> Jacques
>
>
> Le 15/02/2017 à 20:03, Robb Wagoner a écrit :
> > 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"/>
> > 
> > </datasource>
> >
>
>

Shi Jinghai-3

Re: MySQL Deadlocks in OfBiz 12.04

In reply to this post by Robb Wagoner

I'd suggest if possible, use MariaDB 5.5 or MySQL 5.7 and later instead. MySQL 5.6 is very bad performance, I tried to migrate our postgresql to MySQL 5.5/5.6 one month ago. Some of our sql scripts take less than 0.1s in postgresql and MariaDB, but consume more than 100s in MySQL 5.5/5.6 :).

-----邮件原件-----
发件人: Robb Wagoner [mailto:[hidden email]]
发送时间: 2017年2月16日 7:09
收件人: [hidden email]
主题: Re: MySQL Deadlocks in OfBiz 12.04

It is AWS Aurora variant of MySQL v5.6.10.
$ mysql -hofbiz.example.com -e 'select version(), @@aurora_version'
+------------+------------------+
| version() | @@aurora_version |
+------------+------------------+
| 5.6.10-log | 1.10 |
+------------+------------------+

On Wed, Feb 15, 2017 at 3:28 PM Jacques Le Roux < [hidden email]> wrote:

> Hi Robb,
>
> Just to be sure, the MySQL version you use >= 5.6.4 ?
>
> Jacques
>
>
> Le 15/02/2017 à 20:03, Robb Wagoner a écrit :
> > 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"/>
> > 
> > </datasource>
> >
>
>

Scott Gray-3

Re: MySQL Deadlocks in OfBiz 12.04

In reply to this post by Robb Wagoner

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("lastUpdatedStamp",
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 <[hidden email]> 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 <[hidden email]>
> 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" <[hidden email]> 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"/>
> > 
> > </datasource>
> >
>

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

In reply to this post by Robb Wagoner

Then you have not the infamous problem of ms https://issues.apache.org/jira/browse/OFBIZ-2208 and it's another issue indeed

Jacques

Le 16/02/2017 à 00:08, Robb Wagoner a écrit :

> It is AWS Aurora variant of MySQL v5.6.10.
> $ mysql -hofbiz.example.com -e 'select version(), @@aurora_version'
> +------------+------------------+
> | version() | @@aurora_version |
> +------------+------------------+
> | 5.6.10-log | 1.10 |
> +------------+------------------+
>
> On Wed, Feb 15, 2017 at 3:28 PM Jacques Le Roux <
> [hidden email]> wrote:
>
>> Hi Robb,
>>
>> Just to be sure, the MySQL version you use >= 5.6.4 ?
>>
>> Jacques
>>
>>
>> Le 15/02/2017 à 20:03, Robb Wagoner a écrit :
>>> 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"/>
>>> 
>>> </datasource>
>>>
>>
>

Robb Wagoner

Re: MySQL Deadlocks in OfBiz 12.04

In reply to this post by Scott Gray-3

Thanks Scott! Much appreciated. The pattern makes sense. Have a great
weekend.

On Wed, Feb 15, 2017 at 8:05 PM Scott Gray <[hidden email]>
wrote:

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("lastUpdatedStamp",
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 <[hidden email]> wrote:

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

In reply to this post by Scott Gray-3

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("lastUpdatedStamp",
> 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 <[hidden email]> 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 <[hidden email]>
>> 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" <[hidden email]> 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"/>
>>> 
>>> </datasource>
>>>

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

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("lastUpdatedStamp",
>> 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 <[hidden email]> 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 <[hidden email]>
>>> 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" <[hidden email]> 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"/>
>>>> 
>>>> </datasource>
>>>>
>

Pierre Smits

Re: MySQL Deadlocks in OfBiz 12.04

+1. Feel free to investigate and create when applicable.

Best regards,

Pierre

On Friday, February 17, 2017, Jacques Le Roux <[hidden email]>
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 <[hidden email]> 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 <
>>>> [hidden email]>
>>>> 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" <[hidden email]> 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"/>
>>>>> 
>>>>> </datasource>
>>>>>
>>>>>
>>
>

--
Pierre Smits

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

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

Scott Gray-3

Re: MySQL Deadlocks in OfBiz 12.04

In reply to this post by Jacques Le Roux

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" <[hidden email]> 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 <[hidden email]> 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 <[hidden email]
>>> >
>>> 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" <[hidden email]> 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"/>
>>>> 
>>>> </datasource>
>>>>
>>>>
>

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

Hi Scott,

I was referring to the bug which was initially reported in this thread. I understand that we should rather refactor FinAccount services and I have
opened a Jira for that

https://issues.apache.org/jira/browse/OFBIZ-9221

Jacques

Le 18/02/2017 à 08:06, Scott Gray a écrit :

> 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" <[hidden email]> 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 <[hidden email]> 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 <[hidden email]
>>>> 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" <[hidden email]> 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"/>
>>>>> 
>>>>> </datasource>
>>>>>
>>>>>
>

Scott Gray-3

Re: MySQL Deadlocks in OfBiz 12.04

There was no bug report in this thread, just some vague details. Deadlocks
are not simple to locate and fix, finding the competing transactions is
difficult even with access to the logs and database. I don't think there's
any point in filing a ticket unless Robb comes back with more details about
the root cause.

I wasn't referring to deadlocks as being the reason a refactoring is
needed. I would guess that most use cases don't require multiple
concurrent transactions within a single account, hence why I said it
probably isn't worth fixing. Regarding refactoring I was referring to
issues such as:
- The FinAccount data model should stand-alone from the rest of the data
model (i.e. no FKs), it should be treated in a similar fashion to an
external API
- FinAccounts should record a close of day balance instead of summing up
all transactions ever recorded against the account every time an
auth/transaction is recorded.
Things like that which are more problematic for general use cases than the
potential for deadlocks.

Regards
Scott

On 18 February 2017 at 23:08, Jacques Le Roux <[hidden email]>
wrote:

> Hi Scott,
>
> I was referring to the bug which was initially reported in this thread. I
> understand that we should rather refactor FinAccount services and I have
> opened a Jira for that
>
> https://issues.apache.org/jira/browse/OFBIZ-9221
>
> Jacques
>
>
>
> Le 18/02/2017 à 08:06, Scott Gray a écrit :
>
>> 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" <[hidden email]>
>> 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 <[hidden email]>
>>>> 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 <
>>>>> [hidden email]
>>>>> 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" <[hidden email]> 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"/>
>>>>>> 
>>>>>> </datasource>
>>>>>>
>>>>>>
>>>>>>
>>
>

Jacques Le Roux

Re: MySQL Deadlocks in OfBiz 12.04

Administrator

Thanks Scott,

As I referred to the thread it could help people to fix related issues if they stumble upon. Else if you think it's confusing, please feel free to
change the summary and description, maybe by adding your comment below?

Jacques

Le 18/02/2017 à 11:37, Scott Gray a écrit :

> There was no bug report in this thread, just some vague details. Deadlocks
> are not simple to locate and fix, finding the competing transactions is
> difficult even with access to the logs and database. I don't think there's
> any point in filing a ticket unless Robb comes back with more details about
> the root cause.
>
> I wasn't referring to deadlocks as being the reason a refactoring is
> needed. I would guess that most use cases don't require multiple
> concurrent transactions within a single account, hence why I said it
> probably isn't worth fixing. Regarding refactoring I was referring to
> issues such as:
> - The FinAccount data model should stand-alone from the rest of the data
> model (i.e. no FKs), it should be treated in a similar fashion to an
> external API
> - FinAccounts should record a close of day balance instead of summing up
> all transactions ever recorded against the account every time an
> auth/transaction is recorded.
> Things like that which are more problematic for general use cases than the
> potential for deadlocks.
>
> Regards
> Scott
>
>
> On 18 February 2017 at 23:08, Jacques Le Roux <[hidden email]>
> wrote:
>
>> Hi Scott,
>>
>> I was referring to the bug which was initially reported in this thread. I
>> understand that we should rather refactor FinAccount services and I have
>> opened a Jira for that
>>
>> https://issues.apache.org/jira/browse/OFBIZ-9221
>>
>> Jacques
>>
>>
>>
>> Le 18/02/2017 à 08:06, Scott Gray a écrit :
>>
>>> 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" <[hidden email]>
>>> 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 <[hidden email]>
>>>>> 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 <
>>>>>> [hidden email]
>>>>>> 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" <[hidden email]> 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"/>
>>>>>>> 
>>>>>>> </datasource>
>>>>>>>
>>>>>>>
>>>>>>>
>

Scott Gray-3

Re: MySQL Deadlocks in OfBiz 12.04

I don't know, I'm inclined to think these lists do a better job of sharing
information in a searchable manner than Jira does.

Regards
Scott

On 19/02/2017 00:36, "Jacques Le Roux" <[hidden email]> wrote:

Thanks Scott,

As I referred to the thread it could help people to fix related issues if
they stumble upon. Else if you think it's confusing, please feel free to
change the summary and description, maybe by adding your comment below?

Jacques

Le 18/02/2017 à 11:37, Scott Gray a écrit :

> There was no bug report in this thread, just some vague details. Deadlocks
> are not simple to locate and fix, finding the competing transactions is
> difficult even with access to the logs and database. I don't think there's
> any point in filing a ticket unless Robb comes back with more details about
> the root cause.
>
> I wasn't referring to deadlocks as being the reason a refactoring is
> needed. I would guess that most use cases don't require multiple
> concurrent transactions within a single account, hence why I said it
> probably isn't worth fixing. Regarding refactoring I was referring to
> issues such as:
> - The FinAccount data model should stand-alone from the rest of the data
> model (i.e. no FKs), it should be treated in a similar fashion to an
> external API
> - FinAccounts should record a close of day balance instead of summing up
> all transactions ever recorded against the account every time an
> auth/transaction is recorded.
> Things like that which are more problematic for general use cases than the
> potential for deadlocks.
>
> Regards
> Scott
>
>
> On 18 February 2017 at 23:08, Jacques Le Roux <
> [hidden email]>
> wrote:
>
> Hi Scott,
>>
>> I was referring to the bug which was initially reported in this thread. I
>> understand that we should rather refactor FinAccount services and I have
>> opened a Jira for that
>>
>> https://issues.apache.org/jira/browse/OFBIZ-9221
>>
>> Jacques
>>
>>
>>
>> Le 18/02/2017 à 08:06, Scott Gray a écrit :
>>
>> 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" <[hidden email]>
>>> 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 <[hidden email]>
>>>>> 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 <
>>>>>> [hidden email]
>>>>>> 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" <[hidden email]> 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"/>
>>>>>>> 
>>>>>>> </datasource>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>