Hi, This sounds like a bug to me... Do you have a simple, reproducible test case? That would help a lot to resolve the issue.
Regards, Thomas On Wed, Dec 19, 2012 at 5:17 PM, Josh Sanderson <[email protected]>wrote: > Getting internal exception when trying to delete rows. The program ran for > a number of days without any issue before the error started occurring. The > service inserts a few records every second or so, runs a query on the > records every 60 seconds or so, and truncates records older than a week > every 60 minutes. (The inserts/queries/truncation happen simultaneously at > times.) > > It uses connection pool in multi-threaded environment. There should only > be ~3 threads/connections accessing database concurrently at any given > time, but one of the threads is often running a lengthy query (either > SELECT or DELETE). We are using MULTI_THREADED=1 option to avoid serious > thread blocking issues. Without that option, H2 seemed > basically unusable to us. I know MULTI_THREADED is still considered > experimental. Any ideas to resolve appreciated, or let me know if I can > provide more info. I would try writing a test case, but again, things > were working fine for days before this issue started occurring. > > Database created/running with v1.3.168 on a 32-bit linux machine using > Java 6. Connection string: > > - jdbc:h2:~/.dbdir/my-db;LOCK_TIMEOUT=10000;MULTI_THREADED=1 > > The trace file shows this initial error: > > 12-19 00:04:19 jdbc[3]: exception > org.h2.jdbc.JdbcSQLException: General error: "java.lang.RuntimeException: > Undo entry not written"; SQL statement: > DELETE FROM DataPoint WHERE endTimestamp < ? [50000-168] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) > at org.h2.message.DbException.get(DbException.java:158) > at org.h2.message.DbException.convert(DbException.java:281) > at org.h2.table.RegularTable.removeRow(RegularTable.java:379) > at org.h2.command.dml.Delete.update(Delete.java:93) > at org.h2.command.CommandContainer.update(CommandContainer.java:75) > at org.h2.command.Command.executeUpdate(Command.java:230) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) > at > org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) > ...etc... > Caused by: java.lang.RuntimeException: Undo entry not written > at org.h2.message.DbException.throwInternalError(DbException.java:228) > at org.h2.store.PageLog.addUndo(PageLog.java:486) > at org.h2.store.PageStore.update(PageStore.java:1055) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:362) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241) > at org.h2.table.RegularTable.removeRow(RegularTable.java:361) > ... 28 more > > That was then followed by: > > 12-19 01:04:29 jdbc[4]: exception > org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from > index "PUBLIC.IDX_RECEIVERUPLOADTS_DESC: ( /* key:1955899 */ 1955899, > 17.666666666666668, TIMESTAMP '2012-12-18 15:48:44.565', 'sim-josh-001', 1, > 'APJOSH1', '2', TIMESTAMP '2012-12-18 15:48:39.565', 'UPLOADED', TIMESTAMP > '2012-12-18 15:48:50.393')"; SQL statement: > DELETE FROM DataPoint WHERE endTimestamp < ? [90112-168] > at > org.h2.message.DbException.getJdbcSQLException(DbException.java:329) > at org.h2.message.DbException.get(DbException.java:169) > at org.h2.message.DbException.get(DbException.java:146) > at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:225) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241) > at org.h2.table.RegularTable.removeRow(RegularTable.java:361) > at org.h2.command.dml.Delete.update(Delete.java:93) > at org.h2.command.CommandContainer.update(CommandContainer.java:75) > at org.h2.command.Command.executeUpdate(Command.java:230) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) > > After those initial exceptions, a repeating pattern every time the program > tries to delete records would occur. Table lock timeouts would occur (I > assume because the delete operation is running), with an eventual exception > "Row not found while deleting". Excerpt from trace file: > > 12-19 10:11:33 jdbc[4]: exception > org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DATAPOINT"; > SQL statement: > SELECT DISTINCT(receiverIdent) FROM DataPoint WHERE uploadStatus=? ORDER > BY receiverIdent ASC [50200-168] > > 12-19 10:11:35 jdbc[3]: exception > org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DATAPOINT"; > SQL statement: > insert into DataPoint (id, dataValue, endTimestamp, nodeIdent, nodeType, > receiverIdent, sensorInputIdent, startTimestamp, uploadStatus, > uploadStatusUpdated) values (null, ?, ?, ?, ?, ?, ?, ?, ?, ?) [50200-168] > > 12-19 10:11:39 jdbc[5]: exception > org.h2.jdbc.JdbcSQLException: Row not found when trying to delete from > index "PUBLIC.IDX_RECEIVERUPLOADTS_DESC: ( /* key:1955899 */ 1955899, > 17.666666666666668, TIMESTAMP '2012- > 12-18 15:48:44.565', 'sim-josh-001', 1, 'APJOSH1', '2', TIMESTAMP > '2012-12-18 15:48:39.565', 'UPLOADED', TIMESTAMP '2012-12-18 > 15:48:50.393')"; SQL statement: > DELETE FROM DataPoint WHERE endTimestamp < ? [90112-168] > at > org.h2.message.DbException.getJdbcSQLException(DbException.java:329) > at org.h2.message.DbException.get(DbException.java:169) > at org.h2.message.DbException.get(DbException.java:146) > at org.h2.index.PageBtreeLeaf.remove(PageBtreeLeaf.java:225) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:324) > at org.h2.index.PageBtreeIndex.remove(PageBtreeIndex.java:241) > at org.h2.table.RegularTable.removeRow(RegularTable.java:361) > at org.h2.command.dml.Delete.update(Delete.java:93) > at org.h2.command.CommandContainer.update(CommandContainer.java:75) > at org.h2.command.Command.executeUpdate(Command.java:230) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) > at > org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) > at > org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) > ...etc... > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/h2-database/-/rkbaekWMUw4J. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
