Sorry for the mess, in last entry I was referring to a problem that
I've experienced recently, concurrent updates of the same row:
org.springframework.dao.CannotAcquireLockException:
PreparedStatementCallback; SQL [
update sym_data_event set batch_id = ?, batched = ? where
batched = ?
and node_id = ? and channel_id = ?
]; Timeout trying to lock table ; SQL statement:
update sym_data_event set batch_id = ?, batched = ? where
batched = ?
and node_id = ? and channel_id = ?
[50200-135]; nested exception is org.h2.jdbc.JdbcSQLException:
Timeout trying to lock table ; SQL statement:
update sym_data_event set batch_id = ?, batched = ? where
batched = ?
and node_id = ? and channel_id = ?
[50200-135]
at
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:
244)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:
72)
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: Timeout trying to lock
table ; SQL statement:
update sym_data_event set batch_id = ?, batched = ? where
batched = ?
and node_id = ? and channel_id = ?
[50200-135]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.command.Command.executeUpdate(Command.java:204)
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: org.h2.jdbc.JdbcSQLException: Concurrent update in table
"SYM_DATA_EVENT": another transaction has updated or deleted the same
row [90131-135]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.RegularTable.removeRow(RegularTable.java:323)
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)
... 44 more
On Feb 24, 3:44 pm, anavarrog <[email protected]> wrote:
> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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...
>
> 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 [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.
