Openbravo Issue Tracking System - Retail Modules
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0041604Retail ModulesWeb POSpublic2019-08-08 10:572019-09-12 11:21
Reporteraaroncalero 
Assigned Toranjith_qualiantech_com 
PrioritynormalSeveritymajorReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionRR19Q4 
Merge Request Status
Review Assigned Tomarvintm
OBNetwork customer
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0041604: Deadlocks in the Orderloader updating AD_Sequence using Synchronized Mode
DescriptionWhen the "Use Order Document Number for Related Docs" is not configured, the Orderloader needs to generate sequences for shipments and invoices.
When using Synchronized mode, orders are processed as soon as they are sent from WebPOS (there are no import entry queues) and because of this several orders from the same store can be processed in parallel.

If two different transactions need to take the sequence locks in different order (see steps to reproduce) deadlocks can be generated
Steps To ReproduceLog in backend and configure the following:
*In Preference window:
  > Use Order Document Number for Related Docs = 'N'
  > Web POS Synchronized Mode = 'Y'
*In Business Partner window: configure a customer with Invoice Term = After Delivery.

Log in Web POS in terminal VBS-1:
Create a new Layaway, assign the edited customer and add a product.
Create a new ticket, assign the same customer, and add a product.
Go to Pay Open Tickets with the layaway and the draft ticket and add a payment for both documents. Do not complete the process yet.

Log in Web POS in terminal VBS1001:
Create a new ticket, assign the same customer, and add a product.
Click on the total amount button and add a payment. Do not complete the process yet.

In eclipse:
Go to the Orderloader.java and set up a breakpoint at the end of the saveRecord function, just before the "return successMessage(jsonorder);" statement.

In the VBS-1 terminal, click on Done. After a few seconds the breakpoint in the Orderloader will hit. Verify that the order being synchronized is the layaway (use the Expressions window to check the documentNo). At this point, the transaction will hold the lock of the payments sequence.

In the VBS1001 terminal, click on Done. The WebPOS will remain 'Processing Transaction' but the breakpoint in the Orderloader won't hit because the payment sequence is still held by the first transaction. However this second transaction now holds the lock for both the shipment and invoice sequences.

Continue the execution of the breakpoint in the first transaction/thread (to process the second ticket created in VBS-1). Since this second ticket needs to create a shipment and an invoice, it will try to get the lock of these sequences, which are held by the second transaction (which in turn is blocked waiting for the first transaction to finish, generating the deadlock[1])


[1] Error shown:
2019-08-08 10:32:14,579 [http-nio-8080-exec-9] ERROR org.openbravo.erpCommon.utility.DocumentNoData - SQL error in query: CALL AD_Sequence_DocType(?,?,?,?) :org.postgresql.util.PSQLException: ERROR: deadlock detected
  Detail: Process 9594 waits for ShareLock on transaction 3756869; blocked by process 4980.
Process 4980 waits for ShareLock on transaction 3756867; blocked by process 9594.
  Hint: See server log for query details.
  Where: while updating tuple (87,9) in relation "ad_sequence"
SQL statement "UPDATE AD_Sequence
      SET CurrentNext=CurrentNext + IncrementNo
    WHERE AD_Sequence_ID=v_Sequence_ID"
PL/pgSQL function ad_sequence_doctype(character varying,character varying,character) line 54 at SQL statement
Proposed SolutionChanging the Orderloader to hold the locks of all sequences (even if they are not updated) should make sure that there are no locks. The second transaction will need to wait until the first transaction has finished completely.
Additional Information
TagsNo tags attached.
Relationships
causes defect 0042195 closed ranjith_qualiantech_com While doing a Cancel this layaway, the payment no. of the returned its taking the docno. 
Attached Filespatch issue 41604 source posterminal.patch (3,576) 2019-09-04 07:45
https://issues.openbravo.com/file_download.php?file_id=13280&type=bug
Issue History
Date ModifiedUsernameFieldChange
2019-08-08 10:57aaroncaleroNew Issue
2019-08-08 10:57aaroncaleroAssigned To => Retail
2019-08-08 10:57aaroncaleroResolution time => 1566424800
2019-08-08 10:57aaroncaleroTriggers an Emergency Pack => No
2019-08-08 10:59aaroncaleroSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19279#r19279
2019-08-08 11:12aaroncaleroProposed Solution updated
2019-08-08 11:18aaroncaleroSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=19280#r19280
2019-08-13 14:49ranjith_qualiantech_comAssigned ToRetail => ranjith_qualiantech_com
2019-08-13 14:49ranjith_qualiantech_comStatusnew => scheduled
2019-08-14 08:42ranjith_qualiantech_comFile Added: issue41604 source posterminal.patch
2019-08-22 08:34ranjith_qualiantech_comFile Deleted: issue41604 source posterminal.patch
2019-08-22 08:35ranjith_qualiantech_comFile Added: issue 41604 source posterminal.patch
2019-08-23 04:57hgbotCheckin
2019-08-23 04:57hgbotNote Added: 0114240
2019-08-23 04:57hgbotStatusscheduled => resolved
2019-08-23 04:57hgbotResolutionopen => fixed
2019-08-23 04:57hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/079f14931c5f1c9056ea1a5d1dcceff89dc9d43b [^]
2019-08-23 05:37hgbotCheckin
2019-08-23 05:37hgbotNote Added: 0114243
2019-08-28 10:09ranjith_qualiantech_comFile Deleted: issue 41604 source posterminal.patch
2019-08-28 14:43marvintmReview Assigned To => marvintm
2019-08-28 14:43marvintmStatusresolved => closed
2019-08-28 14:44marvintmFixed in Version => RR19Q4
2019-09-02 16:00marvintmNote Added: 0114364
2019-09-02 16:00marvintmStatusclosed => new
2019-09-02 16:00marvintmResolutionfixed => open
2019-09-02 16:00marvintmFixed in VersionRR19Q4 =>
2019-09-04 07:45ranjith_qualiantech_comFile Added: issue 41604 source posterminal.patch
2019-09-04 15:10ranjith_qualiantech_comStatusnew => scheduled
2019-09-10 08:16hgbotCheckin
2019-09-10 08:16hgbotNote Added: 0114474
2019-09-10 08:16hgbotStatusscheduled => resolved
2019-09-10 08:16hgbotResolutionopen => fixed
2019-09-10 08:16hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/079f14931c5f1c9056ea1a5d1dcceff89dc9d43b [^] => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/cdfdf0ba632b5c519c85e66fe186f44c9a401e78 [^]
2019-09-12 11:21marvintmStatusresolved => closed
2019-09-12 11:21marvintmFixed in Version => RR19Q4
2019-11-06 08:17ranjith_qualiantech_comRelationship addedcauses 0042195

Notes
(0114240)
hgbot   
2019-08-23 04:57   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 079f14931c5f1c9056ea1a5d1dcceff89dc9d43b
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Fri Aug 23 08:27:24 2019 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/079f14931c5f1c9056ea1a5d1dcceff89dc9d43b [^]

Fixed issue 41604 : Database Trigger should be disabled at once for Payment, Shipment and Invoice

* Deadlock can appear when getting documentno while orders in OrderLoader from different terminals
  To avoid the situation, Shipment, Invoice and Payment should be processed
  before DB Trigger is enabled

---
M src/org/openbravo/retail/posterminal/OrderLoader.java
M src/org/openbravo/retail/posterminal/utility/DocumentNoHandler.java
---
(0114243)
hgbot   
2019-08-23 05:37   
Repository: tools/automation/pi-mobile
Changeset: e6ddd2cbe5f9a4a05e39077c8a6933dcdcad7af5
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Fri Aug 23 09:06:29 2019 +0530
URL: http://code.openbravo.com/tools/automation/pi-mobile/rev/e6ddd2cbe5f9a4a05e39077c8a6933dcdcad7af5 [^]

Verifies issue 41604 : Modified DeliveryModesHelper

---
M src-test/org/openbravo/test/mobile/retail/extmodules/selenium/terminals/DeliveryModesHelper.java
---
(0114364)
marvintm   
2019-09-02 15:59   
Fix is not correct. APRM payments call should happen with triggers enabled.
(0114474)
hgbot   
2019-09-10 08:16   
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: cdfdf0ba632b5c519c85e66fe186f44c9a401e78
Author: Ranjith S R <ranjith <at> qualiantech.com>
Date: Tue Sep 10 11:45:59 2019 +0530
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/cdfdf0ba632b5c519c85e66fe186f44c9a401e78 [^]

Fixed issue 41604 : Payments should be generated with Triggers Enabled in OrderLoader

---
M src/org/openbravo/retail/posterminal/OrderLoader.java
---