2pack: when packin a big 2pack can get maximum open cursors exceeded by too much statement/recordset is open
Activity
Heng Sin Low November 13, 2019 at 8:26 AM
Have not seen this with Postgresql and the fact there are multiple reports of Oracle jdbc driver cursor leak on the web, could this be one of them?
Also, looking at the code, at the PackInHandler.processDeferFKElements method, the following 4 line should probably be moved to outside of the for loop:
Connection conn = m_ctx.trx.getConnection();
DatabaseMetaData md = conn.getMetaData();
String catalog = DB.getDatabase().getCatalog();
String schema = DB.getDatabase().getSchema();
Carlos Ruiz November 13, 2019 at 8:15 AMEdited
Hi @hiep, I remember I researched that as part of IDEMPIERE-3585.
The thing is:
In Oracle the cursors are not closed on demand, but oracle dedicates a thread to close cursors.
The “correct” setting to let that work would be StatementCacheNumDeferredCloseThreads=1, but when you set that value then the server starts raising APPARENT DEADLOCKS, and behavior sometimes is not correct
See https://www.mchange.com/projects/c3p0/#statementCacheNumDeferredCloseThreads
Commit
Now, if you set the StatementCacheNumDeferredCloseThreads=0 then you don’t get the APPARENT DEADLOCKS, but you would need to raise the number of allowed open cursors in your oracle for these packin to work
Commit
So, this is configurable, you can set this in your system changing the file Oracle/pool.properties and setting the variable StatementCacheNumDeferredCloseThreads according to your required way, but there is not one way that works perfect for both scenarios.
Regards,
Carlos Ruiz
Details
Details
Assignee
Carlos RuizReporter
Hiep LqPriority
Major
14:45:36.115===========> PackIn.importXML: importXML: [100]
org.adempiere.exceptions.AdempiereException: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at org.adempiere.pipo2.PackInHandler.processDeferFKElements(PackInHandler.java:443)
at org.adempiere.pipo2.PackInHandler.endElement(PackInHandler.java:303)
at java.xml/com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.endElement(AbstractSAXParser.java:610)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanEndElement(XMLDocumentFragmentScannerImpl.java:1718)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDriver.next(XMLDocumentFragmentScannerImpl.java:2883)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentScannerImpl.next(XMLDocumentScannerImpl.java:605)
at java.xml/com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:534)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:888)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:824)
at java.xml/com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:141)
at java.xml/com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1216)
at java.xml/com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl$JAXPSAXParser.parse(SAXParserImpl.java:635)
at java.xml/com.sun.org.apache.xerces.internal.jaxp.SAXParserImpl.parse(SAXParserImpl.java:324)
at java.xml/javax.xml.parsers.SAXParser.parse(SAXParser.java:197)
at org.adempiere.pipo2.PackIn.importXML(PackIn.java:173)
at org.adempiere.pipo2.PackIn.importXML(PackIn.java:136)
at org.adempiere.pipo.srv.PipoDictionaryService.merge(PipoDictionaryService.java:106)
at org.adempiere.plugin.utils.AbstractActivator.merge(AbstractActivator.java:82)
at org.adempiere.plugin.utils.Incremental2PackActivator.packIn(Incremental2PackActivator.java:227)
at org.adempiere.plugin.utils.Incremental2PackActivator.packIn(Incremental2PackActivator.java:184)
at org.adempiere.plugin.utils.Incremental2PackActivator.installPackage(Incremental2PackActivator.java:91)