[Fwd: Re: [enhydra] patch to reenable the support for maxWaitingConnections]

[João Paulo Ribeiro]

Hi.

I'm glade to see that my patch was applied.
But i had no feedback about DODS not releasing database locks/resources every time a connection is put back the pool.

This situation is critical for people using Postgresql and may be other databases (Oracle?) in production environment.

I resend my previous email in hope someone can give feedback about this issue.

Best regards.
João Paulo Ribeiro

PS: Many thanks to the dev team for one more important milestone with the 7.0 version almost ready.
Keep the good work! :)

-------- Original Message --------

Subject:	Re: [enhydra] patch to reenable the support for maxWaitingConnections
Date:	Mon, 03 Apr 2006 10:31:31 +0100
From:	João Paulo Ribeiro jp@...
Reply-To:	enhydra@...
To:	enhydra@...
References:	sympa.1142026415.32375.282@... 44154459.3060109@... 442BF34F.1010706@... 1143731669.8848.41.camel@...

I had some doubt about postgresql behaviour being correct and i went the postgresql jdbc mailing list.

>From the discussion:

"The JDBC 4.0 public draft spec says:

The default is for auto-commit mode to be enabled when the Connection object is created. If the value of auto-commit is changed in the middle of a transaction, the current transaction is committed. If setAutoCommit is called and the value for auto-commit is not changed from its current value, it is treated as a no-op.

The JDK1.6 beta2 javadoc confirms this:

NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op. "

The JDBC 3.0 spec was ambiguous but the 4.0 is not and the new javadoc confirm it.
It's look like postgresql is having the correct behaviour and some people confirmed that Oracle as the same behaviour.
>From that point, it seems that DODS can not expect that the setAutocommit(false) will teminate the transaction and free the resources.

I dont know if the autocommit(false) was only to be sure that the connections are always put back the pool with the expected value to autcommit.
The fact is that the backend locks need always to be released verytime a connection is put back the pool.

As i see it, possible solutions are to guarantee that are

1) All the SELECT queries will  be done using the DB connections with setAutoCommit(true).
2) The method connection.reset() guarantee that actual transaction will be terminated using one of this codes, assuming we keep the setAutoCommit(false):
    2.1) connection.commit();  - it should not hurt because only SELECT should reach the reset method uncommitted.
    2.2) connection.setAutoCommit(true);  - followed by the setAutoCommit(false), it will have almost same effect as the previous one.
    2.3) if (!connection.getAutoCommit()) connection.rollback(); - test if the connection is not with autocommit true, and if not it will rollback to free the database locks. The test is necessary because rollback() throw a SQLException if the connection is in auto-commit mode.


I think that the better approach is the 2.3, because it avoid the risk of committing a transaction that an user "forgot" to rollback.
Can someone see other approaches?
Can someone confirms that this is happening with Oracle too?


Best Regards.
João Paulo Ribeiro

PS: i can share my patch but before i'll prefer to confirm that the correct thing is being done. ;)

Petr Stehlik wrote:

João Paulo Ribeiro píše v Čt 30. 03. 2006 v 16:03 +0100:
  

The postgresql have a problem with setAutocommit(...): if the value
passed (false or true) is the same that was previously set, it just does
nothing.
    

If someone is having the same problem, i did a little patch at the dods
code to workaround this problem since i can't change the postgresql
behaviour.
    

I think that everybody using Postgres would be very interested in your
patch. Can you share it, please?

Petr



  

--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: enhydra-unsubscribe@...
For general help: sympa@...
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
  

--
You receive this message as a subscriber of the enhydra@... mailing list.
To unsubscribe: mailto:enhydra-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws



--
You receive this message as a subscriber of the dods@... mailing list.
To unsubscribe: mailto:dods-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[Vladimir Puškaš-2]

Hello João Paulo Ribeiro

sorry for the delay :-(

Thanks for the catch and suggested solutions.
Although you are right, we cannot directly and unconditionally perform a
rollback there.

DODS now provides support for JTA transactions (it's used in recent
Shark releases). This feature prevents us from performing any
commit/rollback/setAutoCommit on JDBC connection. Those operations are
in transaction manager's realm.

AFAICS solution could be, yet another, configuration parameter
"RollbackOnReset", which would make connection perform rollback inside
reset method.

DatabaseManager.DB.<<logicalDBname>>.Connection.RollbackOnReset=true
makes DODS behave exactly by your suggestion 2.3 - thanks again

DatabaseManager.DB.<<logicalDBname>>.Connection.RollbackOnReset=false
keeps DODS JTA compliant

you wrote:

> Hi.
>
> I'm glade to see that my patch was applied.
> But i had no feedback about DODS not releasing database locks/resources every
> time a connection is put back the pool.
>
> This situation is critical for people using Postgresql and may be other
> databases (Oracle?) in production environment.
>
> I resend my previous email in hope someone can give feedback about this issue.
>
> Best regards.
> João Paulo Ribeiro
>
> PS: Many thanks to the dev team for one more important milestone with the 7.0
> version almost ready.
> Keep the good work! :)
>
> -------- Original Message --------
> Subject: Re: [enhydra] patch to reenable the support for maxWaitingConnections
> Date: Mon, 03 Apr 2006 10:31:31 +0100
> From: João Paulo Ribeiro <jp@...>
> Reply-To: enhydra@...
> To: enhydra@...
> References: <sympa.1142026415.32375.282@...>
> <44154459.3060109@...> <442BF34F.1010706@...>
> <1143731669.8848.41.camel@...>
>
>
>
> I had some doubt about postgresql behaviour being correct and i went the
> postgresql jdbc mailing list.
>
>  >From the discussion:
>
> "The JDBC 4.0 public draft spec says:
>
> The default is for auto-commit mode to be enabled when the Connection object is
> created. If the value of auto-commit is changed in the middle of a transaction,
> the current transaction is committed. If setAutoCommit is called and the value
> for auto-commit is not changed from its current value, it is treated as a no-op.
>
> The JDK1.6 beta2 javadoc confirms this:
>
> NOTE: If this method is called during a transaction and the auto-commit mode is
> changed, the transaction is committed. If setAutoCommit is called and the
> auto-commit mode is not changed, the call is a no-op. "
>
> The JDBC 3.0 spec was ambiguous but the 4.0 is not and the new javadoc confirm it.
> It's look like postgresql is having the correct behaviour and some people
> confirmed that Oracle as the same behaviour.
>  >From that point, it seems that DODS can not expect that the
> setAutocommit(false) will teminate the transaction and free the resources.
>
> I dont know if the autocommit(false) was only to be sure that the connections
> are always put back the pool with the expected value to autcommit.
> The fact is that the backend locks need always to be released verytime a
> connection is put back the pool.
>
> As i see it, possible solutions are to guarantee that are
>
> 1) All the SELECT queries will  be done using the DB connections with
> setAutoCommit(true).
> 2) The method *connection.reset()* guarantee that actual transaction will be
> terminated using one of this codes, assuming we keep the setAutoCommit(false):
>     2.1) *connection.commit(); * - it should not hurt because only SELECT should
> reach the reset method uncommitted.
>     2.2) *connection.setAutoCommit(true); * - followed by the
> setAutoCommit(false), it will have almost same effect as the previous one.
>     2.3) *if (!connection.getAutoCommit()) connection.rollback();* - test if the
> connection is not with autocommit true, and if not it will rollback to free the
> database locks. The test is necessary because rollback() throw a SQLException if
> the connection is in auto-commit mode.
>
> I think that the better approach is the 2.3, because it avoid the risk of
> committing a transaction that an user "forgot" to rollback.
> Can someone see other approaches?
> Can someone confirms that this is happening with Oracle too?
>
> Best Regards.
> João Paulo Ribeiro
>
> PS: i can share my patch but before i'll prefer to confirm that the correct
> thing is being done. ;)
>
> Petr Stehlik wrote:
>
>>João Paulo Ribeiro píše v Čt 30. 03. 2006 v 16:03 +0100:
>>  
>>>The postgresql have a problem with setAutocommit(...): if the value
>>>passed (false or true) is the same that was previously set, it just does
>>>nothing.
>>>    
>>>If someone is having the same problem, i did a little patch at the dods
>>>code to workaround this problem since i can't change the postgresql
>>>behaviour.
>>>    
>>I think that everybody using Postgres would be very interested in your
>>patch. Can you share it, please?
>>
>>Petr
>
> --
> João Paulo Ribeiro | Senior Software Engineer
> jp@...
>
> PHONE: + 351 253 305 250
> FAX  : + 351 253 305 250
> www.mobicomp.com

regards
--
Vlada

"Don't stop at one bug."



--
You receive this message as a subscriber of the dods@... mailing list.
To unsubscribe: mailto:dods-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

[João Paulo Ribeiro]

Hi Vladimir.

Thanks a lot for the feedback and your solution looks good to me. :)
I dont know JTA in detail, but if JTA will be responsible for freeing the resources allocated, i think it will be ok too.

Again, many thanks for the feedback and keep the good work. :)

Best regards.
João Paulo Ribeiro

Vladimir Puskas wrote:

Hello João Paulo Ribeiro

sorry for the delay :-(

Thanks for the catch and suggested solutions.
Although you are right, we cannot directly and unconditionally perform a
rollback there.

DODS now provides support for JTA transactions (it's used in recent
Shark releases). This feature prevents us from performing any
commit/rollback/setAutoCommit on JDBC connection. Those operations are
in transaction manager's realm.

AFAICS solution could be, yet another, configuration parameter
"RollbackOnReset", which would make connection perform rollback inside
reset method.

DatabaseManager.DB.<<logicalDBname>>.Connection.RollbackOnReset=true
makes DODS behave exactly by your suggestion 2.3 - thanks again

DatabaseManager.DB.<<logicalDBname>>.Connection.RollbackOnReset=false
keeps DODS JTA compliant

you wrote:
  

Hi.

I'm glade to see that my patch was applied.
But i had no feedback about DODS not releasing database locks/resources every 
time a connection is put back the pool.

This situation is critical for people using Postgresql and may be other 
databases (Oracle?) in production environment.

I resend my previous email in hope someone can give feedback about this issue.

Best regards.
João Paulo Ribeiro

PS: Many thanks to the dev team for one more important milestone with the 7.0 
version almost ready.
Keep the good work! :)

-------- Original Message --------
Subject: 	Re: [enhydra] patch to reenable the support for maxWaitingConnections
Date: 	Mon, 03 Apr 2006 10:31:31 +0100
From: 	João Paulo Ribeiro jp@...
Reply-To: 	enhydra@...
To: 	enhydra@...
References: 	sympa.1142026415.32375.282@... 
44154459.3060109@... 442BF34F.1010706@... 
1143731669.8848.41.camel@...



I had some doubt about postgresql behaviour being correct and i went the 
postgresql jdbc mailing list.

 >From the discussion:

"The JDBC 4.0 public draft spec says:

The default is for auto-commit mode to be enabled when the Connection object is 
created. If the value of auto-commit is changed in the middle of a transaction, 
the current transaction is committed. If setAutoCommit is called and the value 
for auto-commit is not changed from its current value, it is treated as a no-op.

The JDK1.6 beta2 javadoc confirms this:

NOTE: If this method is called during a transaction and the auto-commit mode is 
changed, the transaction is committed. If setAutoCommit is called and the 
auto-commit mode is not changed, the call is a no-op. "

The JDBC 3.0 spec was ambiguous but the 4.0 is not and the new javadoc confirm it.
It's look like postgresql is having the correct behaviour and some people 
confirmed that Oracle as the same behaviour.
 >From that point, it seems that DODS can not expect that the 
setAutocommit(false) will teminate the transaction and free the resources.

I dont know if the autocommit(false) was only to be sure that the connections 
are always put back the pool with the expected value to autcommit.
The fact is that the backend locks need always to be released verytime a 
connection is put back the pool.

As i see it, possible solutions are to guarantee that are

1) All the SELECT queries will  be done using the DB connections with 
setAutoCommit(true).
2) The method *connection.reset()* guarantee that actual transaction will be 
terminated using one of this codes, assuming we keep the setAutoCommit(false):
    2.1) *connection.commit(); * - it should not hurt because only SELECT should 
reach the reset method uncommitted.
    2.2) *connection.setAutoCommit(true); * - followed by the 
setAutoCommit(false), it will have almost same effect as the previous one.
    2.3) *if (!connection.getAutoCommit()) connection.rollback();* - test if the 
connection is not with autocommit true, and if not it will rollback to free the 
database locks. The test is necessary because rollback() throw a SQLException if 
the connection is in auto-commit mode.

I think that the better approach is the 2.3, because it avoid the risk of 
committing a transaction that an user "forgot" to rollback.
Can someone see other approaches?
Can someone confirms that this is happening with Oracle too?

Best Regards.
João Paulo Ribeiro

PS: i can share my patch but before i'll prefer to confirm that the correct 
thing is being done. ;)

Petr Stehlik wrote:

    

João Paulo Ribeiro píše v Čt 30. 03. 2006 v 16:03 +0100:
 
      

The postgresql have a problem with setAutocommit(...): if the value
passed (false or true) is the same that was previously set, it just does
nothing.
   
If someone is having the same problem, i did a little patch at the dods
code to workaround this problem since i can't change the postgresql
behaviour.
   
        

I think that everybody using Postgres would be very interested in your
patch. Can you share it, please?

Petr
      

-- 
João Paulo Ribeiro | Senior Software Engineer
jp@...

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com
    

regards
  

--
You receive this message as a subscriber of the dods@... mailing list.
To unsubscribe: dods-unsubscribe@...
For general help: sympa@...
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
  

--
You receive this message as a subscriber of the dods@... mailing list.
To unsubscribe: mailto:dods-unsubscribe@...
For general help: mailto:sympa@...?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws