While working in a project which is based on a H2 database, we experienced some strange behavior after checking the behavior of the database.
- The application uses a H2 file and keeps connected while open.
- In addition using a utility like DBeaver it’s possible to connect to the same database and check the data.
Problem is that two connection were made to the same file and the jdbc driver couldn’t rollback some transaction because the other connected instance locked the file… and boom corrupted state.
H2 has a quite clever way to solve this.
All instances trying to connect should use the AUTO_SERVER in order to connect to a server instead of straight to the .db file, this way the concurrency is orchestrated by this server.