Concurrent update error in append only table

[Marco Willemart]

Hi,

I get the following error: 

org.h2.jdbc.JdbcSQLException: Concurrent update in table "EVENT_STORE": another transaction has updated or deleted the same row [90131-193]

It seems really strange because the table EVENT_STORE is append only, i.e., only inserts and selects are performed on it, updates and deletes are never performed.

I use H2 1.4.193.

I tried to set MULTI_THREADED=1 but it didn't change anything.

The table schema is as follows:

create table EVENT_STORE (

    EVENT_ID bigint not null auto_increment,

    EVENT_TYPE varchar(255) not null,

    EVENT_BODY blob not null,

    OCCURRED_ON timestamp not null,

    STREAM_NAME varchar(255),

    STREAM_VERSION int,

    constraint ID_EVENT_STORE primary key (EVENT_ID),

    constraint SID_EVENT_STORE unique (STREAM_NAME, STREAM_VERSION)

);

alter table EVENT_STORE add constraint CHECK_COEX_STREAM

    check((STREAM_NAME is not null and STREAM_VERSION is not null)

        or (STREAM_NAME is null and STREAM_VERSION is null));

alter table EVENT_STORE add constraint CHECK_DOM_STREAM_NAME

    check(STREAM_NAME <> '');

alter table EVENT_STORE add constraint CHECK_DOM_STREAM_VERSION

    check(STREAM_VERSION > 0);

 The table is accessed concurrently with many inserts being performed. Each insert may consist of multiple rows and they are inserted as bulk.

Here's the full stack trace:

org.springframework.jdbc.UncategorizedSQLException: ; uncategorized SQLException for SQL [insert into EVENT_STORE (EVENT_BODY, EVENT_TYPE, OCCURRED_ON, STREAM_NAME, STREAM_VE
RSION)
values (?, ?, ?, ?, ?)]; SQL state [HYT00]; error code [50200]; Timeout trying to lock table ; SQL statement:
insert into EVENT_STORE (EVENT_BODY, EVENT_TYPE, OCCURRED_ON, STREAM_NAME, STREAM_VERSION)
values (?, ?, ?, ?, ?) [50200-193]; nested exception is org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
insert into EVENT_STORE (EVENT_BODY, EVENT_TYPE, OCCURRED_ON, STREAM_NAME, STREAM_VERSION)
values (?, ?, ?, ?, ?) [50200-193]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.3.7.RELEASE.jar!/
:4.3.7.RELEASE]
        at com.querydsl.sql.spring.SpringExceptionTranslator.translate(SpringExceptionTranslator.java:49) ~[querydsl-sql-spring-4.1.4.jar!/:na]
        at com.querydsl.sql.Configuration.translate(Configuration.java:459) ~[querydsl-sql-4.1.4.jar!/:na]
        at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:445) ~[querydsl-sql-4.1.4.jar!/:na]
        at com.skalup.common.infrastructure.persistence.event.JdbcEventStore.appendToStream(JdbcEventStore.java:132) ~[common-event-1.0.0-SNAPSHOT.jar!/:1.0.0-SNAPSHOT]
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
insert into EVENT_STORE (EVENT_BODY, EVENT_TYPE, OCCURRED_ON, STREAM_NAME, STREAM_VERSION)
values (?, ?, ?, ?, ?) [50200-193]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.193.jar!/:na]
        at org.h2.message.DbException.get(DbException.java:168) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.Command.filterConcurrentUpdate(Command.java:307) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.Command.executeUpdate(Command.java:260) ~[h2-1.4.193.jar!/:na]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:160) ~[h2-1.4.193.jar!/:na]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:146) ~[h2-1.4.193.jar!/:na]
        at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:431) ~[querydsl-sql-4.1.4.jar!/:na]
        ... 24 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "EVENT_STORE": another transaction has updated or deleted the same row [90131-193]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.193.jar!/:na]
        at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.193.jar!/:na]
        at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.193.jar!/:na]
        at org.h2.mvstore.db.MVSecondaryIndex.add(MVSecondaryIndex.java:216) ~[h2-1.4.193.jar!/:na]
        at org.h2.mvstore.db.MVTable.addRow(MVTable.java:704) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.dml.Insert.insertRows(Insert.java:156) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.dml.Insert.update(Insert.java:114) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.CommandContainer.update(CommandContainer.java:98) ~[h2-1.4.193.jar!/:na]
        at org.h2.command.Command.executeUpdate(Command.java:258) ~[h2-1.4.193.jar!/:na]
        ... 27 common frames omitted

I don't understand how it can even happen since rows are only inserted, never updated or deleted.

I hope this isn't a limitation of H2? So far we were using H2 in production with great success. 

Thanks in advance for your help!

[Noel Grandin]

that looks like you have two different sessions putting data into the table in such a way that it violates the the SID_EVENT_STORE constraint, and we are not reporting that very well​

[Marco Willemart]

I understand, so what can be done?

[Noel Grandin]

in the short term, don't violate the constraint?

in the longer term, if you could make us a standalone test case, I could look at throwing a more useful exception

On 17 May 2017 at 15:23, Marco Willemart <marco.w...@gmail.com> wrote:

I understand, so what can be done?

--
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+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Marco Willemart]

This constraint is essential, it provides optimistic concurrency capability for event sourcing.

What I don't understand though, is why H2 tries to lock the table? I thought locking happened at the row level for inserts / updates / deletes.

[Noel Grandin]

it's not locking the table.

you are inserting a row that generates a constraint violation, so it throws an exception.

If I fixed it, the only difference would be the __kind__ of exception you would see.

[Marco Willemart]

Actually it happens in the context of a long running process where the steps are executed concurrently.

Some transactions can span a few minutes, however the inserts are small and fast and happen at the end of each step. 

It happens in production only because we process real data. In dev mode the steps are much faster and we don't have this issue (although there are actually more concurrent inserts since the steps take less time to complete).

I'm note quite sure it is an issue with H2 after all.

I tried to increase the session timeout up to 1 minute but it didn't work. 

However, if I catch the exception and I retry the insert, the next time it works. I do that during the same transaction, I guess the thrown exception does not mean H2 is in an inconsistent state and I should rollback before retrying?

If the retry is ok as far as H2 is concerned, then it would solve my problem and I could keep working with H2 :D