You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Hawkx <ha...@163.com> on 2010/05/31 06:03:35 UTC
Why derby do not auto commit?
I found that derby do not auto commit data change on my computer, even I set
auto commit to true explicitly. nothing written to database, unless I call
commit().
java version "1.6.0_19"
Java(TM) SE Runtime Environment (build 1.6.0_19-b04)
Java HotSpot(TM) Client VM (build 16.2-b04, mixed mode, sharing)
derby 10.6.1 embedded driver
Code:
Connection con =
DriverManager.getConnection("jdbc:derby:D:/test/testdb");
// con.setAutoCommit(true);
Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rst = stm.executeQuery("SELECT * FROM test_table");
rst.moveToInsertRow();
rst.updateString(1, "test line");
rst.insertRow();
rst.beforeFirst();
while (rst.next()) {
System.out.println(rst.getString(1));
}
System.out.println(con.getAutoCommit());
// con.commit();
con.close();
--
View this message in context: http://old.nabble.com/Why-derby-do-not-auto-commit--tp28726531p28726531.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Why derby do not auto commit?
Posted by Hawkx <ha...@163.com>.
Yes, It is my fault. Thanks!
Kristian Waagan-4 wrote:
>
> Hi,
>
> I think this is expected behavior, because you don't close the statement.
> From [1] (* added by me):
> "Auto-commit mode means that when a statement is completed, the method
> /commit/ is called on that statement automatically. Auto-commit in
> effect makes every SQL statement a transaction. The commit occurs when
> the statement completes or the next statement is executed, whichever
> comes first. *In the case of a statement returning a /ResultSet/, the
> statement completes when the last row of the /ResultSet/ has been
> retrieved or the /ResultSet/ has been closed explicitly.*"
>
> When you call close Connection.close(), Derby will do a rollback because
> there is an active transaction (if you had auto-commit set to false, an
> exception would have been thrown).
>
>
> Hope this helps,
> --
> Kristian
>
> [1] http://db.apache.org/derby/docs/dev/devguide/cdevconcepts29416.html
>
>
--
View this message in context: http://old.nabble.com/Why-derby-do-not-auto-commit--tp28726531p28727742.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: Why derby do not auto commit?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 05/31/10 04:29 PM, Kristian Waagan wrote:
> On 31.05.10 16:04, Knut Anders Hatlen wrote:
>
>> On 05/31/10 03:38 PM, Kristian Waagan wrote:
>>
>>
>>> Is the paragraph referenced from the devguide accurate enough, or should
>>> we change it?
>>> There seems to be many fine details to handle related to this issue...
>>>
>>>
>>>
>> I think that the paragraph is accurate enough. It doesn't say what
>> happens when a connection is closed, but that's probably best to leave
>> unspecified until we have harmonized the drivers.
>>
>> I'm wondering about this sentence, though:
>>
>> In the case of a statement returning a /ResultSet/, the statement
>> completes when the last row of the /ResultSet/ has been retrieved or
>> the /ResultSet/ has been closed explicitly.
>>
>>
>> Is it correct that the statement is completed when the last row has been
>> retrieved if the result set is scrollable? I thought this was only the
>> case for forward-only result sets.
>>
>>
> Well, the code posted for this discussion does "while (rst.next())" to
> print the inserted row, but the insert is lost after the connection close.
> I also tried calling rst.afterLast(), and the insert still got lost
> after connection close.
>
> To test this with the client driver, I added a rollback after the having
> read the last row of the result set. The insert was lost
Thanks for testing it, Kristian. I have filed this doc issue:
https://issues.apache.org/jira/browse/DERBY-4682
--
Knut Anders
Re: Why derby do not auto commit?
Posted by Kristian Waagan <Kr...@Sun.COM>.
On 31.05.10 16:04, Knut Anders Hatlen wrote:
> On 05/31/10 03:38 PM, Kristian Waagan wrote:
>
>> Is the paragraph referenced from the devguide accurate enough, or should
>> we change it?
>> There seems to be many fine details to handle related to this issue...
>>
>>
> I think that the paragraph is accurate enough. It doesn't say what
> happens when a connection is closed, but that's probably best to leave
> unspecified until we have harmonized the drivers.
>
> I'm wondering about this sentence, though:
>
> In the case of a statement returning a /ResultSet/, the statement
> completes when the last row of the /ResultSet/ has been retrieved or
> the /ResultSet/ has been closed explicitly.
>
>
> Is it correct that the statement is completed when the last row has been
> retrieved if the result set is scrollable? I thought this was only the
> case for forward-only result sets.
>
Well, the code posted for this discussion does "while (rst.next())" to
print the inserted row, but the insert is lost after the connection close.
I also tried calling rst.afterLast(), and the insert still got lost
after connection close.
To test this with the client driver, I added a rollback after the having
read the last row of the result set. The insert was lost.
--
Kristian
Re: Why derby do not auto commit?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 05/31/10 03:38 PM, Kristian Waagan wrote:
> Is the paragraph referenced from the devguide accurate enough, or should
> we change it?
> There seems to be many fine details to handle related to this issue...
>
I think that the paragraph is accurate enough. It doesn't say what
happens when a connection is closed, but that's probably best to leave
unspecified until we have harmonized the drivers.
I'm wondering about this sentence, though:
In the case of a statement returning a /ResultSet/, the statement
completes when the last row of the /ResultSet/ has been retrieved or
the /ResultSet/ has been closed explicitly.
Is it correct that the statement is completed when the last row has been
retrieved if the result set is scrollable? I thought this was only the
case for forward-only result sets.
--
Knut Anders
Re: Why derby do not auto commit?
Posted by Kristian Waagan <Kr...@Sun.COM>.
On 31.05.10 14:55, Knut Anders Hatlen wrote:
> On Mon, 2010-05-31 at 14:43 +0200, Knut Anders Hatlen wrote:
>
>> On Mon, 2010-05-31 at 09:46 +0200, Kristian Waagan wrote:
>>
>>> On 31.05.10 06:03, Hawkx wrote:
>>>
>>>> I found that derby do not auto commit data change on my computer, even I set
>>>> auto commit to true explicitly. nothing written to database, unless I call
>>>> commit().
>>>>
>>>>
>>> Hi,
>>>
>>> I think this is expected behavior, because you don't close the statement.
>>> From [1] (* added by me):
>>> "Auto-commit mode means that when a statement is completed, the method
>>> /commit/ is called on that statement automatically. Auto-commit in
>>> effect makes every SQL statement a transaction. The commit occurs when
>>> the statement completes or the next statement is executed, whichever
>>> comes first. *In the case of a statement returning a /ResultSet/, the
>>> statement completes when the last row of the /ResultSet/ has been
>>> retrieved or the /ResultSet/ has been closed explicitly.*"
>>>
>>> When you call close Connection.close(), Derby will do a rollback because
>>> there is an active transaction (if you had auto-commit set to false, an
>>> exception would have been thrown).
>>>
>> I was under the impression that closing the connection would implicitly
>> close the statement, which would then lead to the transaction being
>> auto-committed and not rolled back.
>>
>> From EmbedConnection's javadoc:
>> http://db.apache.org/derby/javadoc/engine/org/apache/derby/impl/jdbc/EmbedConnection.html#checkForTransactionInProgress%28%29
>>
>> Check if the transaction is active so that we cannot close down
>> the connection. If auto-commit is on, the transaction is
>> committed when the connection is closed, so it is always OK to
>> close the connection in that case. Otherwise, throw an exception
>> if a transaction is in progress.
>>
>>
> It turns out this problem is already logged in the bug tracker:
> https://issues.apache.org/jira/browse/DERBY-3115
>
>
Hi Knut Anders,
Thanks for getting this right. I based my answer on a look at the code
in the embedded driver, not from the JDBC specification.
I have confirmed that the client driver behaves according to the
standard, and that the embedded driver doesn't (back to 10.2, where the
feature was introduced).
Is the paragraph referenced from the devguide accurate enough, or should
we change it?
There seems to be many fine details to handle related to this issue...
I now remember from working on the client driver that we do keep track
of open result sets there and close them explicitly on Connection.close().
--
Kristian
Re: Why derby do not auto commit?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On Mon, 2010-05-31 at 14:43 +0200, Knut Anders Hatlen wrote:
> On Mon, 2010-05-31 at 09:46 +0200, Kristian Waagan wrote:
> > On 31.05.10 06:03, Hawkx wrote:
> > > I found that derby do not auto commit data change on my computer, even I set
> > > auto commit to true explicitly. nothing written to database, unless I call
> > > commit().
> > >
> >
> > Hi,
> >
> > I think this is expected behavior, because you don't close the statement.
> > From [1] (* added by me):
> > "Auto-commit mode means that when a statement is completed, the method
> > /commit/ is called on that statement automatically. Auto-commit in
> > effect makes every SQL statement a transaction. The commit occurs when
> > the statement completes or the next statement is executed, whichever
> > comes first. *In the case of a statement returning a /ResultSet/, the
> > statement completes when the last row of the /ResultSet/ has been
> > retrieved or the /ResultSet/ has been closed explicitly.*"
> >
> > When you call close Connection.close(), Derby will do a rollback because
> > there is an active transaction (if you had auto-commit set to false, an
> > exception would have been thrown).
>
> I was under the impression that closing the connection would implicitly
> close the statement, which would then lead to the transaction being
> auto-committed and not rolled back.
>
> From EmbedConnection's javadoc:
> http://db.apache.org/derby/javadoc/engine/org/apache/derby/impl/jdbc/EmbedConnection.html#checkForTransactionInProgress%28%29
>
> Check if the transaction is active so that we cannot close down
> the connection. If auto-commit is on, the transaction is
> committed when the connection is closed, so it is always OK to
> close the connection in that case. Otherwise, throw an exception
> if a transaction is in progress.
>
It turns out this problem is already logged in the bug tracker:
https://issues.apache.org/jira/browse/DERBY-3115
Re: Why derby do not auto commit?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On Mon, 2010-05-31 at 09:46 +0200, Kristian Waagan wrote:
> On 31.05.10 06:03, Hawkx wrote:
> > I found that derby do not auto commit data change on my computer, even I set
> > auto commit to true explicitly. nothing written to database, unless I call
> > commit().
> >
>
> Hi,
>
> I think this is expected behavior, because you don't close the statement.
> From [1] (* added by me):
> "Auto-commit mode means that when a statement is completed, the method
> /commit/ is called on that statement automatically. Auto-commit in
> effect makes every SQL statement a transaction. The commit occurs when
> the statement completes or the next statement is executed, whichever
> comes first. *In the case of a statement returning a /ResultSet/, the
> statement completes when the last row of the /ResultSet/ has been
> retrieved or the /ResultSet/ has been closed explicitly.*"
>
> When you call close Connection.close(), Derby will do a rollback because
> there is an active transaction (if you had auto-commit set to false, an
> exception would have been thrown).
I was under the impression that closing the connection would implicitly
close the statement, which would then lead to the transaction being
auto-committed and not rolled back.
>From EmbedConnection's javadoc:
http://db.apache.org/derby/javadoc/engine/org/apache/derby/impl/jdbc/EmbedConnection.html#checkForTransactionInProgress%28%29
Check if the transaction is active so that we cannot close down
the connection. If auto-commit is on, the transaction is
committed when the connection is closed, so it is always OK to
close the connection in that case. Otherwise, throw an exception
if a transaction is in progress.
--
Knut Anders
Re: Why derby do not auto commit?
Posted by Kristian Waagan <Kr...@Sun.COM>.
On 31.05.10 06:03, Hawkx wrote:
> I found that derby do not auto commit data change on my computer, even I set
> auto commit to true explicitly. nothing written to database, unless I call
> commit().
>
Hi,
I think this is expected behavior, because you don't close the statement.
From [1] (* added by me):
"Auto-commit mode means that when a statement is completed, the method
/commit/ is called on that statement automatically. Auto-commit in
effect makes every SQL statement a transaction. The commit occurs when
the statement completes or the next statement is executed, whichever
comes first. *In the case of a statement returning a /ResultSet/, the
statement completes when the last row of the /ResultSet/ has been
retrieved or the /ResultSet/ has been closed explicitly.*"
When you call close Connection.close(), Derby will do a rollback because
there is an active transaction (if you had auto-commit set to false, an
exception would have been thrown).
Hope this helps,
--
Kristian
[1] http://db.apache.org/derby/docs/dev/devguide/cdevconcepts29416.html
> java version "1.6.0_19"
> Java(TM) SE Runtime Environment (build 1.6.0_19-b04)
> Java HotSpot(TM) Client VM (build 16.2-b04, mixed mode, sharing)
> derby 10.6.1 embedded driver
>
> Code:
> Connection con =
> DriverManager.getConnection("jdbc:derby:D:/test/testdb");
> // con.setAutoCommit(true);
> Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
> ResultSet rst = stm.executeQuery("SELECT * FROM test_table");
> rst.moveToInsertRow();
> rst.updateString(1, "test line");
> rst.insertRow();
> rst.beforeFirst();
> while (rst.next()) {
> System.out.println(rst.getString(1));
> }
> System.out.println(con.getAutoCommit());
> // con.commit();
> con.close();
>
>