Hi,
You are right, if two threads concurrently update the same table when
using multi-version concurrency, things could break. However by
default this can not happend because only one thread can concurrently
update: MULTI_THREADED is disabled by default.
I wrote a test case that enables MULTI_THREADED and MVCC, and I can
reproduce the problem. In the next release I will make sure the
features can not be used at the same time:
"Multi-Version Concurrency (MVCC) may no longer be used when using
the multi-threaded kernel feature (MULTI_THREADED). An exception is
thrown when trying to connect with both settings. Additional
synchronization is required before those features can be used
together."
Regards,
Thomas
On Wed, Sep 3, 2008 at 4:22 PM, ivrusuboca <[EMAIL PROTECTED]> wrote:
>
>
> Hello Thomas,
>
> Thank you for the reply. I had a look at release notes and I think the
> changes below are unrelated to my problem.
>
> On the other hand, I had a look at the source code and I would kindly
> ask you if the scenario below can happen; because, if it happens, then
> I think we have an issue :
>
> Source code snippets ( from h2 version 1.0.69 ) :
>
> TableData.java ( line 275 ) :
>
> 275 public void removeRow(Session session, Row row) throws
> SQLException {
> 276 lastModificationId = database.getNextModificationDataId();
> 277 if (database.isMultiVersion()) {
> 278 if (row.getDeleted()) {
> 279 throw
> Message.getSQLException(ErrorCode.CONCURRENT_UPDATE_1, getName());
> 280 }
> 281 int old = row.getSessionId();
> 282 int newId = session.getId();
> 283 if (old == 0) {
> 284 row.setSessionId(newId);
> 285 } else if (old != newId) {
> 286 throw
> Message.getSQLException(ErrorCode.CONCURRENT_UPDATE_1, getName());
> 287 }
> 288 }
> ................
>
> Record.java ( line 65 onwards ) :
>
> 65 public void setSessionId(int sessionId) {
> 66 this.sessionId = sessionId;
> 67 }
> 68
> 69 public int getSessionId() {
> 70 return sessionId;
> 71 }
> 72
> 73 /**
> 74 * This record has been committed. The session id is reset.
> 75 */
> 76 public void commit() {
> 77 this.sessionId = 0;
> 78 }
> ......
>
> Scenario :
>
> Suppose there are two threads, T1 and T2, which read and update
> 'value' in a table, for a certain row with key = 123
>
> Let's assume that initially value = 0 ( zero )
>
> T1 :
> T1.1 begin tran
> T1.2 select value from my_table where key = 123
> T1.3 newValue = value + 5
> T1.4 update my_table set value = newValue where key = 123
> T1.5 commit
>
> T2 :
> T2.1 begin tran
> T2.2 select value from my_table where key = 123
> T2.3 newValue = value + 8
> T2.4 update my_table set value = newValue where key = 123
> T2.5 commit
>
> Now, if MVCC = true and both threads T1 & T2 are running, we should
> have a final result of 13, as either 0 + 5 + 8 or 0 + 8 + 5.
>
> However, I think, we might end up having either 5 or 8 , in the
> following situation :
>
> step 1 : Thread T1 executes T1.1 & T1.2 , and so it reads value = 0
> step 2 : Thread T2 executes T2.1 & T2.2 , and so it also reads value =
> 0
> step 3 : Thread T1 executes T1.3 and calculates newValue = value + 5 =
> 5
> step 4 : Thread T2 executes T2.3 and calculates newValue = value + 8 =
> 8
> step 5 : Thread T1 executes T1. 4 and updates row with newValue = 5
> step 6 : Thread T1 executes T1.5 and so it commits the transaction; in
> this moment, row.sessionId becomes 0 ( zero ) ( Record.java line 77 )
> step 7 : Thread T2 executes T2.4 & T2.5 and so it updates the row with
> newValue = 8 because, somehow, the code below _might_ work this way :
>
> 275 public void removeRow(Session session, Row row) throws
> SQLException { <=== 'this' is T2
> 276 lastModificationId = database.getNextModificationDataId();
> 277 if (database.isMultiVersion()) { <=== yes, returns true
> 278 if (row.getDeleted()) { <=== returns false; the row is
> not deleted
> 279 throw
> Message.getSQLException(ErrorCode.CONCURRENT_UPDATE_1, getName());
> 280 }
> 281 int old = row.getSessionId(); <=== returns '0' ( zero )
> because T1 committed ( at step 6 above )
> 282 int newId = session.getId(); <=== returns some non-zero
> Id ( '1122334455' )
> 283 if (old == 0) { <== returns true , as per the line 281
> result
> 284 row.setSessionId(newId); <== so this line executes
> and row gets assigned T2's session Id
> 285 } else if (old != newId) { <=== _but_ this else branch
> is not executed, so the old != newId check is skipped
> 286 throw
> Message.getSQLException(ErrorCode.CONCURRENT_UPDATE_1, getName()); <==
> so this does not happen
> 287 }
> 288 }
> 289 <=== so T2 continues here and eventually commits the row
> with an updated newValue = 8
>
> Can you, please, confirm if this is a problem or it is I who got
> something wrong here ? ( I hope I do .... )
>
> Thank you very much, indeed.
>
> Ionut
>
> On Aug 20, 4:24 am, "Thomas Mueller" <[EMAIL PROTECTED]>
> wrote:
>> Hi,
>>
>> Maybe the problem is solved in a newer version of H2 - could you try
>> 1.0.77 (2008-08-16)? Two problems related with MVCC have been solved
>> (CREATE TABLE AS SELECT did not work correctly in the multi-version
>> concurrency mode., Multi version concurrency (MVCC): when a row was
>> updated or deleted, but this change was rolled back, the row was not
>> visible by other sessions if no index was used to access it.) see
>> alsohttp://www.h2database.com/html/changelog.html
>>
>> If this doesn't help, could you send me or post the .trace.db file?
>>
>> Regards,
>> Thomas
>>
>> On Fri, Aug 15, 2008 at 6:23 AM,ivrusuboca<[EMAIL PROTECTED]> wrote:
>>
>> > Thanks guys, for your replies.
>>
>> > Thotheolh : unfortunately I cannot post the entire stuff 'cause it
>> > contains corporate information ... etc ... None the less I will try to
>> > get the most out of the h2 data and post it back.
>>
>> > Thomas : here are the connection details :
>>
>> > jdbc:h2:${DATASTORE_DIR}/${env}/
>> > xstabcache;TRACE_LEVEL_FILE=3;CACHE_SIZE=$
>> > {CACHE_SIZE};LOCK_MODE=3;MVCC=true;MULTI_THREADED=0
>>
>> > Regards,
>> > Ionut
>>
>> > On Aug 8, 9:04 pm,ivrusuboca<[EMAIL PROTECTED]> wrote:
>> >> Hi guys,
>>
>> >> I am having thrown the following exception ( layers are : H2,
>> >> Hibernate & my app ) :
>>
>> >> org.h2.jdbc.JdbcSQLException: Concurrent update in table
>> >> X_ACK_MESSAGE: another transaction has updated or deleted the same row
>> >> [90131-74]
>> >> at org.h2.message.Message.getSQLException(Message.java:103)
>> >> at org.h2.message.Message.getSQLException(Message.java:114)
>> >> at org.h2.message.Message.getSQLException(Message.java:77)
>> >> at org.h2.table.TableData.removeRow(TableData.java:287)
>> >> at org.h2.table.Table.updateRows(Table.java:356)
>> >> at org.h2.command.dml.Update.update(Update.java:131)
>> >> at
>> >> org.h2.command.CommandContainer.update(CommandContainer.java:69)
>> >> at org.h2.command.Command.executeUpdate(Command.java:203)
>> >> at
>> >> org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:
>> >> 137)
>> >> at
>> >> org.h2.jdbc.JdbcPreparedStatement.executeBatch(JdbcPreparedStatement.java:
>> >> 1049)
>> >> at
>> >> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:
>> >> 1723)
>> >> at
>> >> org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:
>> >> 48)
>> >> at
>> >> org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:34)
>> >> at
>> >> org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:
>> >> 2408)
>> >> at
>> >> org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:
>> >> 2312)
>> >> at
>> >> org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:
>> >> 2612)
>> >> at
>> >> org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:
>> >> 96)
>> >> at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:
>> >> 279)
>> >> at
>> >> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
>> >> at
>> >> org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
>> >> at
>> >> org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:
>> >> 298)
>> >> at
>> >> org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:
>> >> 27)
>> >> at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
>> >> at
>> >> org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
>> >> at
>> >> org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:
>> >> 106)
>> >> <my app here>
>>
>> >> So the message suggests that another transaction has updated or
>> >> deleted the same row I am trying to update here. But, unfortunatley, I
>> >> am unable to trace ( via <myh2>.trace.db file ) any such transaction,
>> >> although I have the tracelog on level 3 therefore I see all SQLs
>> >> printed out.
>>
>> >> Do you have any suggestion as how to find the other transaction that
>> >> it is interfering with this one above ?
>>
>> >> Thank you very much, indeed.
>> >> -ivrusuboca-
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
