Cayenne transactions edge cases

[Andrus Adamchik <an...@objectstyle.com>]

Transaction handling is "fun". I am really glad our users don't have to deal with transactions directly 99.9% of the time. There are a number of edge cases where Cayenne can't make it fully transparent though. The most typical one is when multiple DB operations overlap. E.g.:

1. Operations within callbacks and listeners.
2. Operations within an iterated result scope with a single DataSource.
3. Operations within an iterated result scope with multiple DataSources (a typical use case with LinkMove).

Our single thread-bound transaction mechanism often falls short in these cases, as it has to pick a single default behavior out of many possible options. Just yesterday I had to deal with #3 scenario (in LinkMove transactions for source and target DataSources were stepping on each other .. you've probably seen the Jiras). I had to unbind the transaction of the iterated result object from the thread, so that ops executed within the iterator would not try to attach to it. This solved conflicts arising in #3 scenario (transactions against 2 DataSources were stepping on each other), and hopefully this is a good default.

Still I feel we'll need more flexibility in a general case. Overlapping operations means your code (not just Cayenne code) is executed inside a transaction and you suddenly start to care about isolation levels, commit/rollback scope and other fun staff. I am not suggesting any solutions yet. Just thought that I should mention the problem. Hopefully even in these advanced cases we can stay true to Cayenne "you don't have to care about transactions because we do it for you" motto :)

Andrus

Re: Cayenne transactions edge cases

[Andrus Adamchik <an...@objectstyle.com>]

BTW, just came across a very typical use case when someone needs to tweak transaction isolation level. Somehow we had a hole in the new TX API that didn't make it easy to accomplish. So I just added new TransactionListener API [1] that makes managing transaction connections more easy. In a way this is a recast of the old TransactionDelegate. But the delegate was in the wrong scope and was extremely hard to use.

ServerRuntime runtime = ...

TransactionListener listener = new TransactionListener() {
   ...
   public void willAddConnection(Transaction tx, String connectionName, Connection connection) {
        connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
   }
};

List<MyObject> result = runtime.performInTransaction(
	() -> { ObjectSelect.query(MyObject.class).select(runtime.newContext()) }, 
	listener);

Andrus

[1] https://issues.apache.org/jira/browse/CAY-2112


> On Sep 6, 2016, at 6:06 AM, Andrus Adamchik <an...@objectstyle.com> wrote:
> 
> Transaction handling is "fun". I am really glad our users don't have to deal with transactions directly 99.9% of the time. There are a number of edge cases where Cayenne can't make it fully transparent though. The most typical one is when multiple DB operations overlap. E.g.:
> 
> 1. Operations within callbacks and listeners.
> 2. Operations within an iterated result scope with a single DataSource.
> 3. Operations within an iterated result scope with multiple DataSources (a typical use case with LinkMove).
> 
> Our single thread-bound transaction mechanism often falls short in these cases, as it has to pick a single default behavior out of many possible options. Just yesterday I had to deal with #3 scenario (in LinkMove transactions for source and target DataSources were stepping on each other .. you've probably seen the Jiras). I had to unbind the transaction of the iterated result object from the thread, so that ops executed within the iterator would not try to attach to it. This solved conflicts arising in #3 scenario (transactions against 2 DataSources were stepping on each other), and hopefully this is a good default.
> 
> Still I feel we'll need more flexibility in a general case. Overlapping operations means your code (not just Cayenne code) is executed inside a transaction and you suddenly start to care about isolation levels, commit/rollback scope and other fun staff. I am not suggesting any solutions yet. Just thought that I should mention the problem. Hopefully even in these advanced cases we can stay true to Cayenne "you don't have to care about transactions because we do it for you" motto :)
> 
> Andrus