Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042521Openbravo ERPI. Performancepublic2019-12-11 17:192019-12-18 12:11
Reportersalvador_campanella 
Assigned ToAtulOpenbravo 
PriorityurgentSeveritycriticalReproducibilitysometimes
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target VersionFixed in Version3.0PR20Q1 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0042521: The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems
DescriptionThe m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems


Extract of Postgres log
-----------------------
2019-12-09 10:30:15 CST 2019-12-09 09:56:43 CST 19347 11/3630307 2777469593 LOG: process 19347 acquired ShareLock on transaction 2777459601 after 1013158.638 ms
2019-12-09 10:30:15 CST 2019-12-09 09:56:43 CST 19347 11/3630307 2777469593 CONTEXT: while locking tuple (534,3) in relation "m_pricelist"
    SQL statement "SELECT C_ORDER.c_order_id, C_ORDER.c_bpartner_id, c_order.M_PriceList_ID, c_orderline.explode
                                                                        FROM C_ORDER JOIN m_pricelist ON c_order.m_pricelist_id = m_pricelist.m_pricelist_id JOIN c_orderline ON c_order.c_order_id = c_orderline.c_order_id
        WHERE c_orderline.c_orderline_id=v_Record_ID FOR UPDATE"
    PL/pgSQL function m_explodebomnotstock(character varying,character varying) line 73 at SQL statement
    SQL statement "SELECT M_EXPLODEBOMNOTSTOCK(null, CUR_BOM_Line.c_orderline_ID)"
    PL/pgSQL function c_order_post1(character varying,character varying,character varying) line 1176 at PERFORM
    SQL statement "SELECT C_ORDER_POST1($1, $2, 'Y')"
    PL/pgSQL function c_order_post1(character varying,character varying) line 3 at PERFORM
    SQL statement "SELECT C_ORDER_POST1(p_PInstance_ID, NULL)"
    PL/pgSQL function c_order_post(character varying) line 17 at PERFORM
Steps To Reproducen/a
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on backport 00425733.0PR19Q4.1 closed AtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 
depends on backport 00425743.0PR19Q3.3 closed AtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2019-12-11 17:19salvador_campanellaNew Issue
2019-12-11 17:19salvador_campanellaAssigned To => Triage Finance
2019-12-11 17:19salvador_campanellaModules => Core
2019-12-11 17:19salvador_campanellaResolution time => 1577919600
2019-12-11 17:19salvador_campanellaTriggers an Emergency Pack => No
2019-12-11 18:05rafarodaIssue Monitored: rafaroda
2019-12-12 08:37dmiguelezAssigned ToTriage Finance => AtulOpenbravo
2019-12-12 09:27alostaleIssue Monitored: alostale
2019-12-12 13:16salvador_campanellaNote Added: 0116214
2019-12-13 11:57AtulOpenbravoStatusnew => scheduled
2019-12-16 11:05dmiguelezNote Added: 0116332
2019-12-16 16:14martinsdanIssue Monitored: martinsdan
2019-12-16 17:17AtulOpenbravoStatusscheduled => acknowledged
2019-12-16 17:19AtulOpenbravoStatusacknowledged => scheduled
2019-12-18 09:07PracticsIssue Monitored: Practics
2019-12-18 12:08hgbotCheckin
2019-12-18 12:08hgbotNote Added: 0116401
2019-12-18 12:08hgbotStatusscheduled => resolved
2019-12-18 12:08hgbotResolutionopen => fixed
2019-12-18 12:08hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/f385f3e597992614f8211006402af4e00920f0e7 [^]
2019-12-18 12:11dmiguelezReview Assigned To => dmiguelez
2019-12-18 12:11dmiguelezNote Added: 0116404
2019-12-18 12:11dmiguelezStatusresolved => closed
2019-12-18 12:11dmiguelezFixed in Version => 3.0PR20Q1

Notes
(0116214)
salvador_campanella   
2019-12-12 13:16   
The problem seems to be related to the m_explodebomnotstock function that generates locks on the m_pricelist table where any transaction that contains a query to a table with the m_pricelist_id foreign key will be blocked and put in a waitlist

For example, if the function explodebomnotstock takes to finish, the transactions that contain c_order table won't be able to execute because the c_order table have a m_pricelist_id foreign key

The explodebomnotstock function is called when one order has a product with the BOM flag activated, and when the order is processed the functioning generates lines in the order with the products that make up the order
(0116332)
dmiguelez   
2019-12-16 11:05   
Test Plan:

Go to Product Window and create a new record with:
  - Organization: *
  - SearchKey: Test
  - Name: Test
  - Bill of Materials: Yes
  - Stocked: No
Go to Bill Of Materials Tab and create two records with:
  - Product: Bebida energética | Quantity: 1
  - Product: Agua sin gas | Quantity: 1
Go to Price Tab and create a new record with:
  - Price List Version: Tarifa de ventas
  - Unit Price: 10
  - List Price: 10

Go to Sales Order Window and create a new record with:
  - Organization: F&B España - Región Norte
  - Business Partner: Alimentos y Supermercados
Go to Lines Tab and create a new record with:
  - Product: Test
  - Ordered Quantity: 1

With a terminal, log in into psql using the same database as the Openbravo instance that is running
For example: psql -U tad -d openbravo -h localhost
Write:
  - begin;
  - select * from c_order_post1(null, 'ORDER_ID'); -- where order_id is the id of the order created previously

In Openbravo, go to Sales Order Window and create a new record with:
  - Organization: F&B España - Región Norte
  - Business Partner: Alimentos y Supermercados
Go to Lines Tab and create a new record with:
  - Product: Cerveza Ale
  - Ordered Quantity: 1
Book it. The process finishes successfully

Go to the previously created order and Book it.
The process is stuck because it is blocked by the previous session. This is the expected behavior
(0116401)
hgbot   
2019-12-18 12:08   
Repository: erp/devel/pi
Changeset: f385f3e597992614f8211006402af4e00920f0e7
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Wed Dec 18 10:51:37 2019 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/f385f3e597992614f8211006402af4e00920f0e7 [^]

Fixes BUG-42521: Solves problem with lock in pricelist table

  * Remove join to pricelist table, that is not used, from the query
  * Remove extra 'For Update' sentence, as the document and document tables
    are alredy blocked by the first 'For Update' statement

---
M src-db/database/model/functions/M_EXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INOUTEXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INVEXPLODEBOMNOTSTOCK.xml
---
(0116404)
dmiguelez   
2019-12-18 12:11   
Code Review + Testing Ok