Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035855Openbravo ERPA. Platformpublic2017-04-27 09:582017-08-04 13:29
Reporteralostale 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035855: many standard requests borrow more than one connection from DB
DescriptionMany standard request retrieve several connections from DB. This is problematic in terms of scalibility because more connections than the strictly required making to reach the maximum number of available connection earlier than it should.

The connections typically borrowed from pool are:
1. A connection from HttpBaseServlet ConnectionProvider implementation for xsql queries, used in HSAS for basic queries (such as permissions and so)
2. A DAL connection
3. When in applies, a transaction connection to log usage audit
Steps To ReproduceConfig:
0.1 Install org.openbravo.util.db to enable pool debugging
0.2 Enable it in Openbravo.properties: db.pool.interceptor.enabled=true

Flow 1:
1.1 Login

Flow 2:
2.1 Open Sales Order window

In all flows check openbravo.log: several connections by thread are reported.
Proposed SolutionConsolidate, at least for basic infrastructural queries, these connections in a single one making use of the DAL connection and getting rid of the rest.
Additional Information
TagsPerformance
Relationships
related to defect 0035754 closed AugustoMauch Retail Modules MobileServerController.getThisServerDefinition creates contention 
depends on defect 0035193 closed caristu Openbravo ERP Error fired when trying to print and attach a document 
depends on defect 0036352 acknowledged Triage Platform Base Openbravo ERP Still there are requests for standard 2.50 buttons that borrow 2 connections 
related to defect 0035865 closed alostale Openbravo ERP remove unused UsageAudit methods 
related to defect 0036228 closed alostale Openbravo ERP SimpleCallout used as ConnectionProvider borrows 2 connections in same thread 
related to defect 0036290 closed alostale Retail Modules WebPOS login borrows 2 connections from DB pool 
related to design defect 0036291 acknowledged Triage Platform Base Openbravo ERP even not configured, RO pool uses a different pool instance 
related to design defect 0036293 acknowledged Triage Platform Base Openbravo ERP XmlEngine page don't use DAL's connection 
related to defect 0036299 closed alostale Openbravo ERP many requests for standard 2.50 buttons borrow 2 connections 
related to defect 0036609 closed alostale Openbravo ERP 2* flush in FIC when only 1 is required 
causes defect 0036001 closed alostale Openbravo ERP Connection leak after start the system having any servlet with "loaded-on-startup" for example: Analytics module installed 
Not all the children of this issue are yet resolved or closed.
Attached Filespng Selection_180.png (39,994) 2017-05-05 08:55
https://issues.openbravo.com/file_download.php?file_id=10725&type=bug
png
Issue History
Date ModifiedUsernameFieldChange
2017-04-27 09:58alostaleNew Issue
2017-04-27 09:58alostaleAssigned To => platform
2017-04-27 09:58alostaleModules => Core
2017-04-27 09:58alostaleTriggers an Emergency Pack => No
2017-04-27 09:58alostaleTag Attached: Performance
2017-04-27 10:00alostaleRelationship addedrelated to 0035754
2017-04-27 10:00alostaleStatusnew => scheduled
2017-04-27 10:00alostaleAssigned Toplatform => alostale
2017-04-27 10:01alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=15065#r15065
2017-04-27 15:21alostaleRelationship addedrelated to 0035865
2017-04-28 10:04alostaleNote Added: 0096252
2017-05-03 10:26hgbotCheckin
2017-05-03 10:26hgbotNote Added: 0096332
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096333
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096335
2017-05-03 10:27hgbotStatusscheduled => resolved
2017-05-03 10:27hgbotResolutionopen => fixed
2017-05-03 10:27hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/4c129bcea4c0431d175115ffef48b99457c48449 [^]
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096337
2017-05-03 10:27hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/4c129bcea4c0431d175115ffef48b99457c48449 [^] => http://code.openbravo.com/erp/devel/pi/rev/d19e0b10bb458feda6033d07feccdf384b2374a4 [^]
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096338
2017-05-03 10:27hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/d19e0b10bb458feda6033d07feccdf384b2374a4 [^] => http://code.openbravo.com/erp/devel/pi/rev/6d6a1ff11e480d5583f967920da888973ee00575 [^]
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096339
2017-05-03 10:27hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/6d6a1ff11e480d5583f967920da888973ee00575 [^] => http://code.openbravo.com/erp/devel/pi/rev/6fb8997012e9f75a70ee27fbbc8732cc3036d361 [^]
2017-05-03 10:27hgbotCheckin
2017-05-03 10:27hgbotNote Added: 0096340
2017-05-03 10:27hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/6fb8997012e9f75a70ee27fbbc8732cc3036d361 [^] => http://code.openbravo.com/erp/devel/pi/rev/ae4c54d915ad140aed100163c7975ef0db2eb25a [^]
2017-05-03 10:35alostaleReview Assigned To => caristu
2017-05-05 08:55alostaleFile Added: Selection_180.png
2017-05-05 08:56alostaleNote Added: 0096409
2017-05-05 12:25alostaleRelationship addeddepends on 0035193
2017-05-05 12:29alostaleNote Added: 0096417
2017-05-05 12:30alostaleNote Edited: 0096417bug_revision_view_page.php?bugnote_id=0096417#r15117
2017-05-05 12:30alostaleNote Edited: 0096417bug_revision_view_page.php?bugnote_id=0096417#r15118
2017-05-09 09:27hgbotCheckin
2017-05-09 09:27hgbotNote Added: 0096466
2017-05-09 11:52caristuNote Added: 0096486
2017-05-09 11:52caristuStatusresolved => closed
2017-05-09 11:52caristuFixed in Version => 3.0PR17Q3
2017-05-19 08:46caristuRelationship addedcauses 0036001
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096922
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096923
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096924
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096926
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096927
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096928
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096929
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096950
2017-06-12 13:47alostaleRelationship addedrelated to 0036228
2017-06-20 09:40alostaleRelationship addedrelated to 0036290
2017-06-20 10:45alostaleRelationship addedrelated to 0036291
2017-06-20 11:13alostaleRelationship addedrelated to 0036293
2017-06-20 14:49alostaleRelationship addedrelated to 0036299
2017-08-04 13:29alostaleRelationship addeddepends on 0036352
2017-08-10 10:48alostaleRelationship addedrelated to 0036609

Notes
(0096252)
alostale   
2017-04-28 10:04   
Note it's not possible to get rid of them within a small effort for the 2.50 pages using XmlEngine as a XmlEngine instance is cached per servlet instance. DalConnectionProvider is not thread safe, therefore it's not safe to cache it in XmlEngine.
(0096332)
hgbot   
2017-05-03 10:26   
Repository: erp/devel/pi
Changeset: 275afe71cbfd4e1ddd7e1b04671559bd01614af2
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 27 10:18:39 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/275afe71cbfd4e1ddd7e1b04671559bd01614af2 [^]

related to bug 35855: requests borrow more than one connection from DB

  Solved Login window + HSAS infrastructure by using only connection obtained
  for DAL.

---
M src/org/openbravo/base/secureApp/HttpSecureAppServlet.java
M src/org/openbravo/base/secureApp/LoginHandler.java
---
(0096333)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: 0135d8bfedaa9bd7a801b1b263204f9df50c43d2
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 27 10:48:37 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0135d8bfedaa9bd7a801b1b263204f9df50c43d2 [^]

related to bug 35855: requests borrow more than one connection from DB

  Usage audit is now inserted within the same DAL transaction.

---
M src/org/openbravo/erpCommon/security/SessionLogin_data.xsql
M src/org/openbravo/erpCommon/security/UsageAudit.java
---
(0096335)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: 4c129bcea4c0431d175115ffef48b99457c48449
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 27 14:37:18 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/4c129bcea4c0431d175115ffef48b99457c48449 [^]

fixed bug 35855: requests borrow more than one connection from DB

  Prevent double connection when generating dynamic js for 2.50 windows

---
M src/org/openbravo/erpCommon/utility/DynamicJS.java
---
(0096337)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: d19e0b10bb458feda6033d07feccdf384b2374a4
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 27 16:50:24 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/d19e0b10bb458feda6033d07feccdf384b2374a4 [^]

fixed bug 35855: requests borrow more than one connection from DB

  Some more cases in login

---
M src/org/openbravo/authentication/AuthenticationManager.java
M src/org/openbravo/erpCommon/security/Login.java
---
(0096338)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: 6d6a1ff11e480d5583f967920da888973ee00575
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 27 17:50:23 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/6d6a1ff11e480d5583f967920da888973ee00575 [^]

fixed bug 35855: requests borrow more than one connection from DB

  More cases in login page

---
M src/org/openbravo/erpCommon/security/Login.java
---
(0096339)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: 6fb8997012e9f75a70ee27fbbc8732cc3036d361
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Fri Apr 28 09:37:24 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/6fb8997012e9f75a70ee27fbbc8732cc3036d361 [^]

fixed bug 35855: requests borrow more than one connection from DB

  Partially revererted changes in AuthenticationManager. DalConnectionProvider
  is not thread safe, if accessed by 2 threads it is possible T1 to acquire a
  connection that's closed by T2 while still in use by T1.

  So DCP is not cached in AuthenticationManager but cached pool is not used in
  DefaultAuthenticationManger, instead it creates its own DCP in authenticate
  method.

---
M src/org/openbravo/authentication/AuthenticationManager.java
M src/org/openbravo/authentication/basic/DefaultAuthenticationManager.java
M src/org/openbravo/service/db/DalConnectionProvider.java
---
(0096340)
hgbot   
2017-05-03 10:27   
Repository: erp/devel/pi
Changeset: ae4c54d915ad140aed100163c7975ef0db2eb25a
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue May 02 09:27:56 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/ae4c54d915ad140aed100163c7975ef0db2eb25a [^]

fixed bug 35855: requests borrow more than one connection from DB

   Before starting module rebuild through MMC, finalize current transaction to
   release any lock it might have acquired.

   This is now needed because HSAS for this servlet have occurred in the same
   transaction and are still held, before they were already realeased becuase
   they were executed in auto-commit short transactions.

---
M src/org/openbravo/erpCommon/ad_process/ApplyModules.java
---
(0096409)
alostale   
2017-05-05 08:56   
Attached screenshot of munin DB connections graph in a real customer with a daily 250 concurrent backoffice users.
(0096417)
alostale   
2017-05-05 12:29   
(edited on: 2017-05-05 12:30)
Note for backports: if backporting this issue to <3.0PR17Q2, take into account it is required to also backport 0035193.

Because now we're reusing the same DAL connection when before a new one was used, it is possible DAL connection to be closed (committed/rolled back) when it's tried to be used by any of these replacements. Therefore DalConnectionProvider.getConnection needs to ensure it is open and if not, create a new one.
(0096466)
hgbot   
2017-05-09 09:27   
Repository: erp/devel/pi
Changeset: 426a5270b6a3270eed70c528b8ed9f1feae4dcc4
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue May 09 09:26:35 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/426a5270b6a3270eed70c528b8ed9f1feae4dcc4 [^]

related to bug 35855: requests borrow more than one connection from DB

  Reuse connection to use incorrect user/password message

---
M src/org/openbravo/base/secureApp/LoginHandler.java
---
(0096486)
caristu   
2017-05-09 11:52   
Reviewed
(0096922)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096923)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096924)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096926)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096927)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096928)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096929)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0096950)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test