Timeout trying to lock table: Caused by .. Concurrent update in table "MY_TABLE": another transaction has updated or deleted the same row

[Martin Lichtin]

We're sometimes seeing this exception when doing SELECT .. FOR UPDATE concurrently .

Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table ; SQL statement:
SELECT * FROM SA.MY_TABLE WHERE MY_ID = ? FOR UPDATE [50200-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.command.Command.filterConcurrentUpdate(Command.java:307)
    at org.h2.command.Command.executeQuery(Command.java:203)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:320)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:159)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.h2.jdbc.JdbcSQLException: Concurrent update in table "MY_TABLE": another transaction has updated or deleted the same row [90131-191]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.table.RegularTable.removeRow(RegularTable.java:374)
    at org.h2.table.TableFilter.lockRows(TableFilter.java:1147)
    at org.h2.command.dml.Select.queryFlat(Select.java:565)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:654)
    at org.h2.command.dml.Query.query(Query.java:341)
    at org.h2.command.dml.Query.query(Query.java:309)
    at org.h2.command.dml.Query.query(Query.java:36)
    at org.h2.command.CommandContainer.query(CommandContainer.java:110)
    at org.h2.command.Command.executeQuery(Command.java:201)
    ... 3 more

    at org.h2.engine.SessionRemote.done(SessionRemote.java:624)
    at org.h2.command.CommandRemote.executeQuery(CommandRemote.java:158)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:110)

What's a bit confusing, is this a really a timeout situation, or something different as the 'caused by' exception would indicate?

[Thomas Mueller Graf]

Hi,

On a concurrent update, the statement (just the statement, not the whole transaction) is rolled back and re-run. This is done a few times, and after some time (the configured timeout), the "timeout" exception is thrown.

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

[Martin Lichtin]

Ok, but there should be no concurrent updates in my use case.
I'm using SELECT ... FOR UPDATE to lock a row, then UPDATE the row, then COMMIT the transaction, at which point another ...FOR UPDATE session gets access to the row.