We have an application storing data in a local H2 database (file mode). Everything works fine except for a single query that's executed on application shutdown. The query is issued to the H2, but never returns (no exception is thrown).
As far as I know, this only appears on a single workstation (feature still in test not in production).
When using the database of that workstation on my own workstation the application stops right there waiting for the query to return. So with this specific database it is reproducible.
When opening the database in an external tool (DbVisualizer Pro if it matters) and issuing the same query (in particular I used explain analyze <query> to not modify the data in the database) that query runs forever, too. The query looks as follows:
DELETE TOP(1000) FROM my_schema.SOMETABLE ST WHERE ST.someDate < '2019-05-23'
The problem is not directly tied to the shown date as it happend the same yesterday (where the date was 2019-05-22).
Strange thing is, when I stop the query execution and modify the date the query will work as expected (also with explain analyze so no data is modified). If I switch back to the original date it works as well.
When after that "trick" I start the application the query in question works like a charm. So I guess it must have something to do with the actual state of the particular database.
My question is: How to find out whats wrong with the database file?
I've already tried to do this "health check", but this reveals no problems.
Side note: "Running forever" here means I killed the application process after waiting for some 20 minutes, but I guess that time should be enough for deleting 16 of 18 entries in that specific table.
