Re: MVCC and SELECT FOR UPDATE
This doesn't really help, but I thought the idea of MVCC is to be an optimistic lock?. So by definition it shouldn't be able to timeout as it doesn't even need to get a lock ? Or am I grossly misunderstanding something here? Does FOR UPDATE still lock a table for other writers, it just doesn't lock it for other readers ? On 26/02/2013 9:34 PM, srinivas wrote: Hi, We are getting this exception on trying to insert to a table: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168]; nested exception is org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:379) at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:645) at com.x.MaterialRepository.save(MaterialRepository.java:281) at com.x.MaterialRepository.findOrCreateFrom(MaterialRepository.java:317) at com.x.MaterialDatabaseUpdater.folderFor(MaterialDatabaseUpdater.java:140) at com.x.MaterialDatabaseUpdater.initializeMaterialWithLatestRevision(MaterialDatabaseUpdater.java:102) at com.x.MaterialDatabaseUpdater.access$000(MaterialDatabaseUpdater.java:30) at com.x.MaterialDatabaseUpdater$1.doInTransaction(MaterialDatabaseUpdater.java:73) at com.x.transaction.TransactionCallback.doWithExceptionHandling(TransactionCallback.java:8) at com.x.transaction.TransactionTemplate$3.doInTransaction(TransactionTemplate.java:37) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) at com.x.transaction.TransactionTemplate.executeWithExceptionHandling(TransactionTemplate.java:33) at com.x.MaterialDatabaseUpdater.updateMaterial(MaterialDatabaseUpdater.java:71) at com.x.MaterialUpdateListener.onMessage(MaterialUpdateListener.java:27) at com.x.MaterialUpdateListener.onMessage(MaterialUpdateListener.java:12) at com.x.messaging.activemq.JMSMessageListenerAdapter.runImpl(JMSMessageListenerAdapter.java:49) at com.x.activemq.JMSMessageListenerAdapter.run(JMSMessageListenerAdapter.java:34) at java.lang.Thread.run(Thread.java:662) Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168] 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:276) at org.h2.command.Command.executeUpdate(Command.java:232) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2176) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at
Re: MVCC and SELECT FOR UPDATE
A more helpful reply. The exception given seems to give a clue. Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table UNIQUE_a_INDEX_A: another transaction has updated or deleted the same row It would seem you are updating the row simultaneously in 2 transactions?, Therefore giving the exception. I'm not sure why it references the index as the table name? That would appear to be a bug?. Maybe you have 2 simultaneous inserts in the table? I'm not sure how MVCC handles that. Also note that MVCC is experimental. Thanks, Ryan On 26/02/2013 9:34 PM, srinivas wrote: Hi, We are getting this exception on trying to insert to a table: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168]; nested exception is org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124) at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424) at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410) at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:379) at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:645) at com.x.MaterialRepository.save(MaterialRepository.java:281) at com.x.MaterialRepository.findOrCreateFrom(MaterialRepository.java:317) at com.x.MaterialDatabaseUpdater.folderFor(MaterialDatabaseUpdater.java:140) at com.x.MaterialDatabaseUpdater.initializeMaterialWithLatestRevision(MaterialDatabaseUpdater.java:102) at com.x.MaterialDatabaseUpdater.access$000(MaterialDatabaseUpdater.java:30) at com.x.MaterialDatabaseUpdater$1.doInTransaction(MaterialDatabaseUpdater.java:73) at com.x.transaction.TransactionCallback.doWithExceptionHandling(TransactionCallback.java:8) at com.x.transaction.TransactionTemplate$3.doInTransaction(TransactionTemplate.java:37) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) at com.x.transaction.TransactionTemplate.executeWithExceptionHandling(TransactionTemplate.java:33) at com.x.MaterialDatabaseUpdater.updateMaterial(MaterialDatabaseUpdater.java:71) at com.x.MaterialUpdateListener.onMessage(MaterialUpdateListener.java:27) at com.x.MaterialUpdateListener.onMessage(MaterialUpdateListener.java:12) at com.x.messaging.activemq.JMSMessageListenerAdapter.runImpl(JMSMessageListenerAdapter.java:49) at com.x.activemq.JMSMessageListenerAdapter.run(JMSMessageListenerAdapter.java:34) at java.lang.Thread.run(Thread.java:662) Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement: insert into materials (id, a, b, c, d, e, f) values (null, ?, ?, ?, ?, ?, 'a') [50200-168] 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:276) at org.h2.command.Command.executeUpdate(Command.java:232) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:156) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:142) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:94) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2176) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2656) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:321) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:204) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:130) at
Re: MVCC and SELECT FOR UPDATE
On 2013-02-27 10:19, Ryan How wrote: This doesn't really help, but I thought the idea of MVCC is to be an optimistic lock?. So by definition it shouldn't be able to timeout as it doesn't even need to get a lock ? Or am I grossly misunderstanding something here? Does FOR UPDATE still lock a table for other writers, it just doesn't lock it for other readers ? Try reading about MVCC first. http://en.wikipedia.org/wiki/Multiversion_concurrency_control -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: MVCC and SELECT FOR UPDATE
On 2013-02-27 15:54, Ryan How wrote:
Yeah, I've read that one a few times. It doesn't really explain a lot
of detail. It only says
Note, however, that the write transaction does need to use locks
I was just having a dumb moment... I mean what is the point of FOR
UPDATE if it doesn't get a lock?
The H2 Documentation says
When using MVCC in this database, delete, insert and update
operations will only issue a shared lock on the table. An exclusive
lock is still used when adding or removing columns, when dropping the
table, and when using SELECT ... FOR UPDATE
So just to clarify my understanding. I can update / insert / delete in
MVCC without getting exclusive locks, so really it doesn't block other
writers, and still ensures transaction isolation. ?
Correct. It's the OPTIMISTIC CONCURRENCY strategy applied to database
modifications.
It looks like this:
while (true) {
generate some changes
if (nothing else has modified the rows we're interested in) {
commit changes
break;
}
throw away changes
if (timed out)
throw exception;
}
If I do FOR UPDATE, it gets an exclusive lock (Table level?), so this
would block readers and writers. But I'm having trouble thinking of a
use case for this.
Sometimes MVCC needs help, or it have trouble making progress on highly
contended rows and tables.
Sessions can find themselves stuck in a retry loop for some time.
In particular, our MVCC implementation is not nearly as smart as
PostgreSQL or Oracle.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: MVCC and SELECT FOR UPDATE
Thanks for the clarification. That explains the time out exceptions and
also makes perfect sense using FOR UPDATE.
On 27/02/2013 10:03 PM, Noel Grandin wrote:
Correct. It's the OPTIMISTIC CONCURRENCY strategy applied to database
modifications.
It looks like this:
while (true) {
generate some changes
if (nothing else has modified the rows we're interested in) {
commit changes
break;
}
throw away changes
if (timed out)
throw exception;
}
If I do FOR UPDATE, it gets an exclusive lock (Table level?), so this
would block readers and writers. But I'm having trouble thinking of a
use case for this.
Sometimes MVCC needs help, or it have trouble making progress on
highly contended rows and tables.
Sessions can find themselves stuck in a retry loop for some time.
In particular, our MVCC implementation is not nearly as smart as
PostgreSQL or Oracle.
--
You received this message because you are subscribed to the Google Groups H2
Database group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
About the transaction log
I was wondering, in my work with the transaction log of the h2 database I came across a rather strange behaviour. I was just going to ask, if this is happening to everybody or just me. When I create a table and drop it afterwards and then create and drop it again (always the same structure and name), the key value of the written drop transaction in the log is not the same as the saved tableMap-number of the created table. is this supposed to happen? By reading the log and recreating the transactions, I always get a error, because the table is not in the tableMap. i hope you understand what I mean. If not, I will explain it further. Regards, Lutz -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
getting the ROWNUM of a specific row with a WHERE
Hi, I am trying to get the rank of a user's score in an H2 Database. The following query gives me all the scores ordered descending with a corresponding row number (this part works): * *select *, rownum as rn from (select userId, score from nw_data ORDER BY score DESC) But I only want to find the rank of an individual user (say userId=3), so wrapped this in another select query: select * from (select *, rownum as rn from (select userId, score from nw_data ORDER BY score DESC)) WHERE userId=3; I would have expected this to work, but rn (the output for rownum) is always 1. It is as though the rownum function is being repeated in the final outer select, when really I just want the value that was previously computed. Clearly I am missing something here. Any ideas on how to structure this? Thanks, -Adam -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: MVCC and SELECT FOR UPDATE
Hi, MVCC does use a mechanism that behaves like locks when writing (locking the changed rows at least), and in some cases also when reading (predicate locking for example). See also http://www.postgresql.org/docs/9.2/static/transaction-iso.html Regards, Thomas -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: MVCC=true, multi-thread and ReentrantLock
On Wed, Feb 27, 2013 at 9:23 PM, Thomas Mueller thomas.tom.muel...@gmail.com wrote: . And just replacing synchronized with ReentrantLock wouldn't really help much. Depends on how hard the lock is being contended. If a lock is heavily contended, the fairness property of ReentrantLock would be very useful. -- You received this message because you are subscribed to the Google Groups H2 Database group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
