ofbiz 10.04 and lock wait time out exceptions

[Varun Bhansaly]

Hi,
I'm using ofbiz release10.04@1072382 with MySQL database server (with
default settings).

Of late I have been observing lots of 'lock wait time out' exceptions in
the logs primarily while the system is trying to update entity OrderHeader.

If anyone has any suggestions please do share.

Below is the portion of the exception trace -

2012-05-01 18:51:34,865 [ERROR] SQL Exception while executing the
following: UPDATE ORDER_HEADER SET ORDER_TYPE_ID=?, ORDER_NAME=?,
EXTERNAL_ID=?, SALES_CHANNEL_ENUM_ID=?, ORDER_DATE=?, PRIORITY=?,
ENTRY_DATE=?, PICK_SHEET_PRINTED_DATE=?, VISIT_ID=?, STATUS_ID=?,
CREATED_BY=?, FIRST_ATTEMPT_ORDER_ID=?, CURRENCY_UOM=?, SYNC_STATUS_ID=?,
BILLING_ACCOUNT_ID=?, ORIGIN_FACILITY_ID=?, WEB_SITE_ID=?,
PRODUCT_STORE_ID=?, TERMINAL_ID=?, TRANSACTION_ID=?,
AUTO_ORDER_SHOPPING_LIST_ID=?, NEEDS_INVENTORY_ISSUANCE=?, IS_RUSH_ORDER=?,
INTERNAL_CODE=?, REMAINING_SUB_TOTAL=?, GRAND_TOTAL=?, IS_VIEWED=?,
LAST_UPDATED_STAMP=?, LAST_UPDATED_TX_STAMP=?, CREATED_STAMP=?,
CREATED_TX_STAMP=? WHERE ORDER_ID=?
2012-05-01 18:51:34,867 [ERROR] Error while updating
[GenericEntity:OrderHeader][autoOrderShoppingListId,null()][billingAccountId,null()][createdBy,admin(java.lang.String)][createdStamp,2012-05-01
18:48:57.0(java.sql.Timestamp)][createdTxStamp,2012-05-01
18:48:57.0(java.sql.Timestamp)][currencyUom,USD(java.lang.String)][entryDate,2012-05-01
18:48:57.0(java.sql.Timestamp)][externalId,null()][firstAttemptOrderId,null()][grandTotal,72.00(java.math.BigDecimal)][internalCode,null()][isRushOrder,null()][isViewed,null()][lastUpdatedStamp,2012-05-01
18:50:43.297(java.sql.Timestamp)][lastUpdatedTxStamp,2012-05-01
18:50:42.078(java.sql.Timestamp)][needsInventoryIssuance,null()][orderDate,2012-05-01
18:48:57.0(java.sql.Timestamp)][orderId,SE11510(java.lang.String)][orderName,Test
Order(java.lang.String)][orderTypeId,SALES_ORDER(java.lang.String)][originFacilityId,null()][pickSheetPrintedDate,null()][priority,2(java.lang.String)][productStoreId,9000(java.lang.String)][remainingSubTotal,72.00(java.math.BigDecimal)][salesChannelEnumId,WEB_SALES_CHANNEL(java.lang.String)][statusId,ORDER_COMPLETED(java.lang.String)][syncStatusId,null()][terminalId,null()][transactionId,null()][visitId,5171(java.lang.String)][webSiteId,OrderEntry(java.lang.String)]
2012-05-01 18:51:34,869 [ERROR]
---- exception report
----------------------------------------------------------
Failure in store operation for entity [OrderHeader]:
org.ofbiz.entity.GenericEntityException: Error while updating OrderHeader
(SQL Exception: (Lock wait timeout exceeded; try restarting transaction)).
Rolling back transaction.
Exception: org.ofbiz.entity.GenericEntityException
Message: Error while updating OrderHeader (SQL Exception: (Lock wait
timeout exceeded; try restarting transaction))
---- cause
---------------------------------------------------------------------
Exception: org.ofbiz.entity.GenericDataSourceException
Message: SQL Exception: (Lock wait timeout exceeded; try restarting
transaction)
---- cause
---------------------------------------------------------------------
Exception: java.sql.SQLException
Message: Lock wait timeout exceeded; try restarting transaction

--
Regards,
Varun Bhansaly

[Varun Bhansaly]

For now have increased value for "innodb_lock_wait_timeout" from 50 to 300.
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout


On Thu, May 3, 2012 at 7:54 AM, varun bhansaly <[hidden email]> wrote:

> Hi,
> I'm using ofbiz release10.04@1072382 with MySQL database server (with
> default settings).
>
> Of late I have been observing lots of 'lock wait time out' exceptions in
> the logs primarily while the system is trying to update entity OrderHeader.
>
> If anyone has any suggestions please do share.
>
> Below is the portion of the exception trace -
>
> 2012-05-01 18:51:34,865 [ERROR] SQL Exception while executing the
> following: UPDATE ORDER_HEADER SET ORDER_TYPE_ID=?, ORDER_NAME=?,
> EXTERNAL_ID=?, SALES_CHANNEL_ENUM_ID=?, ORDER_DATE=?, PRIORITY=?,
> ENTRY_DATE=?, PICK_SHEET_PRINTED_DATE=?, VISIT_ID=?, STATUS_ID=?,
> CREATED_BY=?, FIRST_ATTEMPT_ORDER_ID=?, CURRENCY_UOM=?, SYNC_STATUS_ID=?,
> BILLING_ACCOUNT_ID=?, ORIGIN_FACILITY_ID=?, WEB_SITE_ID=?,
> PRODUCT_STORE_ID=?, TERMINAL_ID=?, TRANSACTION_ID=?,
> AUTO_ORDER_SHOPPING_LIST_ID=?, NEEDS_INVENTORY_ISSUANCE=?, IS_RUSH_ORDER=?,
> INTERNAL_CODE=?, REMAINING_SUB_TOTAL=?, GRAND_TOTAL=?, IS_VIEWED=?,
> LAST_UPDATED_STAMP=?, LAST_UPDATED_TX_STAMP=?, CREATED_STAMP=?,
> CREATED_TX_STAMP=? WHERE ORDER_ID=?
> 2012-05-01 18:51:34,867 [ERROR] Error while updating
> [GenericEntity:OrderHeader][autoOrderShoppingListId,null()][billingAccountId,null()][createdBy,admin(java.lang.String)][createdStamp,2012-05-01
> 18:48:57.0(java.sql.Timestamp)][createdTxStamp,2012-05-01
> 18:48:57.0(java.sql.Timestamp)][currencyUom,USD(java.lang.String)][entryDate,2012-05-01
> 18:48:57.0(java.sql.Timestamp)][externalId,null()][firstAttemptOrderId,null()][grandTotal,72.00(java.math.BigDecimal)][internalCode,null()][isRushOrder,null()][isViewed,null()][lastUpdatedStamp,2012-05-01
> 18:50:43.297(java.sql.Timestamp)][lastUpdatedTxStamp,2012-05-01
> 18:50:42.078(java.sql.Timestamp)][needsInventoryIssuance,null()][orderDate,2012-05-01
> 18:48:57.0(java.sql.Timestamp)][orderId,SE11510(java.lang.String)][orderName,Test
> Order(java.lang.String)][orderTypeId,SALES_ORDER(java.lang.String)][originFacilityId,null()][pickSheetPrintedDate,null()][priority,2(java.lang.String)][productStoreId,9000(java.lang.String)][remainingSubTotal,72.00(java.math.BigDecimal)][salesChannelEnumId,WEB_SALES_CHANNEL(java.lang.String)][statusId,ORDER_COMPLETED(java.lang.String)][syncStatusId,null()][terminalId,null()][transactionId,null()][visitId,5171(java.lang.String)][webSiteId,OrderEntry(java.lang.String)]
> 2012-05-01 18:51:34,869 [ERROR]
> ---- exception report
> ----------------------------------------------------------
> Failure in store operation for entity [OrderHeader]:
> org.ofbiz.entity.GenericEntityException: Error while updating OrderHeader
> (SQL Exception: (Lock wait timeout exceeded; try restarting transaction)).
> Rolling back transaction.
> Exception: org.ofbiz.entity.GenericEntityException
> Message: Error while updating OrderHeader (SQL Exception: (Lock wait
> timeout exceeded; try restarting transaction))
> ---- cause
> ---------------------------------------------------------------------
> Exception: org.ofbiz.entity.GenericDataSourceException
> Message: SQL Exception: (Lock wait timeout exceeded; try restarting
> transaction)
> ---- cause
> ---------------------------------------------------------------------
> Exception: java.sql.SQLException
> Message: Lock wait timeout exceeded; try restarting transaction
>
> --
> Regards,
> Varun Bhansaly
>

--
Regards,
Varun Bhansaly

[Mike Z]

Try running tuning-primer.sh on the DB host and make changes as recommended.

https://launchpad.net/mysql-tuning-primer

On Thu, May 3, 2012 at 7:59 AM, varun bhansaly <[hidden email]> wrote:

> For now have increased value for "innodb_lock_wait_timeout" from 50 to 300.
>
> http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
>
>
> On Thu, May 3, 2012 at 7:54 AM, varun bhansaly <[hidden email]>
> wrote:
>
> > Hi,
> > I'm using ofbiz release10.04@1072382 with MySQL database server (with
> > default settings).
> >
> > Of late I have been observing lots of 'lock wait time out' exceptions in
> > the logs primarily while the system is trying to update entity
> OrderHeader.
> >
> > If anyone has any suggestions please do share.
> >
> > Below is the portion of the exception trace -
> >
> > 2012-05-01 18:51:34,865 [ERROR] SQL Exception while executing the
> > following: UPDATE ORDER_HEADER SET ORDER_TYPE_ID=?, ORDER_NAME=?,
> > EXTERNAL_ID=?, SALES_CHANNEL_ENUM_ID=?, ORDER_DATE=?, PRIORITY=?,
> > ENTRY_DATE=?, PICK_SHEET_PRINTED_DATE=?, VISIT_ID=?, STATUS_ID=?,
> > CREATED_BY=?, FIRST_ATTEMPT_ORDER_ID=?, CURRENCY_UOM=?, SYNC_STATUS_ID=?,
> > BILLING_ACCOUNT_ID=?, ORIGIN_FACILITY_ID=?, WEB_SITE_ID=?,
> > PRODUCT_STORE_ID=?, TERMINAL_ID=?, TRANSACTION_ID=?,
> > AUTO_ORDER_SHOPPING_LIST_ID=?, NEEDS_INVENTORY_ISSUANCE=?,
> IS_RUSH_ORDER=?,
> > INTERNAL_CODE=?, REMAINING_SUB_TOTAL=?, GRAND_TOTAL=?, IS_VIEWED=?,
> > LAST_UPDATED_STAMP=?, LAST_UPDATED_TX_STAMP=?, CREATED_STAMP=?,
> > CREATED_TX_STAMP=? WHERE ORDER_ID=?
> > 2012-05-01 18:51:34,867 [ERROR] Error while updating
> >
> [GenericEntity:OrderHeader][autoOrderShoppingListId,null()][billingAccountId,null()][createdBy,admin(java.lang.String)][createdStamp,2012-05-01
> > 18:48:57.0(java.sql.Timestamp)][createdTxStamp,2012-05-01
> >
> 18:48:57.0(java.sql.Timestamp)][currencyUom,USD(java.lang.String)][entryDate,2012-05-01
> >
> 18:48:57.0(java.sql.Timestamp)][externalId,null()][firstAttemptOrderId,null()][grandTotal,72.00(java.math.BigDecimal)][internalCode,null()][isRushOrder,null()][isViewed,null()][lastUpdatedStamp,2012-05-01
> > 18:50:43.297(java.sql.Timestamp)][lastUpdatedTxStamp,2012-05-01
> >
> 18:50:42.078(java.sql.Timestamp)][needsInventoryIssuance,null()][orderDate,2012-05-01
> >
> 18:48:57.0(java.sql.Timestamp)][orderId,SE11510(java.lang.String)][orderName,Test
> >
> Order(java.lang.String)][orderTypeId,SALES_ORDER(java.lang.String)][originFacilityId,null()][pickSheetPrintedDate,null()][priority,2(java.lang.String)][productStoreId,9000(java.lang.String)][remainingSubTotal,72.00(java.math.BigDecimal)][salesChannelEnumId,WEB_SALES_CHANNEL(java.lang.String)][statusId,ORDER_COMPLETED(java.lang.String)][syncStatusId,null()][terminalId,null()][transactionId,null()][visitId,5171(java.lang.String)][webSiteId,OrderEntry(java.lang.String)]
> > 2012-05-01 18:51:34,869 [ERROR]
> > ---- exception report
> > ----------------------------------------------------------
> > Failure in store operation for entity [OrderHeader]:
> > org.ofbiz.entity.GenericEntityException: Error while updating OrderHeader
> > (SQL Exception: (Lock wait timeout exceeded; try restarting
> transaction)).
> > Rolling back transaction.
> > Exception: org.ofbiz.entity.GenericEntityException
> > Message: Error while updating OrderHeader (SQL Exception: (Lock wait
> > timeout exceeded; try restarting transaction))
> > ---- cause
> > ---------------------------------------------------------------------
> > Exception: org.ofbiz.entity.GenericDataSourceException
> > Message: SQL Exception: (Lock wait timeout exceeded; try restarting
> > transaction)
> > ---- cause
> > ---------------------------------------------------------------------
> > Exception: java.sql.SQLException
> > Message: Lock wait timeout exceeded; try restarting transaction
> >
> > --
> > Regards,
> > Varun Bhansaly
> >
>
>
>
> --
> Regards,
> Varun Bhansaly
>

[Varun Bhansaly]

This change does not help - still getting the same exceptions.

On Thu, May 3, 2012 at 8:29 PM, varun bhansaly <[hidden email]> wrote:

> For now have increased value for "innodb_lock_wait_timeout" from 50 to 300.
>
> http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout
>
>
>
> On Thu, May 3, 2012 at 7:54 AM, varun bhansaly <[hidden email]>wrote:
>
>> Hi,
>> I'm using ofbiz release10.04@1072382 with MySQL database server (with
>> default settings).
>>
>> Of late I have been observing lots of 'lock wait time out' exceptions in
>> the logs primarily while the system is trying to update entity OrderHeader.
>>
>> If anyone has any suggestions please do share.
>>
>> Below is the portion of the exception trace -
>>
>> 2012-05-01 18:51:34,865 [ERROR] SQL Exception while executing the
>> following: UPDATE ORDER_HEADER SET ORDER_TYPE_ID=?, ORDER_NAME=?,
>> EXTERNAL_ID=?, SALES_CHANNEL_ENUM_ID=?, ORDER_DATE=?, PRIORITY=?,
>> ENTRY_DATE=?, PICK_SHEET_PRINTED_DATE=?, VISIT_ID=?, STATUS_ID=?,
>> CREATED_BY=?, FIRST_ATTEMPT_ORDER_ID=?, CURRENCY_UOM=?, SYNC_STATUS_ID=?,
>> BILLING_ACCOUNT_ID=?, ORIGIN_FACILITY_ID=?, WEB_SITE_ID=?,
>> PRODUCT_STORE_ID=?, TERMINAL_ID=?, TRANSACTION_ID=?,
>> AUTO_ORDER_SHOPPING_LIST_ID=?, NEEDS_INVENTORY_ISSUANCE=?, IS_RUSH_ORDER=?,
>> INTERNAL_CODE=?, REMAINING_SUB_TOTAL=?, GRAND_TOTAL=?, IS_VIEWED=?,
>> LAST_UPDATED_STAMP=?, LAST_UPDATED_TX_STAMP=?, CREATED_STAMP=?,
>> CREATED_TX_STAMP=? WHERE ORDER_ID=?
>> 2012-05-01 18:51:34,867 [ERROR] Error while updating
>> [GenericEntity:OrderHeader][autoOrderShoppingListId,null()][billingAccountId,null()][createdBy,admin(java.lang.String)][createdStamp,2012-05-01
>> 18:48:57.0(java.sql.Timestamp)][createdTxStamp,2012-05-01
>> 18:48:57.0(java.sql.Timestamp)][currencyUom,USD(java.lang.String)][entryDate,2012-05-01
>> 18:48:57.0(java.sql.Timestamp)][externalId,null()][firstAttemptOrderId,null()][grandTotal,72.00(java.math.BigDecimal)][internalCode,null()][isRushOrder,null()][isViewed,null()][lastUpdatedStamp,2012-05-01
>> 18:50:43.297(java.sql.Timestamp)][lastUpdatedTxStamp,2012-05-01
>> 18:50:42.078(java.sql.Timestamp)][needsInventoryIssuance,null()][orderDate,2012-05-01
>> 18:48:57.0(java.sql.Timestamp)][orderId,SE11510(java.lang.String)][orderName,Test
>> Order(java.lang.String)][orderTypeId,SALES_ORDER(java.lang.String)][originFacilityId,null()][pickSheetPrintedDate,null()][priority,2(java.lang.String)][productStoreId,9000(java.lang.String)][remainingSubTotal,72.00(java.math.BigDecimal)][salesChannelEnumId,WEB_SALES_CHANNEL(java.lang.String)][statusId,ORDER_COMPLETED(java.lang.String)][syncStatusId,null()][terminalId,null()][transactionId,null()][visitId,5171(java.lang.String)][webSiteId,OrderEntry(java.lang.String)]
>> 2012-05-01 18:51:34,869 [ERROR]
>> ---- exception report
>> ----------------------------------------------------------
>> Failure in store operation for entity [OrderHeader]:
>> org.ofbiz.entity.GenericEntityException: Error while updating OrderHeader
>> (SQL Exception: (Lock wait timeout exceeded; try restarting transaction)).
>> Rolling back transaction.
>> Exception: org.ofbiz.entity.GenericEntityException
>> Message: Error while updating OrderHeader (SQL Exception: (Lock wait
>> timeout exceeded; try restarting transaction))
>> ---- cause
>> ---------------------------------------------------------------------
>> Exception: org.ofbiz.entity.GenericDataSourceException
>> Message: SQL Exception: (Lock wait timeout exceeded; try restarting
>> transaction)
>> ---- cause
>> ---------------------------------------------------------------------
>> Exception: java.sql.SQLException
>> Message: Lock wait timeout exceeded; try restarting transaction
>>
>> --
>> Regards,
>> Varun Bhansaly
>>
>
>
>
> --
> Regards,
> Varun Bhansaly
>
>

--
Regards,
Varun Bhansaly