auto commit

[Daryl Stultz]

Hi Rob,

The last time I upgraded Ebean was a year ago. I'm thinking it's time to do it again. I was browsing the issues and I saw this one:

https://github.com/ebean-orm/avaje-ebeanorm/issues/204

If you recall I originally asked for the feature in 2.x then discovered some JDBC integration issues around transactions. I then asked for it to be removed which you did with issue #163. Then a couple of months later you put it back with #204. I don't see any discussion attached to the issue and I'm wondering what the rationale is for this. While I was on 2.8 I had to work off my own build because I had to remove this feature.

I'm also wondering if issue #168 has anything to do with this. It kind of seems like the behavior is configurable but I don't really see it in the code (JdbcTransaction).

Thanks.

/Daryl

[Rob Bygrave]

There is a AutoCommitTransactionManager and AutoCommitJdbcTransaction.  If ServerConfig.isAutoCommitMode() is true ... then these are used (as per InternalConfiguration line 158).

That said, I'm rusty on this issue. I wonder if that revert in JdbcTransaction occurred because AutoCommitJdbcTransaction was supposed to deal with it (but perhaps doesn't).  Looking at the code ... AutoCommitJdbcTransaction doesn't really do what I expect in that ... it's call to super means JdbcTransaction is going to turn off autocommit and I think that is what we are trying to avoid here.

There is a TestAutoCommitDataSource test that runs/passes but maybe doesn't assert things it needs to. 

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rob Bygrave]

That is ... in JdbcTransaction we have:

      if (connection != null) {

        this.autoCommit = connection.getAutoCommit();

        if (this.autoCommit) {

          connection.setAutoCommit(false);

        }

      }

I'd expect this to be a protected method that is overridden by AutoCommitJdbcTransaction (which bypasses that check) ... but that isn't the case.

[Daryl Stultz]

On Wed, Aug 19, 2015 at 4:33 PM, Rob Bygrave <robin....@gmail.com> wrote:

That is ... in JdbcTransaction we have:

      if (connection != null) {

        this.autoCommit = connection.getAutoCommit();

        if (this.autoCommit) {

          connection.setAutoCommit(false);

        }

      }

I'd expect this to be a protected method that is overridden by AutoCommitJdbcTransaction (which bypasses that check) ... but that isn't the case.

The challenge I face is integrating transactions across JDBC, Ebean, and OpenJPA. My JDBC stuff has always been auto commit true, and I think JPA wants it true as well. If I'm not already in a JDBC connection, I give the connection to Ebean with auto commit false, but if I'm in a JDBC transaction, I give it to Ebean with auto commit true. Now this test is failing (was working on 4.1.8):

conn.executeUpdate("begin"); // JDBC connection, same as that given to Ebean and JPA
EntityManager em = ExecutionResources.getEntityManager();
em.getTransaction().begin();
EbeanServer ebs = ExecutionResources.getEbeanServer();
assertTrue(conn.getAutoCommit());
ebs.beginTransaction();
assertTrue(ebs.currentTransaction().getConnection().getAutoCommit()); // fails

So Ebean now flips it to false which is new behavior. I have set ebean.autoCommitMode=true and verified it in ServerConfig in the debugger.

Is this expected?

(Interestingly, if I take out the assertion on the autocommit that is failing, the rest of the unit test assertions pass, so I'm not really sure if there's a problem, I'm just reluctant to change the behavior.)

Thanks.

/Daryl

[Rob Bygrave]

> So Ebean now flips it to false which is new behavior.

It looks like a bug to me. Specifically that code above in JdbcTransaction (which checks autoCommit and then sets it to false) needs to be put into a protected method in JdbcTransaction ... and then overridden in AutoCommitJdbcTransaction which should just leave the jdbc connection in autocommit true mode.

I can make the change in Ebean head/master and you can try that?

[Rob Bygrave]

FYI: I have modified the TestAutoCommitDataSource and got it to break as I was expecting using both Postgres and H2.  

I should be able to sort the fix and confirm it.

[Rob Bygrave]

Logged as https://github.com/ebean-orm/avaje-ebeanorm/issues/392 ... and now fixed.

I fixed up the test and it has much better asserts etc. I'll be rolling out the next release in a day or so (if you don't want to build it yourself).

Cheers, Rob.

[Daryl Stultz]

On Thu, Aug 20, 2015 at 6:41 AM, Rob Bygrave <robin....@gmail.com> wrote:

Logged as https://github.com/ebean-orm/avaje-ebeanorm/issues/392 ... and now fixed.

I fixed up the test and it has much better asserts etc. I'll be rolling out the next release in a day or so (if you don't want to build it yourself).

Yes, I don't want to build it myself, but don't hurry, I'd like to get the current 6.x running first, see if I run in to any other issues. (I have a small tweak I'll send in a new thread.)

I did this transaction experimentation on 4.3.1 as it's before the configuration API changes. I use GlobalProperties to set things up so I need to get off of that. I also ran into an odd AbstractMethodException while Ebean was trying to configure plugins or something, I might need help with that.

Thanks.

/Daryl

[Rob Bygrave]

> AbstractMethodException while Ebean was trying to configure plugins or something

No problem.  Still on Java 6 right?  (There was a build that jdk 7 specific api calls got included in accidentally - I can't remember which build that was)

--

[Daryl Stultz]

On Thu, Aug 20, 2015 at 6:41 AM, Rob Bygrave <robin....@gmail.com> wrote:

Logged as https://github.com/ebean-orm/avaje-ebeanorm/issues/392 ... and now fixed.

I fixed up the test and it has much better asserts etc. I'll be rolling out the next release in a day or so (if you don't want to build it yourself).

Hi Rob, I want to make sure I understand what AutoCommitJdbcTransaction is for (and what it's not). I have a ton of JDBC code and JPA code that precedes our adoption of Ebean. Both require or are built around autoCommit being true. I was fudging things in my DataSource by flipping autoCommit to false before giving the connection to Ebean and flipping it back when it's returned. Except... when I've already got a transaction opened in JDBC, then I'd leave it as autoCommit true.

AutoCommitJdbcTransaction simply does nothing which means ebeanServer.beginTransaction() and ebeanServer.commitTransaction() have no effect, transactions have to be managed externally. Is that the intended pattern? In autoCommitMode true, Ebean does no transaction management? I'm not presently sure if this is a challenge for me or a good new pattern. When I mix JDBC transactions with Ebean, it seems good that I should manage the transaction entirely in JDBC (on the connection itself). But I do have places where I have Ebean transactions alone (no mixing with JDBC and JPA) and these place will no longer offer any protection. I would need to replace ebeanServer.beginTransaction() and ebeanServer.commitTransaction() with my JDBC transaction management. I don't think this would be a huge problem, my team would just have to learn the pattern going forward.

I guess I'm just wondering why Ebean can't open a transaction in autoCommit true mode. There's no "beginTransaction()" method on Connection, we just execute a query "begin". Then we execute "commit" or "rollback" (for a stupid reason we don't call commit() or rollback()). Honestly if Ebean did start transactions in autoCommitMode true it might make some things more difficult for me. So six of one...

Thanks.

/Daryl

[Rob Bygrave]

>  AutoCommitJdbcTransaction is for 

AutoCommitJdbcTransaction expects that the DataSource provides connections with autoCommit = true ... AND that is what is desired (that the connection stays in autoCommit=true while Ebean is using it - that Ebean should not turn that off).  That means is that commitTransaction() should not do anything because successful statements are already committed (and if it tries to commit it gets an error etc).

> or a good new pattern

It is not supposed to be a new pattern in that it should be the same as 4.1.3 - #168 from July 2014.  So it would be new if you didn't use #416 (but I thought you were using this feature from around that time).

> I'm just wondering why Ebean can't open a transaction in autoCommit true mode.

I'm thinking this means that sometimes the DataSource is giving Ebean connections with autoCommit=false and hence it isn't working?  I think I need to confirm a few things:

- Did you previously use #168 (so configure serverConfig.setAutoCommitMode(true) with 4.1.6 or above)

- Are the connections sometimes autoCommit=true and sometimes autoCommit=false

Note that we can create a test case with the scenarios you are dealing with and that might help (add more scenarios to TestAutoCommitDataSource).

There is not supposed to be a new pattern here though - it should be the same as Ebean 4.1.3.

Cheers, Rob.

--

[Daryl Stultz]

On Wed, Sep 2, 2015 at 3:32 PM, Rob Bygrave <robin....@gmail.com> wrote:

>  AutoCommitJdbcTransaction is for 

AutoCommitJdbcTransaction expects that the DataSource provides connections with autoCommit = true ... AND that is what is desired (that the connection stays in autoCommit=true while Ebean is using it - that Ebean should not turn that off).  That means is that commitTransaction() should not do anything because successful statements are already committed (and if it tries to commit it gets an error etc).

Successful statements are committed only when you are not in a transaction. Consider this:

connection.executeUpdate("begin"); // a wrapper that creates a Statement and executes the query

connection.executeUpdate("delete from Users");

connection.executeUpdate("rollback");

The connection is in autoCommit true the whole time but the delete is not committed as a transaction is opened and rolled back. This is how all the JDBC stuff we have works. (This may be an odd pattern but I was essentially totally inexperienced when I wrote it.)

> or a good new pattern

It is not supposed to be a new pattern in that it should be the same as 4.1.3 - #168 from July 2014. 

I'm upgrading from 4.1.8 which has #168, but I didn't notice it and haven't been using it until now.

 

So it would be new if you didn't use #416 (but I thought you were using this feature from around that time).

Eh, what's #416? 

> I'm just wondering why Ebean can't open a transaction in autoCommit true mode.

I'm thinking this means that sometimes the DataSource is giving Ebean connections with autoCommit=false and hence it isn't working?  I think I need to confirm a few things:

- Did you previously use #168 (so configure serverConfig.setAutoCommitMode(true) with 4.1.6 or above)

No.

 

- Are the connections sometimes autoCommit=true and sometimes autoCommit=false

Yes, in my current production code, not in my experimental code (always true). In my production code the connection is auto commit false except when I've started a transaction using the above technique, then it's true.

I'm not sure I've made my situation clear enough. Let me know if the code at the top helps. I don't know if Ebean 6 is behaving the way you intended, if it is hopefully I can keep the old trickiness. I'd like to run a bit more standard if I could which might keep me from having to revisit transaction management every so often.

/Daryl

[Rob Bygrave]

> connection.executeUpdate("rollback");

Ah right.


> Eh, what's #416?

A typo / brain fart. I meant #168 (in version 4.1.6) and somehow I typed #416 instead so yes - that should instead be #168.


> Did you previously use #168 - No

Right. That makes sense.

Ok, I'm going to 'jump' to a thought that ... if AutoCommitJdbcTransaction was changed such that:
-  beginTransaction() was connection.executeUpdate("begin");
- commitTransaction() was connection.executeUpdate("commit");
- rollbackTransaction() was connection.executeUpdate("rollback");

... then that might be close to what you are wanting/expecting.  In that case the connection is in autoCommit=true mode but we still want/expect transaction demarcation (and we get that via using the explicit executeUpdate("begin"); etc).

Is that closer to what you are thinking?  (My previous interpretation was that with autoCommitMode=true we would allow the statements to commit but I read the situation as that actually that was never your case but instead the transaction demarcation was explicit via executeUpdate("begin"); etc.)

Cheers, Rob.

--

[Rob Bygrave]

> When I mix JDBC transactions with Ebean

Does the code use Ebean's ExternalJdbcTransaction when it does this or some other approach?  

  Connection connection = ...

  // do some raw JDBC stuff

  // use Ebean with the same connection

  Transaction txn = new ExternalJdbcTransaction(connection);

  ebeanServer.save(someBean, txn);

 

[Daryl Stultz]

On Wed, Sep 2, 2015 at 4:14 PM, Rob Bygrave <robin....@gmail.com> wrote:

Ok, I'm going to 'jump' to a thought that ... if AutoCommitJdbcTransaction was changed such that:
-  beginTransaction() was connection.executeUpdate("begin");
- commitTransaction() was connection.executeUpdate("commit");
- rollbackTransaction() was connection.executeUpdate("rollback");

... then that might be close to what you are wanting/expecting.  In that case the connection is in autoCommit=true mode but we still want/expect transaction demarcation (and we get that via using the explicit executeUpdate("begin"); etc).

Is that closer to what you are thinking?

I think that is exactly what I would expect. Ebean can work with all autoCommit true or all autoCommit false and the (external) behavior is the same.

I probably shouldn't have mentioned executeUpdate("commit") and executeUpdate("rollback"), I think you just use Connection.commit() and rollback(). As far as "begin" goes, both "begin" and "begin work" work for Postgresql, I have no idea if this applies to other database engines. Might need a DB Dictionary for it... And of course my connection.executeUpdate(...) is a convenience method for creating a statement and executing the query. I'm sure you can handle that, I just want to make my code samples understandable by others that come across this.

> When I mix JDBC transactions with Ebean

 

Does the code use Ebean's ExternalJdbcTransaction when it does this or some other approach?  
  Connection connection = ...
  // do some raw JDBC stuff
  // use Ebean with the same connection
  Transaction txn = new ExternalJdbcTransaction(connection);
  ebeanServer.save(someBean, txn);

No, I started to look at this approach but it was too complex for the time I had.

Thanks.

/Daryl

[Daryl Stultz]

On Wed, Sep 2, 2015 at 7:57 PM, Daryl Stultz <kungfum...@gmail.com> wrote:

I think that is exactly what I would expect. Ebean can work with all autoCommit true or all autoCommit false and the (external) behavior is the same.

I don't know the motivation for AutoCommitJdbcTransaction, so I don't want anybody to be surprised (screwed!) by the new behavior if you elect to do things my way.

/Daryl

[Rob Bygrave]

Yes, I can't remember the motivation (who prompted that) and it seems they are not reading this conversation at the moment (but I remember looking into the performance implications of running with autocommit=true and per statement commits etc).

I'm thinking we want a similar but different implementation.  Need a good name for it.

[Rob Bygrave]

Right, I dropped the ball on this - logged it just now as https://github.com/ebean-orm/avaje-ebeanorm/issues/415

Cheers, Rob.

[Daryl Stultz]

On Thu, Sep 17, 2015 at 6:59 PM, Rob Bygrave <robin....@gmail.com> wrote:

Right, I dropped the ball on this - logged it just now as https://github.com/ebean-orm/avaje-ebeanorm/issues/415

Thanks, I think this is the only thing keeping my from upgrading.

/Daeyl

[Rob Bygrave]

Ok, that is in master.  Note that Postgres wanted the explicit statements for commit and rollback (could not use connection.commit() or connection.rollback() when the underlying connection was autocommit=true with postgres - it gives an error).

Also note, H2 was happy but Oracle not - this feature is restricted to Postgres and H2 use only really.

I'll be releasing the next version (with this in it) in the next day or so.

Cheers, Rob.

[Daryl Stultz]

On Fri, Sep 18, 2015 at 6:31 AM, Rob Bygrave <robin....@gmail.com> wrote:

Ok, that is in master.  Note that Postgres wanted the explicit statements for commit and rollback (could not use connection.commit() or connection.rollback() when the underlying connection was autocommit=true with postgres - it gives an error).

Ah ha! THAT'S why I use executeUpdate("commit" / "rollback")! I knew there had to be a good reason...

Thanks.

/Daryl