SELECT FOR UPDATE doesn't block if executed twice

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

See attached testcase. It performs a SELECT ... FOR UPDATE twice, using the
same connection, performing a COMMIT in between to release the first table
lock. After that, the table should be locked again. But it isn't, because
OPTIMIZE_REUSE_RESULTS is enabled by default. Instead, the second SELECT
only returns the result from the cache, without locking.

What is the expected output? What do you see instead?

A third SELECT on the table should block and bail out with SQLException
saying something like "timeout waiting for lock on table ...". Instead, the
third SELECT on the table doesn't block because the lock has not been set.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 1.1.118 (stable), but happens with 1.2.127 also.
Windows XP SP 2
NTFS
Sun JRE 1.6.0-16

Do you know a workaround?

Yes. Rewrite the SELECT statement so that H2 does not consider it to be
DETERMINISTIC, eg. by adding something like "WHERE rand() >= -1". Results
of nondeterministic statements are not cached.

How important/urgent is the problem for you?

Not important/urgent because we use SELECT FOR UPDATE rarely and we know
the workaround.

In your view, is this a defect or a feature request?

Defect.

Please provide any additional information below.

I think the reason for this defect is in Query.java line 230:

        if (isEverything(ExpressionVisitor.DETERMINISTIC)) {

I think it should recognize the fact that the query might be a FOR UPDATE
query:

        if (!isForUpdate && isEverything(ExpressionVisitor.DETERMINISTIC)) {

This fix switches off result caching for FOR UPDATE queries. A more elegant
solution would be to cache the result, but perform the lock anyway.

Note that we tracked this defect down from an installation of our product
which uses the Quartz scheduler. Quartz uses SELECT FOR UPDATE statements
as a kind of "DB semaphore" to protect and serialize access to various
parts of their scheduling engine. We've seen code blocks run in parallel
that should run serialized, because the SELECT FOR UPDATE didn't block. 

(If you're interested in the details: Quartz Trigger inserts wake up the
scheduler thread immediately. But the scheduler thread cannot see the newly
inserted trigger because it wakes up too early, before the insert
transaction has been committed.)

Original issue reported on code.google.com by dmoeb...@gmx.net on 21 Jan 2010 at 12:55

[GoogleCodeExporter]

Hi

Thanks a lot for your help and the test case! Your analysis is correct,
however I think I will change Select.isEverything(..):
case ExpressionVisitor.DETERMINISTIC: {
  if (isForUpdate) {
    return false;
  }
...

With this change, your test case works. 

Original comment by thomas.t...@gmail.com on 23 Jan 2010 at 6:39

• Changed state: Accepted

[GoogleCodeExporter]

Fixed in version 1.2.128.

Original comment by thomas.t...@gmail.com on 30 Jan 2010 at 2:59

• Changed state: Fixed