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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0042574
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] I. Performancecriticalsometimes2019-12-11 17:192019-12-18 12:11
Reportersalvador_campanellaView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityurgentResolutionfixedFixed in Version3.0PR19Q3.3
StatusclosedFix in branchFixed in SCM revision8645d2244ee9
ProjectionnoneETAnoneTarget Version3.0PR19Q3.3
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionpiSCM revision 
Review Assigned Todmiguelez
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0042574: 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 ]
blocks defect 0042521 closedAtulOpenbravo The m_explodebomnotstock function creates locks in m_pricelist table causing big performance problems 

-  Notes
(0116361)
AtulOpenbravo (developer)
2019-12-16 17:19

 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
(0116398)
hgbot (developer)
2019-12-18 12:08

 Repository: erp/backports/3.0PR19Q3.3
Changeset: f2bf2a6fb19585bcea6b02a8e123db0ee8b55621
Author: David Miguelez <david.miguelez <at> openbravo.com>
Date: Wed Dec 18 11:23:08 2019 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR19Q3.3/rev/f2bf2a6fb19585bcea6b02a8e123db0ee8b55621 [^]

Related to Issue 42574. Partial fix of Issue 40823 must be included in order
to be compatible with the changes done to solve the proper issue.

The changes included are to avoid using functions that are deprecated.

---
M src-db/database/model/functions/M_EXPLODEBOMNOTSTOCK.xml
M src-db/database/model/functions/M_INVEXPLODEBOMNOTSTOCK.xml
---
(0116400)
hgbot (developer)
2019-12-18 12:08

 Repository: erp/backports/3.0PR19Q3.3
Changeset: 8645d2244ee93719b5e126ed0348301078d05314
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Wed Dec 18 11:26:19 2019 +0100
URL: http://code.openbravo.com/erp/backports/3.0PR19Q3.3/rev/8645d2244ee93719b5e126ed0348301078d05314 [^]

Fixes BUG-42574: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
---
(0116402)
dmiguelez (developer)
2019-12-18 12:11

 Code Review + Testing Ok

- Issue History
Date Modified Username Field Change
2019-12-16 17:19 AtulOpenbravo Type defect => backport
2019-12-16 17:19 AtulOpenbravo Target Version => 3.0PR19Q3.3
2019-12-16 17:19 AtulOpenbravo Note Added: 0116361
2019-12-18 12:08 hgbot Checkin
2019-12-18 12:08 hgbot Note Added: 0116398
2019-12-18 12:08 hgbot Checkin
2019-12-18 12:08 hgbot Note Added: 0116400
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/backports/3.0PR19Q3.3/rev/8645d2244ee93719b5e126ed0348301078d05314 [^]
2019-12-18 12:11 dmiguelez Review Assigned To => dmiguelez
2019-12-18 12:11 dmiguelez Note Added: 0116402
2019-12-18 12:11 dmiguelez Status resolved => closed
2019-12-18 12:11 dmiguelez Fixed in Version => 3.0PR19Q3.3

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker