Mailing-List: contact user-help@ofbiz.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@ofbiz.apache.org
Subject: Re: MySQL Deadlocks in OfBiz 12.04
To: user@ofbiz.apache.org
References: 
	<CAOWifkFu0EeDvgiKB2cy-t6NM74Rw9tebjBQpY=6Ow9+BcKRGA@mail.gmail.com>
From: Jacques Le Roux <jacques.le.roux@les7arts.com>
Organization: Les Arts Informatiques
Message-ID: <3dc13d3d-05b1-a021-a436-8c84b82922cb@les7arts.com>
Date: Wed, 15 Feb 2017 23:27:02 +0100
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:45.0) Gecko/20100101
	Thunderbird/45.7.1
MIME-Version: 1.0
In-Reply-To: 
	<CAOWifkFu0EeDvgiKB2cy-t6NM74Rw9tebjBQpY=6Ow9+BcKRGA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
archived-at: Wed, 15 Feb 2017 22:28:12 -0000

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