#1473 SELECT...FOR UPDATE locking unexpected behaviour
There is a thread about this that has some background info here:
https://sourceforge.net/p/hsqldb/discussion/73674/thread/4bdce697/
Basically, I'm trying to use SELECT...FOR UPDATE to coordinate access to a shared pool of tasks where each task is represented as a row. The goal is to ensure that each task is picked up by one thread only. Each thread queries for a "pending" task and then, while holding the update lock, changes that task from "pending" to "processing" so that no other "pending" query will return that task.
This method has the expected behaviour on other DBs I've tried but not HSql.
I've attached a test program showing the issue. You can just import it into Eclipse. I tried this program against a SAP HANA database and it works in the expected way.
The issue is with the query. Use of " LIMIT 1 " in the query makes it non-updatable (this follows the SQL standard). After removing the LIMIT the test case runs successfully and prints "Complete!"
I added a reply to the discussion thread making the argument that this should potentially be supported despite the letter of the SQL standard. Any chance this could be reconsidered?
https://sourceforge.net/p/hsqldb/discussion/73674/thread/4bdce697/#419e/55c9