Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0042521
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] I. Performancecriticalsometimes2019-12-11 17:192019-12-18 12:11
Reportersalvador_campanellaView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityurgentResolutionfixedFixed in Version3.0PR20Q1
StatusclosedFix in branchFixed in SCM revisionf385f3e59799
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionpiSCM revision 
Merge Request Status
Review Assigned Todmiguelez
OBNetwork customerGold
Web browser
ModulesCore
Support ticket13607
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0042521: 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
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 00425733.0PR19Q4.1 closedAtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 
depends on backport 00425743.0PR19Q3.3 closedAtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 

-  Notes
(0116214)
salvador_campanella (viewer)
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 (viewer)
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 (developer)
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 (viewer)
2019-12-18 12:11

 Code Review + Testing Ok

- Issue History
Date Modified Username Field Change
2019-12-11 17:19 salvador_campanella New Issue
2019-12-11 17:19 salvador_campanella Assigned To => Triage Finance
2019-12-11 17:19 salvador_campanella OBNetwork customer => Gold
2019-12-11 17:19 salvador_campanella Modules => Core
2019-12-11 17:19 salvador_campanella Support ticket => 13607
2019-12-11 17:19 salvador_campanella Resolution time => 1577919600
2019-12-11 17:19 salvador_campanella Triggers an Emergency Pack => No
2019-12-11 18:05 rafaroda Issue Monitored: rafaroda
2019-12-12 08:37 dmiguelez Assigned To Triage Finance => AtulOpenbravo
2019-12-12 09:27 alostale Issue Monitored: alostale
2019-12-12 13:16 salvador_campanella Note Added: 0116214
2019-12-13 11:57 AtulOpenbravo Status new => scheduled
2019-12-16 11:05 dmiguelez Note Added: 0116332
2019-12-16 16:14 martinsdan Issue Monitored: martinsdan
2019-12-16 17:17 AtulOpenbravo Status scheduled => acknowledged
2019-12-16 17:19 AtulOpenbravo Status acknowledged => scheduled
2019-12-18 09:07 Practics Issue Monitored: Practics
2019-12-18 12:08 hgbot Checkin
2019-12-18 12:08 hgbot Note Added: 0116401
2019-12-18 12:08 hgbot Status scheduled => resolved
2019-12-18 12:08 hgbot Resolution open => fixed
2019-12-18 12:08 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/f385f3e597992614f8211006402af4e00920f0e7 [^]
2019-12-18 12:11 dmiguelez Review Assigned To => dmiguelez
2019-12-18 12:11 dmiguelez Note Added: 0116404
2019-12-18 12:11 dmiguelez Status resolved => closed
2019-12-18 12:11 dmiguelez Fixed in Version => 3.0PR20Q1

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker