Concurrent update Exception ...

[ivrusuboca]

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-

[Thotheolh]

Maybe you could post the trace , sql statement trying to execute ,
database url and any additional options appended and the rough
scenario of what happened.

[Thomas Mueller]

Hi,

> 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]

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

What database URL and setting do you use?

> Do you have any suggestion as how to find the other transaction that
> it is interfering with this one above ?

There is no easy solution I am afraid. You need to check what other
SQL statements were executed that changed (but not committed)
X_ACK_MESSAGE right before the one that fails.

Regards,
Thomas

[ivrusuboca]

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

[Thomas Mueller]

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 also
http://www.h2database.com/html/changelog.html

If this doesn't help, could you send me or post the .trace.db file?

Regards,
Thomas

[ivrusuboca]

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" <thomas.tom.muel...@gmail.com>
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<ivrusub...@gmail.com> 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
>

[Thomas Mueller]

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

[ivrusuboca]

Hello Thomas,

Thank you very much for looking at this issue.

The problem, however, is that my connection is exactly this :

jdbc:h2:/tmp/prod/
h2cache;TRACE_LEVEL_FILE=3;CACHE_SIZE=1048576;LOCK_MODE=3;MVCC=TRUE;MULTI_THREADED=0

But I am opening and using several Sessions, concurrently, from
different threads .

I know that you are using, I think, a synchonized(database){ } for
MVCC=FALSE;MULTI_THREADED=0 mode ; but I have the feeling that even
with MVCC=TRUE;MULTI_THREADED=0, the situation might occur, since , I
believe, for MVCC=TRUE, you are synchronizing in sessions, not
database ( I need to look into the h2 source code again , to check
this )

Can you, please, check, that there is really no way this situation
desccribed earlier, can happen in MVCC=FALSE;MULTI_THREADED=0 mode ?

Thank you,
Ionut



On Sep 10, 3:45 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> 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
>

[Thomas Mueller]

Hi,

> jdbc:h2:/tmp/prod/
> h2cache;TRACE_LEVEL_FILE=3;CACHE_SIZE=1048576;LOCK_MODE=3;MVCC=TRUE;MULTI_THREADED=0

This means MULTI_THREADED is disabled in your case.

> But I am opening and using several Sessions, concurrently, from
> different threads .

This shouldn't be a problem.

> I believe, for MVCC=TRUE, you are synchronizing in sessions, not
> database

No, in this case access is synchronized on 'database'. See also
Command.java, methods executeQueryLocal and executeUpdate. You can
also find the places where isMultiVersion and getMultiThreaded is
used.

Regards,
Thomas