When Infinispan interacts with the CacheLoader/Writer, ISPN does not allow the access to the transaction. This behavior is the same for shared and non-shared CacheLoader/Writer.
As I said, I knew that, already. I even understand why the restriction is in place (see referenced Jira).
My question is specifically about if it could be done if the two conditions are met that the CacheStore is a) transactional and b) shared because I think the reasoning why ISPN-604 was rejected wouldn't apply, then. If it could be done, what extra work would be involved to pull this off?
I don't think this is possible for the general case: the node that writes to the store is the data owner, which might be different than the node where the transaction has originated. I.e. we cannot propagate the transaction context from the tx originator VM to the node that actually writes (at least not easily). It would be simple to do this for local caches, as the tx originator and the the node that writes is the same, but not sure this is good enough for your use case.
Enabling recovery should give you an XA transaction over both the cache and the storage though, arguably with lower performance though (the tx write to the store is a multi operation vs a batch). Doesn't that do the trick for you?
Finally got some more time to look into this:
Regarding my intial question, I interpreted "only the node where the modification originated will write to the cache store" to mean "the node where Cache.put() is called". If it's the node that is elected to be the data owner, I have to agree, it's not necessarily the node where the transaction is running.
My use case is roughly that I want to store some XML documents and a corresponding Lucene index in Infinispan. So I got 4 caches: the XML documents keyed by a business key and a Lucene directory with segments, metadata and locks. The size of XML documents is small (10kb), their number might go into the millions. But at first there will only be a couple of thousands, so one node for data and index is where I am now. Both data and index should be durable, re-indexing on startup is not desirable. I also must be able to deploy into a JEE environment where direct filesystem access is not desired, i.e. this is why I want to use a JDBC CacheStore.
My current test setup consists of Infinispan 6, Lucene 4, Bitronix 3 as the JTA provider and PostgreSQL 9 as the DataSource. To integrate Infinispan and Bitronix I use my own code and not the jar you link to in your docs, because I think each cache represent one XA resource with a lifecycle of its own and recovery should be handled independently for each one.
I'm currently struggling to follow your advice to get Infinispan and the DataSource to participate in the same transaction on one local node. The problem is that Infinispan calls the CacheStore only after it suspends the transaction in its prepare-phase (interesting that even works). So there is no possible way to participate in that transaction via a JDBC CacheStore.
The suspension of the transaction also causes me some headaches. I'm not entirely sure about the control flow but PostgreSQL is telling me that I'm trying to interleave multiple transactions which it doesn't support. Maybe it's because the transaction is suspended in the prepare-phase or it's because I have 4 caches and each one suspends the transaction before it lets its CacheStore write to the (same) DataSource, I don't know.
Another thing I noticed is that the JDBC store does no transaction management whatsoever, i.e. it expects the DataSource not to fail if there is no running JTA transaction and it expects auto-commit to be enabled for the DataSource. If auto-commit is disabled writes to the CacheStore seem to silently do nothing.
To solve my first two problems I'm thinking about configuring the CacheStore as a read-only store and instead use a cache-listener that does the writing to the DataSource. That way I would also be sure that the CacheStore write would be on the same node as the call to Cache.put().
So far, I haven't addressed the third problem because Bitronix can be configured to allow the use of its managed DataSources outside of a JTA transaction and with auto-commit enabled. Ultimately, tough, I want to disable those two options as they can easily cause subtle bugs where you assume something is atomic but really isn't and still works.