FWD » Core Development » Database

In H2 using the PageStore engine, writes acquire locks on database pages or tables. In a highly concurrent environment, this coarse-grained locking can cause many operations to block or timeout, making PageStore unsuitable (in it's current form) for write-heavy workloads.

Important note:
H2 has a general invariant - the lock must be on the database instance, this is expected throughout the project and is often checked, if there is no exclusive lock on the database, an error is thrown. As a result we cannot synchronize the lock acquiring on anything else other than the database.

After extensive analysis and trial and error, modifying the H2 PageStore locking system is proving extremely complex. The current implementation relies on a tightly coupled set of locks throughout the codebase, and any change in PageStoreTable triggers ripple effects across multiple modules, making it risky and costly.

• Switch to MVStore, which handles concurrency more robustly and avoids these locking complications.
• Leave the current PageStore implementation as-is, accepting the existing locking behavior and working around it with batch CRUD operations.

Switch to MVStore, which handles concurrency more robustly and avoids these locking complications.

If this is preferred, then I recommend integrating the newest H2 for this case and rename the package of internal H2 to fwdh2. This way, we can use 2 H2 dependencies that ultimately use different stores.

Leave the current PageStore implementation as-is, accepting the existing locking behavior and working around it with batch CRUD operations.

• weak consistency as there aren't many constraints to ensure and very very few unique ones. The locking is meant to handle unique or other transitive violations, but this won't be the usual case in 4GL.
• weak durability as the data is purged on database restart.
• weak isolation as most statements should be auto-committed.

In other words, I would think that not locking at all will be closer to the _meta database behavior than locking. There are very few cases in which we need to ensure that operations are happening synchronous, but most meta records are independent and session should just insert/delete/update their own rows without much conflict with others.

This is why I thinking that having a special connection parameter towards H2 might fix this. There is LOCK_MODE, but it may be quite tricky to adapt to our need. I would envision a SOFT_LOCK parameter. With this active, we will enforce auto-commit (maybe) and ensure the operations are not blocking. The only blocking data structures should be the maps and lists used by the indexes, while the DB engine should be straight-forward. I don't have any technical details for implementation, but from my POV it is a complete overhead to gain a lock on a table to insert a new record while most certainly it won't conflict the concurrent others.

Maybe it would make sense to think of AUTO-COMMIT like a session that modifies the DB baseline version directly (without a new transaction, locks and commit).

Setting a certain lock mode for certain h2 databases is a reasonable solution, anything that involves changing the overly engineered locking system in h2 is not a good idea, as I said there are invariants throughout the project that will be broken if we try to tinker with the locks.

Related to MVStore, yes it also uses locks in exclusive mode, but the locks are much more granular and are held for less time, from my experiments it is orders of magnitude faster for concurrent CRUD operations.

For testing I am using a script that starts a number of concurrent batch clients that connect to the database.
With page store, I am able to hit the h2 locking timeout with 100 concurrent clients.
With mv store there is another issue, a recid unique violation:

26/03/26 17:33:27.878+0200 |  SEVERE | com.goldencode.p2j.persist.meta.ConnectTableUpdater | ThreadName:Conversation [00000020:bogus], Session:00000032, ThreadId:00000054, User:bogus | Error inserting into _connect/_myconnection table for local/fwd/meta (51)
com.goldencode.p2j.persist.PersistenceException: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.META_MYCONNECTION_(RECID) [10053, 11, 1, 328947, 0, 0, 0]"; SQL statement:
SELECT * FROM FINAL TABLE(INSERT INTO meta_myconnection_ VALUES (?, ?, ?, ?, ?, ?, ?)) [23505-200]
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.META_MYCONNECTION_(RECID) [10053, 11, 1, 328947, 0, 0, 0]"; SQL statement:
SELECT * FROM FINAL TABLE(INSERT INTO meta_myconnection_ VALUES (?, ?, ?, ?, ?, ?, ?)) [23505-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
    at org.h2.message.DbException.get(DbException.java:205)
    at org.h2.message.DbException.get(DbException.java:181)
    at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:127)
    at org.h2.index.Index.add(Index.java:58)
    at org.h2.mvstore.db.MVTable.addRow(MVTable.java:536)
    at org.h2.command.dml.Insert.insertRows(Insert.java:195)
    at org.h2.command.dml.Insert.update(Insert.java:151)
    at org.h2.table.DataChangeDeltaTable.getResult(DataChangeDeltaTable.java:89)
    at org.h2.index.VirtualConstructedTableIndex.find(VirtualConstructedTableIndex.java:38)
    at org.h2.index.BaseIndex.find(BaseIndex.java:148)
    at org.h2.index.Index.find(Index.java:111)
    at org.h2.index.IndexCursor.find(IndexCursor.java:169)
    at org.h2.index.IndexCursor.find(IndexCursor.java:155)
    at org.h2.table.TableFilter.next(TableFilter.java:498)
    at org.h2.command.dml.Select$LazyResultQueryFlat.fetchNextRow(Select.java:2021)
    at org.h2.result.LazyResult.hasNext(LazyResult.java:154)
    at org.h2.result.LazyResult.next(LazyResult.java:70)
    at org.h2.command.dml.Select.queryFlat(Select.java:840)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:954)
    at org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:212)
    at org.h2.command.dml.Query.query(Query.java:508)
    at org.h2.command.dml.Query.query(Query.java:470)
    at org.h2.command.CommandContainer.query(CommandContainer.java:254)
    at org.h2.command.Command.executeQuery(Command.java:195)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:124)
    at com.goldencode.p2j.persist.h2.UpdatableMyConnectionView.fire(UpdatableMyConnectionView.java:191)
    at org.h2.tools.TriggerAdapter.fire(TriggerAdapter.java:144)
    at org.h2.schema.TriggerObject.fireRow(TriggerObject.java:261)
    at org.h2.table.Table.fireRow(Table.java:1372)
    at org.h2.table.Table.fireBeforeRow(Table.java:1251)
    at org.h2.command.dml.Insert.insertRows(Insert.java:191)
    at org.h2.command.dml.Insert.update(Insert.java:151)
    at org.h2.command.CommandContainer.update(CommandContainer.java:167)
    at org.h2.command.Command.executeUpdate(Command.java:251)
    at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:250)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:1216)
    at com.goldencode.p2j.persist.orm.SQLExecutor.executeImpl(SQLExecutor.java:382)
    at com.goldencode.p2j.persist.orm.SQLExecutor.execute(SQLExecutor.java:304)
    at com.goldencode.p2j.persist.orm.SQLExecutor.execute(SQLExecutor.java:147)
    at com.goldencode.p2j.persist.orm.Persister.insert(Persister.java:1048)
    at com.goldencode.p2j.persist.orm.Persister.insert(Persister.java:491)
    at com.goldencode.p2j.persist.orm.Session.save(Session.java:959)
    at com.goldencode.p2j.persist.meta.ConnectTableUpdater.databaseConnected(ConnectTableUpdater.java:404)

The problem is the pattern used in databaseConnected, currently id does select -> insert/update and assumes (quote from the comments):

we don't do any pessimistic locking here because the records affected are specific to the current
user session, thus no other session touches them

The 11291a branch is obsolete after 11282a was merged. About the MERGE statements - in H2 these lock the table for each record operation (INSERT or UPDATE), so there is not a lock/merge/unlock mode. I don't know if this can help.

I think this task and #11356 are required if we want to switch to MVStore. At the least, we need to document all meta tables used in FWD and how do create/update/delete is done on them; is it synchronized? is it postponed or on each event?

Because IMO we need to do both: synchronize the write access and postpone it.

MVStore is great because of the superior lock acquiring algorithm, but if we synchronize all the access to meta in FWD, what is the benefit of MVStore?

Artur Școlnic wrote:

MVStore is great because of the superior lock acquiring algorithm, but if we synchronize all the access to meta in FWD, what is the benefit of MVStore?

Well, don't we get unique index violations and maybe other problems otherwise? As we did for meta_connect until we synchronized the methods, before 11282a was released?

Constantin Asofiei wrote:

Well, don't we get unique index violations and maybe other problems otherwise? As we did for meta_connect until we synchronized the methods, before 11282a was released?

With MVStore, we could get it for other tables, but i did not see it yet. But why use MVStore in the first place since now after we do the locking/synchronization in FWD?

Artur Școlnic wrote:

Constantin Asofiei wrote:

Well, don't we get unique index violations and maybe other problems otherwise? As we did for meta_connect until we synchronized the methods, before 11282a was released?

With MVStore, we could get it for other tables, but i did not see it yet. But why use MVStore in the first place since now after we do the locking/synchronization in FWD?

Please run some tests in PageStore and find the limit for SELECT in H2 (share-lock in doLock1 is being used for read...).

IMO, #11356 makes this task obsolete. We cannot change the locking system in PageStore (internal architecture limitations), and MVStore requires synchronization in FWD and at that point it does not make a difference what we use, might as well keep PageStore. I also tried PageStore with locking mode 0 and saw no difference in performance.