#1734 joins and for-update

[Steve Ebersole]

Not sure if this is a bug or not, but I could not find an answer anywhere as to the expectation. Should for-update applied to a query with join locked the joined table?

E.g. in a transaction, T1, I have a query like -

select ...
from books b
   join book_tags t on ...
where ...
for update

while T1 is still active, in another transaction, T2, I execute the query -

delete from book_tags

The delete done in T2 succeeds. Is that expected? I know the documentation talks about for-update simply affecting the cursor updateability, but I have seen discussions here that within a transaction it also holds locks on the tables for update. But that has not been my experience.

FWIW, I have also tried -

select ...
from books b
   join book_tags t on ...
where ...
for update of id, book_fk

Which also does not lock the book_tags table.

Thanks!

P.S. This is with version 2.7.4 if that matters.
P.S.S. With the for-update-of form, how are "duplicate" column names handled? Once I get the main concern figured out I need to play with that as well.

[Fred Toussi]

General locking behavior depends on the transaction control setting for the database (LOCKS or MVCC). Please state which one is being used. I will check the issue in detail in a couple of days.

[Steve Ebersole]

Since it is not specified, afaik the default (2PL/LOCKS) should apply - https://hsqldb.org/doc/guide/sessions-chapt.html#snc_tx_tx_cc

However, I did not see anything obvious there about the impact, if any, of for-update. Can I assume then that with LOCKS for-update only acquires shared locks?

Assuming so, another option would possibly be something mentioned in that above doc -

HyperSQL also supports explicit locking of a group of tables for the duration of the current transaction. Use of this command blocks access to the locked tables by other sessions and ensures the current session can complete the intended reads and writes on the locked tables.

However, it never mentions what "this command" is. Any pointers? Does this approach lock the entire table, or is there a way (given LOCKS mode) to acquire exclusive locks on just some rows?

Also, given a JDBC Connection or DatabaseMetaData, is there a way to tell which concurrency control model is in effect?

Thanks Fred!

[Fred Toussi]

I will check the FOR UPDATE behavior next week and will also provide more information on the limitations.

Re explicit locking, see the same chapter of the guide under < lock table statement >. It goes like LOCK TABLE books WRITE, book_tags READ

For current isolation level use the java.sql.Connection method, getTransactionIsolation()

For database transaction control, use the query below:

select property_value from information_schema.system_properties where property_name = 'hsqldb.tx'

Thanks Steve!

[Fred Toussi]

The issue here is that HSQLDB does not support queries with joined tables as updatable. Another issue is that the FOR UPDATE clause is accepted and ignored even when the query is not updatable.

The following types of query are updatable

  select * from books b where book_name = 'A BOOK' for update 
  select * from books b where id in (select book_fk from book_tags) for update

In the second query, the table books has a write lock and the table book_tags has a read lock.

[Steve Ebersole]

So you are saying that given that joined for-update query, even delete from books won't be blocked?

[Fred Toussi]

The FOR UPDATE clause is actually ignored in a joined query. The query is read-only, with read locks on the two tables.
In my second example query, the query is updatable and there is also a read lock on book_tags.
In the default READ COMMITTED isolation mode these read locks are immediately removed after each execution. But in SERIALIZABLE (and REPEATABLE READ) isolation mode the read locks are retained until commit or rollback. So a DELETE statement will be blocked.