This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

java.lang.OutOfMemoryError: Java heap space in DB query. Thanks for help in advance

skylei(sky)

I use Spring JdbcTemplate to do the following query and use RowMapper to get
the ResultSet. However, the Jboss server generate java.lang.
OutOfMemoryError some time.

SELECT *
FROM transactions
WHERE transaction_id = (SELECT refenence_tran_id FROM transactions WHERE
transaction_id = '1234')
or transaction_id = (SELECT original_transaction_id FROM transactions WHERE
transaction_id = '1234')
or original_transaction_id = '1234' or refenence_tran_id = '1234' order by
transaction_id

Notes:
1. The table transactions is a very big table
2. I have set all index for columns transaction_id, refenence_tran_id and
original_transaction_id.
3. The query usually return 2-3 result rows
4. Seems the java.lang.OutOfMemoryError happened in high load

My concern is why there is memory error for this query with just quite a few
result sets. Any wrong with my query syntax above? Thank you for your help
in advance.

(#5345139@0)
Last Updated: 2009-6-15
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / java.lang.OutOfMemoryError: Java heap space in DB query. Thanks for help in advance -skylei(sky); 2009-6-15 {936} (#5345139@0)
I use Spring JdbcTemplate to do the following query and use RowMapper to get
the ResultSet. However, the Jboss server generate java.lang.
OutOfMemoryError some time.

SELECT *
FROM transactions
WHERE transaction_id = (SELECT refenence_tran_id FROM transactions WHERE
transaction_id = '1234')
or transaction_id = (SELECT original_transaction_id FROM transactions WHERE
transaction_id = '1234')
or original_transaction_id = '1234' or refenence_tran_id = '1234' order by
transaction_id

Notes:
1. The table transactions is a very big table
2. I have set all index for columns transaction_id, refenence_tran_id and
original_transaction_id.
3. The query usually return 2-3 result rows
4. Seems the java.lang.OutOfMemoryError happened in high load

My concern is why there is memory error for this query with just quite a few
result sets. Any wrong with my query syntax above? Thank you for your help
in advance.

Please click into it for the whole error logs -skylei(sky); 2009-6-15 {852} (#5345141@0)
INFO [org.springframework.jdbc.support.SQLErrorCodesFactory] SQLErrorCodes
loaded: [DB2, HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]

ERROR [STDERR] com.active.dao.DAOException: An exception occurred during
database access: PreparedStatementCallback; uncategorized SQLException for
SQL [SELECT * FROM transactions WHERE transaction_id = (SELECT refenence_
tran_id FROM transactions WHERE transaction_id = ?) or transaction_id = (
SELECT original_transaction_id FROM transactions WHERE transaction_id = ?)
or original_transaction_id = ? or refenence_tran_id = ? order by transaction
_id]; SQL state [null]; error code [0]; Error; - nested throwable: (java.
lang.OutOfMemoryError: Java heap space); nested exception is org.jboss.util.
NestedSQLException: Error; - nested throwable: (java.lang.OutOfMemoryError:
Java heap space)

它说heap用完了,你就给他增加点呗,现在内存这么便宜. Java sucks! -oceanwater(灌水日月长); 2009-6-15 (#5345203@0)

Well, it's nothing to do with Java itself. There is a memory leak somewhere in the code. I am not too sure what he wants to do in his query. But he can always use Heap Analyzer to find out which object consumes the most memory. -rosella(残荷听雨); 2009-6-15 (#5345343@0)

How do you know there is a memory leak somewhere? what if his code really needs that much memory? If double the heap size solved the problem, why bother analyze the memory usage? -oceanwater(灌水日月长); 2009-6-15 (#5345514@0)

我的SQL语句是要返回与给出transactionID相关的transaction数据。根据理论返回的数据量很少，一般在2-3条。请问我的SQL语句有问题吗？我最近才加这个SQL, 运行了一个月左右，这几天出现这个问题。导致Jboss停顿了。 -skylei(sky); 2009-6-15 (#5345731@0)

You can run the same SQL in database to verify if result set has only 2-3 rows. If that is true, it is most likely caused by memory leak, in which case the SQL query just happened to hit the heap limit. -majorhomedepot(马甲后的炮); 2009-6-16 {82} (#5348574@0)
I think you can do a heap dump, but you might want to google a good analysis tool.

还有就是transactions 表的数据非常大，与这个有关吗？我认为好像没有关系，因为所有的query条件column都有索引。请问各位有不同意见吗？ -skylei(sky); 2009-6-16 (#5345740@0)

If you are not sure about your sql, do some test in database. Don't mess it up with your code. This error really depends on how your application works. If you are not sure, ask someone around you to take a look. There is really no simple answer. -oceanwater(灌水日月长); 2009-6-16 (#5346239@0)

try this: -sxffff(Felix); 2009-6-16 {240} (#5348028@0)
Select * from transactions t1, transactions t2
Where ((t1.transaction_id=t2.refenence_tran_id or t1.transaction_id=t2.original_transaction_id ) and t2. transaction_id=1234)
or t1.original_transaction_id =1234 or t1.refenence_tran_id =1234

@Calgary

java.lang.OutOfMemoryError: Java heap space in DB query. Thanks for help in advance

Replies, comments and Discussions:

More Topics