MVCC and writers blocking readers

[steve.ebersole]

Using MVCC, should writers cause readers to block? I have a test that
does a select-for-update and then an update on one connection and then
tries a normal select on a second. Is that expected behavior for H2's
MVCC?

For reference the test is
http://fisheye.jboss.org/browse/Hibernate/core/trunk/testsuite/src/test/java/org/hibernate/test/jpa/lock/JPALockTest.java?r=17944#l54

So basically on the first connection we:
1) start a transaction
2) issue a select for update for a specific row
3) do an update to that specific row

(the transaction is still active)

Then on a second connection we:
1) start a transaction
2) perform a normal select of the same row

The normal select on second connection leads to:
16:02:52,608 WARN JDBCExceptionReporter:100 - SQL Error: 50200,
SQLState: HYT00
16:02:52,610 ERROR JDBCExceptionReporter:101 - Timeout trying to lock
table "EJB3_ITEM" [50200-134]
...
Caused by: org.h2.jdbc.JdbcSQLException: Timeout trying to lock table
"EJB3_ITEM" [50200-134]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
316)
at org.h2.message.DbException.get(DbException.java:167)
at org.h2.message.DbException.get(DbException.java:144)
at org.h2.table.RegularTable.doLock(RegularTable.java:464)
at org.h2.table.RegularTable.lock(RegularTable.java:402)
at org.h2.table.TableFilter.lock(TableFilter.java:117)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:542)
at org.h2.command.dml.Query.query(Query.java:233)
at org.h2.command.CommandContainer.query(CommandContainer.java:80)
at org.h2.command.Command.executeQuery(Command.java:132)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:
96)
...

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

> Using MVCC, should writers cause readers to block?

No. Usually not. However, SELECT ... FOR UPDATE does not do what it
"should" do in MVCC mode. This is a known problem, and it's on the
roadmap since quite a long time ("MVCC: select for update should only
lock the selected rows."). Currently, SELECT ... FOR UPDATE locks the
whole table (no matter what) in exclusive mode. That means even
queries are blocked.

I hope I can change that in the next release, but I'm not sure yet.

> Is that expected behavior for H2's MVCC?

It's a known issue.

Regards,
Thomas

[Steve Ebersole]

To be clear this is the same row I am talking about.   So even in that scenario, with this mvcc case fixed, H2 would still block the reader in the second transaction is what I am hearing.  Could you plase confim my understanding is correct?

-- Sent from my Palm Pre

st...@hibernate.org
http://hibernate.org

---

[steve.ebersole]

Came out a little ugly; let me repost the clean up...

To be clear this is the same row I am talking about. So even in that

scenario, with this MVCC case fixed, H2 would still block the reader
in the second transaction is what I am hearing. Could you please
confirm my understanding is correct?

So basically on the first connection we:
1) start a transaction

2) select ... from my_table where id = 1 for update
3) update my_table set ... where id = 1

(the transaction is still active)

Then on a second connection we:
1) start a transaction

2) select ... from my_table where id = 1

Step (2) on the second connection fails with the timeout.

Again, I just want to confirm that this is expected behavior as it is
different from most of the MVCC impls of which I am aware where the
writers are given a special workspace until the changes are merged at
end of transaction specifically to allow readers to proceed (depending
on isolation),

Thanks.

[Thomas Mueller]

Hi,

> H2 would still block the reader
> in the second transaction is what I am hearing.  Could you please
> confirm my understanding is correct?

Yes.

> So basically on the first connection we:
> 1) start a transaction
> 2) select ... from my_table where id = 1 for update
> 3) update my_table set ... where id = 1
> (the transaction is still active)
>
> Then on a second connection we:
> 1) start a transaction
> 2) select ... from my_table where id = 1
>
> Step (2) on the second connection fails with the timeout.

Yes. Even without "update my_table set ... where id = 1". However, if
you replace "select ... from my_table where id = 1 for update" with
"update my_table set id = id where id = 1" then it should be fine (I
didn't test it yet however).

> Again, I just want to confirm that this is expected behavior as it is
> different from most of the MVCC impls of which I am aware

Yes. The problem is that the current implementation in H2, the SELECT
FOR UPDATE doesn't do what it's supposed to do for MVCC: it locks the
table in exclusive mode. This is a known problem since quite a long
time, but I didn't have time to implement it yet. It was just not
important enough so far. Now, one of the top priorities, and it should
be relatively easy to implement (internally replacing the SELECT with
a dummy UPDATE). Maybe it will be implemented in the next release (in
about two weeks), but not sure yet.

Regards,
Thomas