error when turning off auto commit

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

error when turning off auto commit

Chris-5
Hi,

i think i've found a bug in derby.  i'm following the guidelines from the website and posting it here before reporting it just to make sure it *is* a bug!

I'm running a set of ~50000 queries on one table, using inserts and updates, and i want to be able to roll them back so i turned off autocommit using setAutoCommit(false).  As the update runs, the memory used by the JVM increases continually until i get the following exception about 20% of the way through:

ERROR 40XT0: An internal error was identified by RawStore module.
   at org.apache.derby.iapi.error.StandardException.newException(StandardException.java)
   at org.apache.derby.impl.store.raw.xact.Xact.setActiveState(Xact.java)
   at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Xact.java)
   at org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(OpenConglomerate.java)
   at org.apache.derby.impl.store.access.heap.Heap.open(Heap.java)
   at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java)
   at org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java)
   at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(DataDictionaryImpl.java)
   at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.locateSchemaRow(DataDictionaryImpl.java)
   at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(DataDictionaryImpl.java)
   at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(QueryTreeNode.java)
   at org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(QueryTreeNode.java)
   at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(FromBaseTable.java)
   at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(FromBaseTable.java)
   at org.apache.derby.impl.sql.compile.FromList.bindTables(FromList.java)
   at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(SelectNode.java)
   at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(DMLStatementNode.java)
   at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java)
   at org.apache.derby.impl.sql.compile.ReadCursorNode.bind(ReadCursorNode.java)
   at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java)
   at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java)
   at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java)
   at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java)
   at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java)
   at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java)
   at vi.hotspot.database.DataInterface._query(DataInterface.java:181)
   at vi.hotspot.database.DataInterface.query(DataInterface.java:160)
   at vi.hotspot.database.UpdateManager.updatePartialTable(UpdateManager.java:518)
   at vi.hotspot.database.UpdateManager.updatePartialTables(UpdateManager.java:619)
   at vi.hotspot.database.UpdateManager.run(UpdateManager.java:924)
   at java.lang.Thread.run(Thread.java:534)
vi.hotspot.exception.ServerTransactionException
   at vi.hotspot.database.UpdateManager.updatePartialTable(UpdateManager.java:555)
   at vi.hotspot.database.UpdateManager.updatePartialTables(UpdateManager.java:619)
   at vi.hotspot.database.UpdateManager.run(UpdateManager.java:924)
   at java.lang.Thread.run(Thread.java:534)

Derby is running in standalone mode.

Cheers,

Chris



Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

Daniel John Debrunner
Chris wrote:

> Hi,
>
> i think i've found a bug in derby.  i'm following the guidelines from
> the website and posting it here before reporting it just to make sure it
> *is* a bug!
>
> I'm running a set of ~50000 queries on one table, using inserts and
> updates, and i want to be able to roll them back so i turned off
> autocommit using setAutoCommit(false).  As the update runs, the memory
> used by the JVM increases continually until i get the following
> exception about 20% of the way through:
>
> ERROR 40XT0: An internal error was identified by RawStore module.
>    at
> org.apache.derby.iapi.error.StandardException.newException(StandardException.java)


It looks like a bug, but a reproducible case would be needed to start
trying to fix it. Is there a chance you could post code that shows this
behaviour?

There may also be an earlier error the is the real problem, try checking
in your derby.log file.

Thanks,
Dan.


Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

Chris-5
Hi,

to demonstrate the problem, i've written some code which approximates
the code i'm using.  (attached)

I'm running Java 1.4.2 with the JVM parameter -Xmx64m.

If you run the test() method,  the memory used by the JVM  rises to
above 150mb until the following exception happens:

java.lang.OutOfMemoryError
ERROR 40XT0: An internal error was identified by RawStore module.
    at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java)
    at org.apache.derby.impl.store.raw.xact.Xact.setActiveState(Xact.java)
    at org.apache.derby.impl.store.raw.xact.Xact.openContainer(Xact.java)
    at
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.init(OpenConglomerate.java)
    at org.apache.derby.impl.store.access.heap.Heap.open(Heap.java)
    at
org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java)
    at
org.apache.derby.impl.store.access.RAMTransaction.openConglomerate(RAMTransaction.java)
    at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getDescriptorViaIndex(DataDictionaryImpl.java)
    at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.locateSchemaRow(DataDictionaryImpl.java)
    at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSchemaDescriptor(DataDictionaryImpl.java)
    at
org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(QueryTreeNode.java)
    at
org.apache.derby.impl.sql.compile.QueryTreeNode.getSchemaDescriptor(QueryTreeNode.java)
    at
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(FromBaseTable.java)
    at
org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(FromBaseTable.java)
    at org.apache.derby.impl.sql.compile.FromList.bindTables(FromList.java)
    at
org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(SelectNode.java)
    at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(DMLStatementNode.java)
    at
org.apache.derby.impl.sql.compile.DMLStatementNode.bind(DMLStatementNode.java)
    at
org.apache.derby.impl.sql.compile.ReadCursorNode.bind(ReadCursorNode.java)
    at org.apache.derby.impl.sql.compile.CursorNode.bind(CursorNode.java)
    at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java)
    at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java)
    at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java)
    at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java)
    at
org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(EmbedStatement.java)
    at AutoCommitTest.query(AutoCommitTest.java:223)
    at AutoCommitTest.test(AutoCommitTest.java:72)
    at AutoCommitTest.<init>(AutoCommitTest.java:40)
    at AutoCommitTest.main(AutoCommitTest.java:235)
java.lang.NullPointerException
    at AutoCommitTest.test(AutoCommitTest.java:75)
    at AutoCommitTest.<init>(AutoCommitTest.java:40)
    at AutoCommitTest.main(AutoCommitTest.java:235)



The derby.log file looked like this:

2005-05-13 10:21:36.593 GMT:
 Booting Derby version IBM Corp. - Apache Derby - 10.0.2.0 - (46005):
instance c013800d-0103-d592-c983-000000197520
on database directory D:\eclipse\workspace\DerbyAutoCommitTest\testdb

Database Class Loader started - derby.database.classpath=''
2005-05-13 10:36:18.890 GMT Thread[main,5,main] (XID = 101522),
(SESSIONID = 0), (DATABASE = testdb), (DRDAID = null), Cleanup action
starting
2005-05-13 10:36:18.890 GMT Thread[main,5,main] (XID = 101522),
(SESSIONID = 0), (DATABASE = testdb), (DRDAID = null), Failed Statement
is: INSERT INTO test(id,
s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13,s14,s15,i1,i2,i3,i4,i5,i6)
VALUES(13465,'JYC&''>2_vE\wW9yBhpN=R<8$<3;e\xz.X9&-ozwCW&=kF"8dfn','Id7j}\Mu''Ho8W)q<SE7vNMgGU|S{^-/F]@g;k8kZ~+cdOK;^zu','CyrL${s+}w(dyfw&#<q9>ts\vU|z2u
B7oB[wx[U6k%O*dq51H','5[%x.fzodgnx!|i|X7kHFP3#9,}d#JmZz@`)ZwuFyC^rZe}8ZH','tz6Vj%2\opWt_Bwt6gyR#~.U*0FYQ70erT?yM.~W3?Ce;-jds<','D"lP(,oZK$Y"E[OKY"8=,1%br."pwon\6]=e0h.{DL[m`yrE]U','uWewV{=YzH;YC[0&Ie;fJ<L)`d_pl2t>NPO"gb[M)ID-pDiaFP','m[MfF]%}]}d3m81m+@^t
Hh%*Y{5k|n==m_P*KRD3~^%C1;9II','8~XK''kZZyJ<}L?=3Kd#J`@}L!3i{Sv6qAh@9VVF`t}z<<zLeEg','OP$xd(*S06mH\F_f|&#v{_5e%8=HJhJ`ee"df*V=^a~j&$?;ZY','J|jW9h^;QuWU$Zg05G@:X]\]"wnG:m(s|%<''lAEW#?XbA^,7oV','fl)P#I_%G/;NceIv]AeWY
%B$?5XFijw),_Ts+mpE#dRRYO@Wp','$(xHn"^LA-D\2^pN7}+coue8ySFlHnF7T;<!oxS;3{C,(M--iJ','O#dZ,=_s5uY6jMo\N''z]D-&1w!dkrok!exZ5Oe]^-xXdTbLBsv','Fp@p~{nu/=RUQF]#cpU}Qo
Ou]gM)Ir:(tx[`.?b<vY_/ct9F+',1492672792,-43909196,808521047,235300249,-1291212720,-867862481)
java.lang.OutOfMemoryError
Cleanup action completed


------------------------------------------------------

If this is run without the autocommit being turned off, there is no problem.

Interestingly, test2() which has the id query removed, causes no problem.


Cheers,

Chris



>It looks like a bug, but a reproducible case would be needed to start
>trying to fix it. Is there a chance you could post code that shows this
>behaviour?
>
>There may also be an earlier error the is the real problem, try checking
>in your derby.log file.
>
>Thanks,
>Dan.
>
>
>
>  
>


AutoCommitTest.java (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

Daniel John Debrunner
Chris wrote:

> Hi,
>
> to demonstrate the problem, i've written some code which approximates
> the code i'm using.  (attached)
>
> I'm running Java 1.4.2 with the JVM parameter -Xmx64m.
>
> If you run the test() method,  the memory used by the JVM  rises to
> above 150mb until the following exception happens:

Thanks for the test case.

I noticed that you are not using PreparedStatements for the query or the
 insert. Using PreparedStatements will make your program much faster, as
the statement does not need to be recompiled every time. This is true
for all JDBC drivers.

See

http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705

E.g.

PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
id = ?");
psq.setInt(1, id);
ResultSet rs = psq.executeQuery();


Your code uses a new Statement object to execute every statement, which
can also be avoided since Statement objects are reuseable. Though I
would recommend PreparedStatements.

Also your code never closes the Statement or ResultSet objects it
creates, this may be contributing to the problem.

Applying the above techniques may allow you to progress past this issue,
though it should still be seen as a bug.

Dan.



Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

Chris-5
Hi Dan,

thanks very much for the advice.   Recyling the same statement significantly reduces the memory problem, and using prepared statements is *really* speeding up my code.

Cheers!

Chris

Daniel John Debrunner wrote:
Chris wrote:

  
Hi,

to demonstrate the problem, i've written some code which approximates
the code i'm using.  (attached)

I'm running Java 1.4.2 with the JVM parameter -Xmx64m.

If you run the test() method,  the memory used by the JVM  rises to
above 150mb until the following exception happens:
    

Thanks for the test case.

I noticed that you are not using PreparedStatements for the query or the
 insert. Using PreparedStatements will make your program much faster, as
the statement does not need to be recompiled every time. This is true
for all JDBC drivers.

See

http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705

E.g.

PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
id = ?");
psq.setInt(1, id);
ResultSet rs = psq.executeQuery();


Your code uses a new Statement object to execute every statement, which
can also be avoided since Statement objects are reuseable. Though I
would recommend PreparedStatements.

Also your code never closes the Statement or ResultSet objects it
creates, this may be contributing to the problem.

Applying the above techniques may allow you to progress past this issue,
though it should still be seen as a bug.

Dan.




  

Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

mikem_app
In reply to this post by Daniel John Debrunner
Does anyone know if it is expected in derby to accumulate memory in
a single transaction if you don't close statements or resultSets?
I am pretty sure not closing ResultSets is a problem, I am not sure
about Statements.

Daniel John Debrunner wrote:

> Chris wrote:
>
>
>>Hi,
>>
>>to demonstrate the problem, i've written some code which approximates
>>the code i'm using.  (attached)
>>
>>I'm running Java 1.4.2 with the JVM parameter -Xmx64m.
>>
>>If you run the test() method,  the memory used by the JVM  rises to
>>above 150mb until the following exception happens:
>
>
> Thanks for the test case.
>
> I noticed that you are not using PreparedStatements for the query or the
>  insert. Using PreparedStatements will make your program much faster, as
> the statement does not need to be recompiled every time. This is true
> for all JDBC drivers.
>
> See
>
> http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705
>
> E.g.
>
> PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
> id = ?");
> psq.setInt(1, id);
> ResultSet rs = psq.executeQuery();
>
>
> Your code uses a new Statement object to execute every statement, which
> can also be avoided since Statement objects are reuseable. Though I
> would recommend PreparedStatements.
>
> Also your code never closes the Statement or ResultSet objects it
> creates, this may be contributing to the problem.
>
> Applying the above techniques may allow you to progress past this issue,
> though it should still be seen as a bug.
>
> Dan.
>
>
>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

David Van Couvering
I thought there was a bug where not closing statements accumulated
memory, but it's just a rumor for me, not a direct experience.

David

Mike Matrigali wrote:

> Does anyone know if it is expected in derby to accumulate memory in
> a single transaction if you don't close statements or resultSets?
> I am pretty sure not closing ResultSets is a problem, I am not sure
> about Statements.
>
> Daniel John Debrunner wrote:
>
>> Chris wrote:
>>
>>
>>> Hi,
>>>
>>> to demonstrate the problem, i've written some code which approximates
>>> the code i'm using.  (attached)
>>>
>>> I'm running Java 1.4.2 with the JVM parameter -Xmx64m.
>>>
>>> If you run the test() method,  the memory used by the JVM  rises to
>>> above 150mb until the following exception happens:
>>
>>
>>
>> Thanks for the test case.
>>
>> I noticed that you are not using PreparedStatements for the query or the
>>  insert. Using PreparedStatements will make your program much faster, as
>> the statement does not need to be recompiled every time. This is true
>> for all JDBC drivers.
>>
>> See
>>
>> http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705 
>>
>>
>> E.g.
>>
>> PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
>> id = ?");
>> psq.setInt(1, id);
>> ResultSet rs = psq.executeQuery();
>>
>>
>> Your code uses a new Statement object to execute every statement, which
>> can also be avoided since Statement objects are reuseable. Though I
>> would recommend PreparedStatements.
>>
>> Also your code never closes the Statement or ResultSet objects it
>> creates, this may be contributing to the problem.
>>
>> Applying the above techniques may allow you to progress past this issue,
>> though it should still be seen as a bug.
>>
>> Dan.
>>
>>
>>
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: error when turning off auto commit

Satheesh Bandaram
In reply to this post by mikem_app
If you keep reusing Statement object, I don't believe there is a leak.
If one keeps creating a new Statement for every query executed, there
seems to be a "leak". Derby client also behaves the same way as embedded
driver here.

Seems like a very bad practice...

Satheesh

Mike Matrigali wrote:

> Does anyone know if it is expected in derby to accumulate memory in
> a single transaction if you don't close statements or resultSets?
> I am pretty sure not closing ResultSets is a problem, I am not sure
> about Statements.
>
> Daniel John Debrunner wrote:
>
>> Chris wrote:
>>
>>
>>> Hi,
>>>
>>> to demonstrate the problem, i've written some code which approximates
>>> the code i'm using.  (attached)
>>>
>>> I'm running Java 1.4.2 with the JVM parameter -Xmx64m.
>>>
>>> If you run the test() method,  the memory used by the JVM  rises to
>>> above 150mb until the following exception happens:
>>
>>
>>
>> Thanks for the test case.
>>
>> I noticed that you are not using PreparedStatements for the query or the
>>  insert. Using PreparedStatements will make your program much faster, as
>> the statement does not need to be recompiled every time. This is true
>> for all JDBC drivers.
>>
>> See
>>
>> http://incubator.apache.org/derby/manuals/tuning/perf21.html#HDRSII-PERF-18705
>>
>>
>> E.g.
>>
>> PreparedStatement psq = conn.prepareStatement("SELECT id FROM test WHERE
>> id = ?");
>> psq.setInt(1, id);
>> ResultSet rs = psq.executeQuery();
>>
>>
>> Your code uses a new Statement object to execute every statement, which
>> can also be avoided since Statement objects are reuseable. Though I
>> would recommend PreparedStatements.
>>
>> Also your code never closes the Statement or ResultSet objects it
>> creates, this may be contributing to the problem.
>>
>> Applying the above techniques may allow you to progress past this issue,
>> though it should still be seen as a bug.
>>
>> Dan.
>>
>>
>>
>>
>>
>
>
>