Thread: Behaviour of setAutoCommit may not be completely correct.
Hello.
I found a situation that is causing db locks using enhydra dods with postgresql due to somethinf that look like a bad
behaviourof postgresql.
Since version 6.0, i think, dods do all the db queries with setAutoCommit(false). The expected behaviour of
setAutocommit(...)is that the current transaction is commited, allowing the db backend to free resources previously
allocated,and a new one is created with the autocommit set to the value passed.
From the specs: "If the value of auto-commit is changed in the middle of a transaction, the current transaction is
committed."
The postgresql have a problem with setAutocommit(...): if the value passed (false or true) is the same that was
previouslyset, it just does nothing.
I looked to the postgresql driver and found this:
public void setAutoCommit(boolean autoCommit) throws SQLException
{
if (this.autoCommit == autoCommit)
return ;
if (autoCommit)
{
execSQL("end");
}
else
{
if (haveMinimumServerVersion("7.1"))
{
execSQL("begin;" + getIsolationLevelSQL());
}
else
{
execSQL("begin");
execSQL(getIsolationLevelSQL());
}
}
this.autoCommit = autoCommit;
}
The driver do nothing when the value passed (false or true) is the same
that was previously set.
I fixed the driver and found that the behaviour was the same: it's look
likes the backend is using the same logic and that explain why someone
at the driver JDBC decided to not pass this to the backend.
My tests were done with Postgresql 7.4.12.
Can someone confirm that the problem exist and it is in the driver code
and the backend server?
Is this still happening with postgresql 8.1?
Best regards
João Paulo Ribeiro
--
João Paulo Ribeiro | Senior Software Engineer
jp@mobicomp.com
PHONE: + 351 253 305 250
FAX : + 351 253 305 250
www.mobicomp.com
________________________________________________________________
About Solutions | Wireless World
CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for
theindividual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any
usewhatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does
notaccept liability for any fact which may interfere with the integrity of its content.
Hi, João Paolo, João Paulo Ribeiro wrote: > From the specs: "If the value of auto-commit is changed in the middle > of a transaction, the current transaction is committed." > > 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. I don't see this as bug, acutally setting the value to the same it had previously is not "changing", strictly seen. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Markus Schaber write: > João Paulo Ribeiro wrote: > > > From the specs: "If the value of auto-commit is changed in the middle > > of a transaction, the current transaction is committed." > > > > 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. > > I don't see this as bug, acutally setting the value to the same it had > previously is not "changing", strictly seen. The latest javadoc of the Connection interface at http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#setAutoCommit(boolean) has (same for 1.5.0 and 1.4.2): setAutoCommit: ... "NOTE: If this method is called during a transaction, the transaction is committed." This sentence is completely unambigously stating that calling the method will commit a running transaction. If the postgresql jdbc driver is in violation of this, it should be fixed. Otherwise it will break applications that are written based on the specified semantics. Best Regards, Michael Paesold
So the question is which document is right.
The specs as Michael pointed out say if it is called, the specs as
Joao pointed out suggest that if it is changed. Joao, where did you
read this ?
Dave
On 31-Mar-06, at 5:02 AM, João Paulo Ribeiro wrote:
> Hello.
>
> I found a situation that is causing db locks using enhydra dods
> with postgresql due to somethinf that look like a bad behaviour of
> postgresql.
> Since version 6.0, i think, dods do all the db queries with
> setAutoCommit(false). The expected behaviour of setAutocommit(...)
> is that the current transaction is commited, allowing the db
> backend to free resources previously allocated, and a new one is
> created with the autocommit set to the value passed.
>
> From the specs: "If the value of auto-commit is changed in the
> middle of a transaction, the current transaction is committed."
>
> 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.
>
> I looked to the postgresql driver and found this:
>
> public void setAutoCommit(boolean autoCommit) throws SQLException
> {
> if (this.autoCommit == autoCommit)
> return ;
> if (autoCommit)
> {
> execSQL("end");
> }
> else
> {
> if (haveMinimumServerVersion("7.1"))
> {
> execSQL("begin;" + getIsolationLevelSQL());
> }
> else
> {
> execSQL("begin");
> execSQL(getIsolationLevelSQL());
> }
> }
> this.autoCommit = autoCommit;
> }
>
> The driver do nothing when the value passed (false or true) is the
> same
> that was previously set.
> I fixed the driver and found that the behaviour was the same: it's
> look
> likes the backend is using the same logic and that explain why someone
> at the driver JDBC decided to not pass this to the backend.
> My tests were done with Postgresql 7.4.12.
> Can someone confirm that the problem exist and it is in the driver
> code
> and the backend server?
> Is this still happening with postgresql 8.1?
>
> Best regards
> João Paulo Ribeiro
>
> --
> João Paulo Ribeiro | Senior Software Engineer
> jp@mobicomp.com
>
> PHONE: + 351 253 305 250
> FAX : + 351 253 305 250
> www.mobicomp.com
>
> ________________________________________________________________
>
> About Solutions | Wireless World
>
> CONFIDENTIALITY NOTICE: This message, as well as existing attached
> files, is confidential and intended exclusively for the individual
> (s) named as addressees. If you are not the intended recipient, you
> are kindly requested not to make any use whatsoever of its contents
> and to proceed to the destruction of the message, thereby notifying
> the sender.
> DISCLAIMER: The sender of this message can not ensure the security
> of its electronic transmission and consequently does not accept
> liability for any fact which may interfere with the integrity of
> its content.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Just an FYI, I ran a quick compatibility test and Oracle's 10g driver
does NOT commit if autoCommit was false and is set to false again.
-- Mark Lewis
On Fri, 2006-03-31 at 08:39 -0500, Dave Cramer wrote:
> So the question is which document is right.
>
> The specs as Michael pointed out say if it is called, the specs as
> Joao pointed out suggest that if it is changed. Joao, where did you
> read this ?
>
> Dave
> On 31-Mar-06, at 5:02 AM, João Paulo Ribeiro wrote:
>
> > Hello.
> >
> > I found a situation that is causing db locks using enhydra dods
> > with postgresql due to somethinf that look like a bad behaviour of
> > postgresql.
> > Since version 6.0, i think, dods do all the db queries with
> > setAutoCommit(false). The expected behaviour of setAutocommit(...)
> > is that the current transaction is commited, allowing the db
> > backend to free resources previously allocated, and a new one is
> > created with the autocommit set to the value passed.
> >
> > From the specs: "If the value of auto-commit is changed in the
> > middle of a transaction, the current transaction is committed."
> >
> > 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.
> >
> > I looked to the postgresql driver and found this:
> >
> > public void setAutoCommit(boolean autoCommit) throws SQLException
> > {
> > if (this.autoCommit == autoCommit)
> > return ;
> > if (autoCommit)
> > {
> > execSQL("end");
> > }
> > else
> > {
> > if (haveMinimumServerVersion("7.1"))
> > {
> > execSQL("begin;" + getIsolationLevelSQL());
> > }
> > else
> > {
> > execSQL("begin");
> > execSQL(getIsolationLevelSQL());
> > }
> > }
> > this.autoCommit = autoCommit;
> > }
> >
> > The driver do nothing when the value passed (false or true) is the
> > same
> > that was previously set.
> > I fixed the driver and found that the behaviour was the same: it's
> > look
> > likes the backend is using the same logic and that explain why someone
> > at the driver JDBC decided to not pass this to the backend.
> > My tests were done with Postgresql 7.4.12.
> > Can someone confirm that the problem exist and it is in the driver
> > code
> > and the backend server?
> > Is this still happening with postgresql 8.1?
> >
> > Best regards
> > João Paulo Ribeiro
> >
> > --
> > João Paulo Ribeiro | Senior Software Engineer
> > jp@mobicomp.com
> >
> > PHONE: + 351 253 305 250
> > FAX : + 351 253 305 250
> > www.mobicomp.com
> >
> > ________________________________________________________________
> >
> > About Solutions | Wireless World
> >
> > CONFIDENTIALITY NOTICE: This message, as well as existing attached
> > files, is confidential and intended exclusively for the individual
> > (s) named as addressees. If you are not the intended recipient, you
> > are kindly requested not to make any use whatsoever of its contents
> > and to proceed to the destruction of the message, thereby notifying
> > the sender.
> > DISCLAIMER: The sender of this message can not ensure the security
> > of its electronic transmission and consequently does not accept
> > liability for any fact which may interfere with the integrity of
> > its content.
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Michael Paesold wrote: > has (same for 1.5.0 and 1.4.2): > > setAutoCommit: > ... > "NOTE: If this method is called during a transaction, the transaction is > committed." > > This sentence is completely unambigously stating that calling the method > will commit a running transaction. If the postgresql jdbc driver is in > violation of this, it should be fixed. Otherwise it will break > applications that are written based on the specified semantics. Note that this is new to the JDBC3 javadoc. The JDBC2 javadoc doesn't have that note. The main spec (e.g. the JDBC 3.0 PDF) document is unambiguous, too, but describes different behaviour: > 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. The specification behaviour is what the driver implements (it sounds like Oracle did the same thing too). Does the specification trump the javadoc, or vice versa? -O
Oliver Jowett <oliver@opencloud.com> writes:
> Does the specification trump the javadoc, or vice versa?
The spec certainly ought to win, but the fact that the change in the
javadoc is more recent is a pretty clear clue what Sun thinks.
You quote the spec text as
>> 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.
I don't see that as unambiguous. If the autocommit setting is *changed*
then it's clear what must happen, but calling the method with the same
setting that already prevails could easily be considered not to cause
a "change" in the setting.
It looks to me like Sun is trying to clarify their reading of that
detail with the javadoc change. It's a crummy way to handle it; they
should have updated the spec. But I'd say the handwriting is on the
wall about what the next spec version will say.
Is it reasonable to provide an option about how to handle this case?
regards, tom lane
On Sat, 1 Apr 2006, Tom Lane wrote: > It looks to me like Sun is trying to clarify their reading of that > detail with the javadoc change. It's a crummy way to handle it; they > should have updated the spec. But I'd say the handwriting is on the > wall about what the next spec version will say. > 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. So I think we're fine. Kris Jurka
Ok. I believe you are right. Looks like postgresql is doing the right thing. I'm gonna pass this information to the dods developper team. Many thanks. Best regards. João Paulo Ribeiro Kris Jurka wrote: > > > On Sat, 1 Apr 2006, Tom Lane wrote: > >> It looks to me like Sun is trying to clarify their reading of that >> detail with the javadoc change. It's a crummy way to handle it; they >> should have updated the spec. But I'd say the handwriting is on the >> wall about what the next spec version will say. >> > > 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. > > So I think we're fine. > > Kris Jurka > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > -- João Paulo Ribeiro | Senior Software Engineer jp@mobicomp.com PHONE: + 351 253 305 250 FAX : + 351 253 305 250 www.mobicomp.com ________________________________________________________________ About Solutions | Wireless World CONFIDENTIALITY NOTICE: This message, as well as existing attached files, is confidential and intended exclusively for theindividual(s) named as addressees. If you are not the intended recipient, you are kindly requested not to make any usewhatsoever of its contents and to proceed to the destruction of the message, thereby notifying the sender. DISCLAIMER: The sender of this message can not ensure the security of its electronic transmission and consequently does notaccept liability for any fact which may interfere with the integrity of its content.