search

sagarswathi / h2database

Automatically exported from code.google.com/p/h2database
0 stars 1 forks source link

MVCC write lock does not wake waiters on unlock #367

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
I enabled MVCC and have two writers attempting to lock the same row.  I expect 
one writer to get the lock and block the second.  After commit, I expect the 
second writer to wake up and proceed.  I observe that the second writer is 
blocked forever and eventually times out.  I attached a test case that fails:

Running org.h2.test.mvcc.TestMvccMultiThreaded
Exception in thread "main" java.lang.RuntimeException: 
java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: Timeout trying to 
lock table ; SQL statement:
SELECT * FROM TEST WHERE KEY = 3 FOR UPDATE [50200-162]
        at org.h2.build.BuildBase.java(BuildBase.java:872)
        at org.h2.build.Build.test(Build.java:672)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.h2.build.BuildBase.invoke(BuildBase.java:241)
        at org.h2.build.BuildBase.runTarget(BuildBase.java:207)
        at org.h2.build.BuildBase.run(BuildBase.java:188)
        at org.h2.build.Build.main(Build.java:35)
Caused by: java.lang.RuntimeException: org.h2.jdbc.JdbcSQLException: Timeout 
trying to lock table ; SQL statement:
SELECT * FROM TEST WHERE KEY = 3 FOR UPDATE [50200-162]
        at org.h2.build.BuildBase.invoke(BuildBase.java:250)
        at org.h2.build.BuildBase.java(BuildBase.java:870)
        ... 9 more
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL 
statement:
SELECT * FROM TEST WHERE KEY = 3 FOR UPDATE [50200-162]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
        at org.h2.message.DbException.get(DbException.java:158)
        at org.h2.command.Command.filterConcurrentUpdate(Command.java:272)
        at org.h2.command.Command.executeQuery(Command.java:189)
        at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:173)
        at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:152)
        at org.h2.test.mvcc.TestMvccMultiThreaded.testSelectForUpdateMultiThreaded(TestMvccMultiThreaded.java:77)
        at org.h2.test.mvcc.TestMvccMultiThreaded.test(TestMvccMultiThreaded.java:40)   
        at org.h2.test.mvcc.TestMvccMultiThreaded.main(TestMvccMultiThreaded.java:32)   
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.h2.build.BuildBase.invoke(BuildBase.java:241)
        ... 10 more
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "TEST": 
another transaction has updated or deleted the same row [90131-162]
        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.table.RegularTable.removeRow(RegularTable.java:346)
        at org.h2.table.TableFilter.lockRows(TableFilter.java:953)
        at org.h2.command.dml.Select.queryFlat(Select.java:535)
        at org.h2.command.dml.Select.queryWithoutCache(Select.java:618)
        at org.h2.command.dml.Query.query(Query.java:297)
        at org.h2.command.dml.Query.query(Query.java:267)
        at org.h2.command.dml.Query.query(Query.java:36)
        at org.h2.command.CommandContainer.query(CommandContainer.java:82)
        at org.h2.command.Command.executeQuery(Command.java:187)
        ... 20 more

Original issue reported on code.google.com by arg...@gmail.com on 23 Dec 2011 at 12:57

GoogleCodeExporter commented 8 years ago 
For H2, running multiple concurrent SELECT ... FOR UPDATE is the same as having 
multiple concurrent update statements trying to change the same row. That's why 
you get "another transaction has updated or deleted the same row".

I don't think this behavior can easily be changed with the current 
implementation, but patches are welcome.

Original comment by thomas.t...@gmail.com on 17 Feb 2012 at 1:38

GoogleCodeExporter commented 8 years ago 
Not sure if I have the same issue or not, but would appreciate your input.

My app has a number of concurrent threads.  One thread takes requests from a 
web UI and performs equivalent CRUD operations on the H2 DB and an external 
store. Another, queries the external store every minute and merges the results 
into the H2 DB.  This way, both stores remain in sync.  Most of the time, it 
all works fine.  A few times per week, however, the threads (which share the 
same H2 data source and access it via Spring/JPA/Hibernate) try to update the 
same row in H2 at around the same time.

When this happens, I get the following error, after which point the record in 
question actually appears to be in the db twice (same PK and everything)!  
Bouncing the process fixes the issue, though, so the H2 file is obviously still 
in a good state.

org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
update GROUPS set allowedMemberType=?, description=?, dn=?, email=?, 
emailDisplayName=?, iacEnabled=?, lastModified=?, managementGroup_
name=? where name=? [50200-149]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
        at org.h2.message.DbException.get(DbException.java:156)
        at org.h2.command.Command.filterConcurrentUpdate(Command.java:253)
        at org.h2.command.Command.executeUpdate(Command.java:211)
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143)
        at org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:1087)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:185)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
        at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:76)
        at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:512)
        at com.nomura.fid.core.ldap.transaction.JpaAndLdapTransactionManager.doCommit(JpaAndLdapTransactionManager.java:75)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.
java:754)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:72
3)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSuppor
t.java:393)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)

 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy57.saveGroup(Unknown Source)
        at search.LdapSearchEngine.addManagerToGroup(LdapSearchEngine.java:159)
        at controllers.Modify.addToList(Modify.java:70)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:546)
        at play.mvc.ActionInvoker.invoke(ActionInvoker.java:500)
        at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:476)
        at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:471)
        at play.mvc.ActionInvoker.invoke(ActionInvoker.java:159)
        at play.server.ServletWrapper$ServletInvocation.execute(ServletWrapper.java:540)
        at play.Invoker$Invocation.run(Invoker.java:265)
        at play.server.ServletWrapper$ServletInvocation.run(ServletWrapper.java:531)
        at play.Invoker.invokeInThread(Invoker.java:67)
        at play.server.ServletWrapper.service(ServletWrapper.java:130)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
        at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
        at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
        at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
        at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
        at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
        at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
        at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
        at org.mortbay.jetty.Server.handle(Server.java:326)
        at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
        at org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
        at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:547)
        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:582)
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "GROUPS": 
another transaction has updated or deleted the same row [90131-149]
        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:345)
        at org.h2.table.Table.updateRows(Table.java:410)
        at org.h2.command.dml.Update.update(Update.java:125)
        at org.h2.command.CommandContainer.update(CommandContainer.java:69)
        at org.h2.command.Command.executeUpdate(Command.java:209)
        ... 57 more

H2 config is:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" 
destroy-method="close">
        <property name="driverClassName" value="${ldap.jdbc.driver:org.h2.Driver}" />
        <property name="url" value="jdbc:h2:file:db/swarmDB;CACHE_SIZE=10000;MVCC=TRUE;LOCK_TIMEOUT=30000;TRACE_LEVEL_FILE=3" />
        <property name="username" value="sa" />
        <property name="password" value="" />
    <property name="minIdle" value="4" /> 
    <property name="timeBetweenEvictionRunsMillis" value="20000" />  
</bean>

Any idea how I can avoid this situation?  I obviously have no control over 
which rows are being updated at any given time.  I would have thought that this 
is a valid MVCC use case and the transactions would just queue up and be 
executed in order.

Thanks very much for your help.

Original comment by brian.s....@gmail.com on 10 Sep 2012 at 6:41

GoogleCodeExporter commented 8 years ago 
Please don't 'hijack' other issues. It's much better to send an email to the 
group or use StackOverflow.

You are using an old version if H2 (build 149). I suggest to try again using a 
more recent version. If it's still a problem, send an email to the group or use 
StackOverflow.

Original comment by thomas.t...@gmail.com on 10 Sep 2012 at 6:58

GoogleCodeExporter commented 8 years ago 
Sorry, wasn't trying to hijack.  This just looked very similar to my issue.  I 
will try a more recent version and, if the issue persists, I will send an email 
to the group or use StackOverflow.  Thanks.

Original comment by brian.s....@gmail.com on 10 Sep 2012 at 10:44

GoogleCodeExporter commented 8 years ago 
Btw: Since the problem is intermittent, it may take a few days to
determine the effect if the newer version. Given that the original
poster is using 162 and has the same basic stack trace, do you have
reason to believe that my issue would be resolved in a more recent
version?

Original comment by brian.s....@gmail.com on 10 Sep 2012 at 11:45

GoogleCodeExporter commented 8 years ago 
Hi,

> Given that the original poster is using 162 and has the same basic stack 
trace, do you have
> reason to believe that my issue would be resolved in a more recent version?

Yes. Changes are listed here: http://www.h2database.com/html/changelog.html

Original comment by thomas.t...@gmail.com on 11 Sep 2012 at 4:38

GoogleCodeExporter commented 8 years ago 
Setting to fixed. If not, please provide a test case.

Original comment by thomas.t...@gmail.com on 28 Jul 2013 at 9:36