MVCC and SELECT FOR UPDATE

[Dashie]

When I use H2 in MVCC mode I have a JdbcSQLException if I acquire a
row lock with a SELECT FOR UPDATE.

org.h2.jdbc.JdbcSQLException: Concurrent update in table "TEST":
another transaction has updated or deleted the same row; SQL
statement:
SELECT * FROM test WHERE id = 1 FOR UPDATE; [90131-148]

But when I acquire a lock with an UPDATE I have no errors.

[Thomas Mueller]

Hi,

I think I know the problem. SELECT ... FOR UPDATE doesn't currently
re-run the query the same way as UPDATE on a concurrent update. I will
try to fix this problem for the next release.

Regards,
Thomas

[srinivas]

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
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
at
org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:563)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:551)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:547)
at
org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:648)
at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:374)
... 17 more
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table
"UNIQUE_a_INDEX_A": another transaction has updated or deleted the same row
[90131-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.table.RegularTable.addRow(RegularTable.java:146)
at org.h2.command.dml.Insert.insertRows(Insert.java:124)
at org.h2.command.dml.Insert.update(Insert.java:84)
at org.h2.command.CommandContainer.update(CommandContainer.java:75)
at org.h2.command.Command.executeUpdate(Command.java:230)
... 40 more

We are using: h2-1.3.168.jar with Hibernate (3.3.2.GA) & IBatis (2.3.4.726).
With following settings: jdbc:h2:" + path +
"/cruise;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MVCC=TRUE;CACHE_SIZE=131072;DATABASE_EVENT_LISTENER='"
+ ClassName + "'";

We found this post related to the error:
http://stackoverflow.com/questions/14060632/how-to-do-testing-with-hibernate-h2-using-testng

Do you have an idea when this can occur?

Regards,
Srinivas




--
View this message in context: http://h2-database.66688.n3.nabble.com/MVCC-and-SELECT-FOR-UPDATE-tp2140527p4025850.html
Sent from the H2 Database mailing list archive at Nabble.com.

[Ryan How]

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" ?

[Ryan How]

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:

[Ryan How]

Also, what is UNIQUE_a_INDEX_A ? What is in an index on?. I can't
remember hibernate very well, but does it insert it's own ID field which
it generates and manages?. If that is the case it might be trying to
insert the same ID twice?. I think for MVCC mode it would need to be a
database sequence or a random ID.

[Noel Grandin]

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

[Ryan How]

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. ?

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.


Thanks, Ryan

[Noel Grandin]

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.

[Ryan How]

Thanks for the clarification. That explains the time out exceptions and
also makes perfect sense using FOR UPDATE.

[Thomas Mueller]

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

[srinivas]

Hi Ryan,

Thank you for the reply. The unique index is created by us to maintain
uniqueness on that column. The values are generated by us based on some
criteria. We made sure that the generated value is not in DB already.

Regards,
Srinivas



--
View this message in context: http://h2-database.66688.n3.nabble.com/MVCC-and-SELECT-FOR-UPDATE-tp2140527p4025864.html