How to use Write-behind with foreign keys in Apache Ignite

Question

I am having a problem when trying to use cache entries related to tables that have external key constraints between them. It seems that the write-write mechanism does not perform updates / inserts in a deterministic order, but tries to consistently forward all the collected changes to each cache in some unknown order. But since we have foreign keys in the tables, the order of the operation matters, therefore, you must first insert / update the parent objects, and then only the children (otherwise, because of the foreign key violation, they exit the database).

It seems that the current implementation is trying to solve this problem based on trial and error ( org.apache.ignite.cache.store.jdbc.CacheAbstractJdbcStore:888), which means that it will periodically try to re-update the changes for the caches in case of which a violation of the restriction occurred. Thus, the "child" cache will periodically retry to clear it until the "parent" cache is cleared first. Ultimately, this will lead to the receipt of data in the database, but it also means many unsuccessful attempts in the case of complex hierarchical tables until the correct order is found. This leads to poor performance and unnecessary shelling of the database.

Do you have any suggestions on how I can work around this problem?

(Initially, I was trying to do an end-to-end recording, but this led to very poor performance, because it CacheAbstractJdbcStoreseems to open a new prepared statement for each insert / update operation.)

Answer 1

When writing in the order of updating the store, it is undefined, since each node writes independently and asynchronously. If you have foreign key restrictions, you should use pass-through writing.

As for recording performance, it CacheAbstractJdbcStoreworks with custom DataSource, so it depends on its implementation, regardless of whether it opens a new connection every time or not. If you use some empty version, this will not happen.