Re: [Proxool-developer] Unable to set status of connection 1 from ACTIVEto AVAILABLE

[Bill H. <bi...@lo...>]

Leon,

On Wed, 2004-05-12 at 23:04, Leon Noordermeer wrote:

> I get a lot of “[WARN] bog - -Unable to set status of connection 1
> from ACTIVEto AVAILABLE. It remains AVAILABLE” warnings 

This sounds like you are doing something worrying with closing
connections and then keeping them to reuse another time without giving
them back to Proxool. Could that be true?

> en it takes a lot of time before my data get’s committed to the
> database.

I'm not sure why that is. But it might become apparent if we explore
what else is happening.

> In a single transaction a couple of tables needs to be updated (about
> 5 of them) so what we do is setting autocommit off and add a number of
> update / insert statements to a connections and commit them at the
> very end. If anything goes wrong, we do a rollback and everything is
> fine J. 

Sounds cool.

> These connections are stored in a HashTable associated by the thread
> (we only use 1 connection for each thread). But when I store a
> connection in a thread an decide to add some statements later, I get
> the waning mentioned in the subject. Why is this? 

Warning bells are going off in my head. Why do you need to store the
connection in a hashtable? Usually I would expect a transaction to be

start
  execute#1
  execute#2
  ....
commit

I think if you can explain why you needed a hashtable and threads then
it will all become clear.

> Is there any way I can resolve this? By changing my code or even
> Proxool? 

You're welcome to do either :)

> I have just multiple connections which all must be performed or none
> at al I thought putting them in a HashTable by thread would be a good
> idea, but is it?

Let's see a bit more of your code and find out. 

In general, my advice would be:

1) Get a connection

2) Do all the things you need to do with it one after the other without
going off and doing other things.

3) Give it back to Proxoool (close it).

4) Don't use it again.

With reference to that last point, the elusive 0.9.0 will introduce
disposable connection wrappers that prohibit you being able to do
anything to a connection after it is closed. I can't wait...

- Bill