The only way I've managed to prevent concurrent writes in the same row with MVCC on, is reducing the CPU load in every massive data load/ export. It's about 8 times slower than before, but now it's reliable. I suppose I should move to a non java database due to my low performance hardware requirements.
On Feb 22, 12:46 pm, anavarrog <anavar...@gmail.com> wrote: > Sorry, in last entry I was referring to the error: > > org.h2.jdbc.JdbcSQLException: Statement was canceled or the session > timed out; > > when I said that the query: > > delete from sym_data_event where batch_id not in (select batch_id from > sym_outgoing_batch where batch_id between 6 and 106 and status != > 'OK') and batched = 1 and batch_id between 6 and 106 > > was causing it. I've solved it changing the configuration of > SymmetricDS that was cancelling the query execution when it last more > than 5 minutes. So, the problem left is the first one, caused by an > update in SYM_DATA_EVENT. > > On Feb 22, 11:07 am, anavarrog <anavar...@gmail.com> wrote: > > > > > > > > > The query that is causing these errors is: > > > delete from sym_data_event where batch_id not in (select batch_id from > > sym_outgoing_batch where batch_id between 6 and 106 and status != > > 'OK') and batched = 1 and batch_id between 6 and 106 > > > and sym_data_event is a table containing 2810225 entries. I've > > launched that query in a quad core, 4GiB RAM machine and it finishes > > without errors after ~ 15 minutes (881968 ms), deleting 101428 entries > > from sym_data_event. It looks like the netbooks I'm using to run the > > application (Atom N455 dual core 1.66GHz processors, 2GiB RAM) cannot > > afford that kind of effort. I'll try to reduce the number of elements > > to delete in every transaction hoping it will reduce the overhead. > > > On Feb 21, 5:33 pm, anavarrog <anavar...@gmail.com> wrote: > > > > Hi all, > > > > I'm using H2 database and SymmetricDS to synchronize data between > > > several terminals: > > > > * H2 version: 1.2.135 > > > * Connection URL: jdbc:h2:~/path/to/ > > > database;DB_CLOSE_DELAY=-1;CACHE_TYPE=SOFT_LRU;CIPHER=AES;LOCK_TIMEOUT=100000;MVCC=TRUE > > > > and after an intensive stress test execution we've observed several > > > exceptions in one of the virtual machines I'm using for testing. It > > > starts showing this: > > > > 2012-02-19 17:52:08,616 ERROR (AbstractServlet.java:295) Error while > > > processing GET request for externalId: null, node: > > > 90bba6e2-0e57-495b-9412-0c47819d99e5 at 10.80.18.30 (10.80.18.30) > > > org.springframework.jdbc.UncategorizedSQLException: > > > PreparedStatementCallback; uncategorized SQLException for SQL [ > > > update sym_data_event set batch_id = ?, batched = ? where > > > batched = ? > > > and node_id = ? and channel_id = ? > > > ]; SQL state [HY000]; error code [50000]; General error: > > > "java.lang.RuntimeException: Unexpected code path"; SQL statement: > > > > update sym_data_event set batch_id = ?, batched = ? where > > > batched = ? > > > and node_id = ? and channel_id = ? > > > [50000-135]; nested exception is org.h2.jdbc.JdbcSQLException: > > > General error: "java.lang.RuntimeException: Unexpected code path"; SQL > > > statement: > > > > update sym_data_event set batch_id = ?, batched = ? where > > > batched = ? > > > and node_id = ? and channel_id = ? > > > [50000-135] > > > at > > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > > 83) > > > at > > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > > 80) > > > at > > > org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java: > > > 80) > > > at > > > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: > > > 607) > > > at > > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > > 792) > > > at > > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > > 850) > > > at > > > org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java: > > > 858) > > > at > > > org.jumpmind.symmetric.service.impl.OutgoingBatchService.buildOutgoingBatches(OutgoingBatchService.java: > > > 148) > > > at > > > org.jumpmind.symmetric.service.impl.DataExtractorService.extract(DataExtractorService.java: > > > 286) > > > at > > > org.jumpmind.symmetric.transport.handler.PullResourceHandler.pull(PullResourceHandler.java: > > > 59) > > > at > > > org.jumpmind.symmetric.web.PullServlet.handlePost(PullServlet.java: > > > 66) > > > at > > > org.jumpmind.symmetric.web.PullServlet.handleGet(PullServlet.java: > > > 49) > > > at > > > org.jumpmind.symmetric.web.SymmetricServlet.handleGet(SymmetricServlet.java: > > > 133) > > > at > > > org.jumpmind.symmetric.web.AbstractServlet.doGet(AbstractServlet.java: > > > 98) > > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) > > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) > > > at > > > org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java: > > > 502) > > > at org.mortbay.jetty.servlet.ServletHandler > > > $CachedChain.doFilter(ServletHandler.java:1148) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:151) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:145) > > > at > > > org.jumpmind.symmetric.web.compression.CompressionFilter.doFilter(CompressionFilter.java: > > > 157) > > > at > > > org.jumpmind.symmetric.web.CompressionFilter.doFilter(CompressionFilter.java: > > > 73) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > > at > > > org.jumpmind.symmetric.web.NodeConcurrencyFilter.doFilter(NodeConcurrencyFilter.java: > > > 74) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > > at > > > org.jumpmind.symmetric.web.InetAddressFilter.doFilter(InetAddressFilter.java: > > > 89) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > > at > > > org.jumpmind.symmetric.web.AuthenticationFilter.doFilter(AuthenticationFilter.java: > > > 68) > > > at org.jumpmind.symmetric.web.SymmetricFilter > > > $SymmetricFilterChain.doFilter(SymmetricFilter.java:143) > > > at > > > org.jumpmind.symmetric.web.SymmetricFilter.doFilter(SymmetricFilter.java: > > > 76) > > > at org.mortbay.jetty.servlet.ServletHandler > > > $CachedChain.doFilter(ServletHandler.java:1139) > > > at > > > org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java: > > > 378) > > > at > > > org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java: > > > 765) > > > at > > > org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java: > > > 114) > > > at > > > org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java: > > > 152) > > > at org.mortbay.jetty.Server.handle(Server.java:324) > > > at > > > org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java: > > > 535) > > > at org.mortbay.jetty.HttpConnection > > > $RequestHandler.headerComplete(HttpConnection.java:865) > > > at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:539) > > > at > > > org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212) > > > at > > > org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404) > > > at > > > org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java: > > > 409) > > > at org.mortbay.thread.QueuedThreadPool > > > $PoolThread.run(QueuedThreadPool.java:520) > > > Caused by: org.h2.jdbc.JdbcSQLException: General error: > > > "java.lang.RuntimeException: Unexpected code path"; SQL statement: > > > > update sym_data_event set batch_id = ?, batched = ? where > > > batched = ? > > > and node_id = ? and channel_id = ? > > > [50000-135] > > > at > > > org.h2.message.DbException.getJdbcSQLException(DbException.java: > > > 327) > > > at org.h2.message.DbException.get(DbException.java:156) > > > at org.h2.message.DbException.convert(DbException.java:279) > > > at org.h2.table.RegularTable.removeRow(RegularTable.java:349) > > > at org.h2.table.Table.updateRows(Table.java:400) > > > at org.h2.command.dml.Update.update(Update.java:124) > > > at > > > org.h2.command.CommandContainer.update(CommandContainer.java:70) > > > at org.h2.command.Command.executeUpdate(Command.java:199) > > > at > > > org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java: > > > 141) > > > at > > > org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java: > > > 127) > > > at > > > org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java: > > > 101) > > > at org.springframework.jdbc.core.JdbcTemplate > > > $2.doInPreparedStatement(JdbcTemplate.java:798) > > > at > > > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: > > > 591) > > > ... 39 more > > > Caused by: java.lang.RuntimeException: Unexpected code path > > > at org.h2.message.DbException.throwInternalError(DbException.java: > > > 226) > > > at org.h2.message.DbException.throwInternalError(DbException.java: > > > 239) > > > at org.h2.index.PageBtreeNode.addChild(PageBtreeNode.java:157) > > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:356) > > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:323) > > > at org.h2.index.PageBtreeNode.remove(PageBtreeNode.java:323) > > > at... > > read more » -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
