db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Segel" <mse...@segel.com>
Subject RE: SQLException: The heap container with container id Container(-1, 1157060695837) is closed.
Date Wed, 13 Sep 2006 14:01:23 GMT
Alan,

The first suggestion is to comment out all of your finally blocks.
You really don't need them and if you're hitting one prematurely, you're
going to close down your result set.

The next issue would be to take the queries and run them in IJ outside of
your application. This would help identify if the problem is within the data
set or your query and not your application.

Again the problem sounds path dependent (meaning data dependent) so if you
can identify the data which causes the exception to be thrown you can then
find the path the program is taking.

On a side note: 9 times out of 10, the error is due to the programmer, so
lets focus on your application first.

I'd also suggest that you look at your queries. Sometimes is not that
efficient to go 3rd normal form. (You have to know when to break the rules.)

Also you may want to consider, for performance, you may want to break up
your database transaction. Its sometimes faster to grab a central record,
then use prepared statements to grab the fact table's data based on the
current record.

I'm a strong believer in the KISS methodology. ;-)

HTH

-Mike


> -----Original Message-----
> From: BALDWIN, ALAN J [AG-Contractor/1000]
> [mailto:alan.j.baldwin@monsanto.com]
> Sent: Tuesday, September 12, 2006 4:31 PM
> To: 'Derby Discussion'
> Subject: RE: SQLException: The heap container with container id
> Container(-1, 1157060695837) is closed.
> 
> Michael,
> Thanks for the responses.  Here is what I know so far:
> 
> ** There is no other finally block that would close that result set.  The
> only operations that happen on that result set (other than the finally
> block
> you saw) are rs.getString(), rs.getInt(), etc...
> 
> ** I am quite certain that this is not your run-of-the mill jdbc error.  I
> would not be posting here if I thought it were.
> 
> ** I can switch the order of the inner join clauses and reproduce this on
> datasets that previously had no problem.  For example:
> 
> //This returns data:
> SELECT * FROM DeliveryNotification dn
> INNER JOIN DealerTransaction dt ON dn.InventoryTransactionID =
> dt.TransactionId
> INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
> dn.SeedYear
> //more joins here...
> 
> //This throws the exception:
> SELECT * FROM DealerTransaction dt
> INNER JOIN DeliveryNotification dn ON dn.InventoryTransactionID =
> dt.TransactionId
> INNER JOIN Product p ON p.ProductUPC = dn.ProductUPC AND p.SeedYear =
> dn.SeedYear
> //more joins here...
> 
> We have one DealerTransaction row to many DeliveryNotification rows.  The
> joins following those three tables are mostly just lookup data (status
> tables and such... our database is very normalized), and they remain
> unchanged in both versions of this query.
> 
> To me, this smells like something out of my control (jdbc driver, database
> engine?) is running out of resources.  This seems to be more frequent the
> larger the dataset gets.  This dataset for example, contains about 250
> DealerTransaction rows, ~1000 DeliveryNotification rows, and ~5000
> products.
> Is this large by Derby standards?
> 
> Regards,
> -Alan-
> 
> 
> 
> -----Original Message-----
> From: Michael Segel [mailto:msegel@segel.com]
> Sent: Friday, September 01, 2006 9:20 AM
> To: 'Derby Discussion'
> Subject: RE: SQLException: The heap container with container id
> Container(-1, 1157060695837) is closed.
> 
> 
> Mike,
> 
> I doubt that this has anything to do with autocommit.
> 
> The comment that this is data specific is an indication that the result
> set
> is being closed due to the path taken during execution.
> 
> The interesting thing is that Alan uses a finally clause as part of his
> try/catch block. I'm going out on a limb and betting that he uses the
> finally block on all of his try/catch blocks.
> 
> My guess is that when he calls process transaction, he's hitting the
> finally
> block and that's closing the result set.
> 
> Of course this is only a guess since:
> 1) I don't see that section of code.
> 2) He's using a finally block in the section he's posted.
> 3) This is a data dependent issue. Since he's hitting this loop if the
> transaction is null.
> 4) Another guess is that the transaction will be null if the id is null,
> therefore it's a simple check of the data to see if this is what is
> causing
> the case.
> 
> 
> But hey! What do I know? ;-)
> 
> -G
> 
> > -----Original Message-----
> > From: Mike Matrigali [mailto:mikem_app@sbcglobal.net]
> > Sent: Thursday, August 31, 2006 6:29 PM
> > To: Derby Discussion
> > Subject: Re: SQLException: The heap container with container id
> > Container(-1, 1157060695837) is closed.
> >
> > are you in autocommit=true or not?
> > What does addLineItem do? ie. does it execute any queries?
> >
> > This certainly has the feel of a resultset being closed in the middle by
> > some other statement.  Usually this is seen with autocommit=true and
> > "non held" cursors.
> > in the "next" loop there is a call to some other code that executes a
> > query and when that statement ends, it commits the transaction of the
> > calling select loop, closing the non-held cursors.
> >
> > When such a situation occurs, it can be data dependent when the
> > error is encountered.  Depending on isolation level and query plans
> > a number of rows may be cached in memory before returning the 1st
> > row to the select loop.  If possible derby streams rows to the select
> > loop so that a normal situation is for there to be a number of results
> > waiting to be drained by the select loop, but also at some point the
> > act of draining another row may move the pipeline along causing raw
> > rows to be read from disk and processed to create the next "batch" of
> > rows for the select loop.
> >
> > BALDWIN, ALAN J [AG-Contractor/1000] wrote:
> > > The weird thing with this is that it seems to be data dependent.  This
> > > code has been in use (unchanged) for some time now, and has only today
> > > started breaking with a specific dataset, which is around 60 rows
> joined
> > > across 5-6 tables.
> > >
> > >
> > >
> > > -Alan Baldwin-
> > >
> > >
> > >
> > > -----Original Message-----
> > > *From:* BALDWIN, ALAN J [AG-Contractor/1000]
> > > *Sent:* Thursday, August 31, 2006 5:13 PM
> > > *To:* 'Derby Discussion'
> > > *Subject:* RE: SQLException: The heap container with container id
> > > Container(-1, 1157060695837) is closed.
> > >
> > >
> > >
> > > Michael,
> > >
> > > Here is the java code block.
> > >
> > >
> > >
> > > Neither the processTransactionResultSetRow() nor the
> > > lineItemDao.processResultSetRow() methods close the resultset.  In
> fact,
> > > they are included below.
> > >
> > >
> > >
> > >
> > >
> > >       rs = ps.executeQuery();
> > >
> > >
> > >
> > >       Map transactionSet = new HashMap();
> > >
> > >       while (rs.next()) {                         //BOMBS HERE ON
> SECOND
> > > LOOP
> > >
> > >         Long id = new Long(rs.getLong("TransactionId"));
> > >
> > >         DealerTransaction transaction = (DealerTransaction)
> > > transactionSet.get(id);
> > >
> > >         if (transaction == null) {
> > >
> > >           transaction = processTransactionResultSetRow(rs);
> > >
> > >           transactionSet.put(id, transaction);
> > >
> > >           dealerTransactions.add(transaction);
> > >
> > >         }
> > >
> > >         transaction.addLineItem(lineItemDao.processResultSetRow(rs,
> > > connection));
> > >
> > >       }
> > >
> > >     } finally {
> > >
> > >       DBHelper.closeResources(ps, rs);
> > >
> > >     }
> > >
> > >
> > >
> > >
> > >
> > > =====================
> > >
> > > private DealerTransaction processTransactionResultSetRow(ResultSet rs)
> > > throws SQLException {
> > >
> > >     DealerTransaction dealerTransaction = new
> > > DealerTransaction(rs.getLong("TransactionId"),
> > >
> > >         rs.getDate("TransactionDate"), rs.getInt("SeedYear"),
> > > rs.getString("ShipToName"), rs.getString("ShipFromName"));
> > >
> > >
> > >
> > >     dealerTransaction.setPoNumber(rs.getString("PONumber"));
> > >
> > >     dealerTransaction.setBolNumber(rs.getInt("BillOfLadingNumber"));
> > >
> > >
> > >
> >
> dealerTransaction.setTransactionToFrom(rs.getString("TransactionToFrom"));
> > >
> > >     dealerTransaction.setSoldToId(rs.getString("SoldToID"));
> > >
> > >     dealerTransaction.setSoldToIdType(rs.getString("SoldToIdType"));
> > >
> > >     dealerTransaction.setPlantCode(rs.getString("PlantCode"));
> > >
> > >     dealerTransaction.setPlantName(rs.getString("PlantName"));
> > >
> > >
> > >
> >
> dealerTransaction.setTransactionNumber(rs.getString("TransactionNumber"));
> > >
> > >     dealerTransaction.setShipToId(rs.getString("ShipToID"));
> > >
> > >     dealerTransaction.setShipToIdType(rs.getString("ShipToIdType"));
> > >
> > >     dealerTransaction.setSoldToName(rs.getString("SoldToName"));
> > >
> > >
> > >
> > >     return dealerTransaction;
> > >
> > >   }
> > >
> > >
> > >
> > > ====================
> > >
> > > DealerTransactionDetail processResultSetRow(ResultSet rs, Connection
> > > connection) throws SQLException {
> > >
> > >     Dealer affiliate = dealerDao.loadDealer(connection,
> > > rs.getString("AffiliateIRDAccountId"));
> > >
> > >     Product product = productDao.loadProduct(connection,
> > > rs.getString("ProductUPC"), rs.getInt("SeedYear"));
> > >
> > >
> > >
> > >     DealerTransactionDetail dealerTransactionDetail = new
> > > DealerTransactionDetail(rs.getInt("ItemNumber"), rs
> > >
> > >             .getInt("Quantity"), DBHelper.getYesNo(rs, "IsVoided"),
> > > product, rs.getString("TransactionTypeName"), rs
> > >
> > >             .getString("StatusName"), rs.getInt("SeedYear"));
> > >
> > >
> > >
> > >
> > dealerTransactionDetail.setOriginalUnits(rs.getFloat("SapQuantity"));
> > >
> > >
> > >
> >
> dealerTransactionDetail.setDamagedQuantity(rs.getFloat("DamagedQuantity"))
> > ;
> > >
> > >     dealerTransactionDetail.setLotNumber(rs.getString("LotNumber"));
> > >
> > >     dealerTransactionDetail.setAffiliateDealer(affiliate);
> > >
> > >     dealerTransactionDetail.setLocation(rs.getString("Location"));
> > >
> > >
> dealerTransactionDetail.setComment(rs.getString("DeliveryComment"));
> > >
> > >     dealerTransactionDetail.setSeedSize(rs.getString("SeedSize"));
> > >
> > >     dealerTransactionDetail.setUserDate(rs.getDate("UserDate"));
> > >
> > >
> dealerTransactionDetail.setProductSapId(rs.getLong("ProductSapId"));
> > >
> > >
> dealerTransactionDetail.setSapUom(rs.getString("SapUnitOfMeasure"));
> > >
> > >     return dealerTransactionDetail;
> > >
> > >   }
> > >
> > >
> > >
> > > Thanks!
> > >
> > >
> > >
> > > -Alan Baldwin-
> > >
> > >
> > >
> > >
> > >
> > > -----Original Message-----
> > > *From:* Michael Segel [mailto:msegel@segel.com] *On Behalf Of
> > > *derby@segel.com
> > > *Sent:* Thursday, August 31, 2006 5:05 PM
> > > *To:* 'Derby Discussion'
> > > *Subject:* RE: SQLException: The heap container with container id
> > > Container(-1, 1157060695837) is closed.
> > >
> > >
> > >
> > > Can you provide some code of the loop?
> > >
> > > It sounds like you're closing out the result set.
> > >
> > > But hey! What do I know?
> > >
> > >
> > >
> > > ----------------------------------------------------------------------
> --
> > >
> > > *From:* BALDWIN, ALAN J [AG-Contractor/1000]
> > > [mailto:alan.j.baldwin@monsanto.com]
> > > *Sent:* Thursday, August 31, 2006 5:01 PM
> > > *To:* derby-user@db.apache.org
> > > *Subject:* SQLException: The heap container with container id
> > > Container(-1, 1157060695837) is closed.
> > >
> > >
> > >
> > > Hello all,
> > >
> > > I'm running a query from java that throws the exception in the
> subject.
> > > The code that fails is when calling rs.next() **on the second
> iteration
> > > only**.  I can't seem to find anything on the web regarding this
> > > error.   Could someone point me in the right direction?
> > >
> > > Regards,
> > >
> > > Alan Baldwin
> > >
> > > //This e-mail message may contain privileged and/or confidential
> > > information, and is intended to be received only by persons entitled
> to
> > > receive such information. If you have received this e-mail in error,
> > > please notify the sender immediately. Please delete it and all
> > > attachments from any servers, hard drives or any other media. Other
> use
> > > of this e-mail by you is strictly prohibited.//
> > >
> > > //All e-mails and attachments sent and received are subject to
> > > monitoring, reading and archival by Monsanto. The recipient of this
> > > e-mail is solely responsible for checking for the presence of
> "Viruses"
> > > or other "Malware". Monsanto accepts no liability for any damage
> caused
> > > by any such code transmitted by or accompanying this e-mail or any
> > > attachment.//
> > >
> > > //This e-mail message may contain privileged and/or confidential
> > > information, and is intended to be received only by persons entitled
> to
> > > receive such information. If you have received this e-mail in error,
> > > please notify the sender immediately. Please delete it and all
> > > attachments from any servers, hard drives or any other media. Other
> use
> > > of this e-mail by you is strictly prohibited.//
> > >
> > > //All e-mails and attachments sent and received are subject to
> > > monitoring, reading and archival by Monsanto. The recipient of this
> > > e-mail is solely responsible for checking for the presence of
> "Viruses"
> > > or other "Malware". Monsanto accepts no liability for any damage
> caused
> > > by any such code transmitted by or accompanying this e-mail or any
> > > attachment.//
> > >
> > > /This e-mail message may contain privileged and/or confidential
> > > information, and is intended to be received only by persons entitled
> to
> > > receive such information. If you have received this e-mail in error,
> > > please notify the sender immediately. Please delete it and all
> > > attachments from any servers, hard drives or any other media. Other
> use
> > > of this e-mail by you is strictly prohibited./
> > >
> > > /All e-mails and attachments sent and received are subject to
> > > monitoring, reading and archival by Monsanto. The recipient of this
> > > e-mail is solely responsible for checking for the presence of
> "Viruses"
> > > or other "Malware". Monsanto accepts no liability for any damage
> caused
> > > by any such code transmitted by or accompanying this e-mail or any
> > > attachment./
> > >
> 
> 
> 
> 
> --------------------------------------------------------------------------
> -------------------------------
> This e-mail message may contain privileged and/or confidential
> information, and is intended to be received only by persons entitled to
> receive such information. If you have received this e-mail in error,
> please notify the sender immediately. Please delete it and all attachments
> from any servers, hard drives or any other media. Other use of this e-mail
> by you is strictly prohibited.
> 
> 
> All e-mails and attachments sent and received are subject to monitoring,
> reading and archival by Monsanto. The recipient of this e-mail is solely
> responsible for checking for the presence of "Viruses" or other "Malware".
> Monsanto accepts no liability for any damage caused by any such code
> transmitted by or accompanying this e-mail or any attachment.
> --------------------------------------------------------------------------
> -------------------------------




Mime
View raw message