IS 4.6 now appears to need db COMMIT

, ,
1

We upgraded from 4.0.2 to 4.6 on Solaris 2.8 and now we’ve noticed that a set of services that insert into the oracle db are not working. After some testing we found out that if we put a commit after the insert and before the close statement, then the row will show up in the database.

Now the problem is that the first time the commit was executed, it commited rows for the previous 3 runs of the services. And each run had it’s own connect → insert → close sequence.

So what appears to happen is that the transaction is across the Alias and not the database connection!

Anybody else seen this?

2

I suppose you activated the connection pooling (server.cnf: watt.server.db.connectionCache=server). In this case you need to work carefully with you db connections. I can give you some guidelines:

  • pub.db:connect will return a free connection from the pool, but not necessarily a new one; you need to clear it (pub.db:clearTransaction);
  • If you decide to work with transactions (pub.db:startTransaction) be sure to commit (pub.db:commit) or rollback (pub.db:rollback) always before releasing that connection. If you don�t, some other flow will get the same connection and you will have the behavior that you described.
  • Close all the connections.
  • Use try-catch sequences (flow steps) for your db transactions and commit/rollback and close the connection in the �catch� sequence.
  • IS_4-6_SP1 adds some new features for the connection pooling that can help.
3

I’m stunned! I don’t see anything in the database pooling documentation that suggests the default of auto commit is turned off with server based pooling. Auto commit is on with session based pooling and off with server based pooling? That’s a major bug as far as I’m concerned.

I understand start transaction. That is far taking control of the commit boundary. A connect, insert, close sequence should still commit the transaction - even with server based pooling. Basically, implementing server based pooling will break all existing DB integrations. Developers assume auto commit will work.

Closing connections is not enough.

I’m stunned!