You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Wujek Srujek <wu...@gmail.com> on 2013/02/18 15:15:14 UTC
Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Hi. I am using a standalone Derby server that comes with my JDK (the
following is invoked in $JDK_HOME/db/bin):
$ ./sysinfo
------------------ Java Information ------------------
Java Version: 1.7.0_07
Java Vendor: Oracle Corporation
Java home: /usr/lib/jvm/jdk1.7.0_07/jre
Java classpath:
/usr/lib/jvm/java/db/lib/derby.jar:/usr/lib/jvm/java/db/lib/derbynet.jar:/usr/lib/jvm/java/db/lib/derbytools.jar:/usr/lib/jvm/java/db/lib/derbyclient.jar
OS name: Linux
OS architecture: amd64
OS version: 3.0.0-31-generic
Java user name: wujek
Java user home: /home/wujek
Java user dir: /usr/lib/jvm/jdk1.7.0_07/db/bin
java.specification.name: Java Platform API Specification
java.specification.version: 1.7
java.runtime.version: 1.7.0_07-b10
--------- Derby Information --------
JRE - JDBC: Java SE 7 - JDBC 4.0
[/usr/lib/jvm/jdk1.7.0_07/db/lib/derby.jar] 10.8.2.2 - (1181258)
[/usr/lib/jvm/jdk1.7.0_07/db/lib/derbytools.jar] 10.8.2.2 - (1181258)
[/usr/lib/jvm/jdk1.7.0_07/db/lib/derbynet.jar] 10.8.2.2 - (1181258)
[/usr/lib/jvm/jdk1.7.0_07/db/lib/derbyclient.jar] 10.8.2.2 - (1181258)
The data source configuration is in the attached domain.xml file, but just
for reference:
<jdbc-connection-pool is-isolation-level-guaranteed="false"
datasource-classname="org.apache.derby.jdbc.ClientXADataSource40"
res-type="javax.sql.XADataSource" name="DerbyTestXAPool">
<property name="databaseName" value="hibertest" />
<property name="password" value="hibertest" />
<property name="user" value="hibertest" />
<property name="serverName" value="localhost" />
</jdbc-connection-pool>
<jdbc-resource pool-name="DerbyTestXAPool" description=""
jndi-name="jdbc/DerbyTestXAPool" />
(So, I am using an XA data source. The DDL script for this database will be
attached.)
The whole project consists of a single 3.0 servlet, whose code is below
(sans package and imports):
@WebServlet(name = "JdbcServlet", value = "/jdbc")
public class JdbcServlet extends HttpServlet {
@Resource(name = "jdbc/DerbyTestXAPool")
private DataSource dataSource;
@Resource
private UserTransaction utx;
protected void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
response.setContentType("text/plain");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
Writer out = response.getWriter();
Connection conn = null;
try {
// utx.begin();
out.write(utx.getStatus() + "\n");
conn = dataSource.getConnection();
out.write(conn.getAutoCommit() + "\n");
conn.setAutoCommit(false);
out.write(conn.getAutoCommit() + "\n");
ResultSet rs = conn.createStatement().executeQuery("select
max(id) from person");
rs.next();
long nextid = rs.getLong(1) + 1;
out.write(conn.getAutoCommit() + "\n");
PreparedStatement pst = conn.prepareStatement("insert into
person(id, first_name, last_name) values(?, ?, ?)");
out.write(conn.getAutoCommit() + "\n");
pst.setLong(1, nextid);
pst.setString(2, "fn" + nextid);
pst.setString(3, "ln" + nextid);
pst.execute();
out.write(conn.getAutoCommit() + "\n");
rs = conn.createStatement().executeQuery("select id,
first_name, last_name from person");
while (rs.next()) {
long id = rs.getLong("id");
String firstName = rs.getString("first_name");
String lastName = rs.getString("last_name");
out.write(String.format("[%d] %s %s\n", id, firstName,
lastName));
}
// utx.commit();
out.write(utx.getStatus() + "\n");
} catch (Exception e) {
// try {
// utx.rollback();
// } catch (SystemException e2) {
// ignore
// }
throw new ServletException(e);
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// ignore
}
}
}
}
}
It does this:
1. takes a connection from the data source (which should be an XA
connection per configuration)
2. _does not_ start any transaction, prints the status twice (and I get 6,
which is Status.STATUS_NO_TRANSACTION)
3. sets autoCommit to false and prints it a few times
4. gets the biggest id in the table (0 when table is empty)
5. creates a new row with a prepared statement
6. prints out all rows
7. _does not_ commit the transaction in any way
The output I see is:
6
true
false
false
false
false
[1] fn1 ln1
6
(the 6 is the mentioned utx status, true is the initial value of
autoCommit, and false are the values after setting it to false)
What I don't understand:
1. how is it possible that the insertion is visible in the next select?
there is no transaction, autoCommit is explicitly set to false, there is no
caching like EntityManager cache - or is there?
2. how is it possible that the insertion is made persistent after the code
executes, without the transaction ever being committed (either local or
global) - I can see in a db viewer that the row has been added? the close()
in finally seems to be doing magic things
The transaction isolation is READ_COMITTED by default, at least that's what
I found, so reading the just-inserted row should not happen. I think the RA
for Derby is allowed to start a local transaction when there is no global
one (the specs allows this as optional), but there is not a single commit
in this code. How come the row gets saved?
I am obviously doing things wrong here, making invalid assumptions or just
plain don't know how jdbc / jta works. Could someone help me understand
what is going on here?
wujek
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Wujek Srujek <wu...@gmail.com>.
So, in case anybody is interested in this, the problem seems to be
GlassFish in this case. When neither a global nor a local transaction is
started, when the connection is closed and returned to the pool (using
their wrapper ManagedConnection), the following code is called at some
point:
private void resetAutoCommit() throws ResourceException {
if (defaultAutoCommitValue != getLastAutoCommitValue() &&
!(isTransactionInProgress())) {
try {
actualConnection.setAutoCommit(defaultAutoCommitValue);
} catch (SQLException sqle) {
String i18nMsg = localStrings.getString(
"jdbc.error_during_setAutoCommit");
throw new ResourceException(i18nMsg + sqle.getMessage(),
sqle);
}
setLastAutoCommitValue(defaultAutoCommitValue);
}
}
For some reason, the connection is thought to be using JTA (although I
never really start the tx), isTransactionInProgress returns false, and the
autoCommit is set - which results in the local transaction that was
implicitly started being committed. That's why I never see any Derby
exceptions. I will ask the GF team about it, because this seems strange.
wujek
On Mon, Feb 18, 2013 at 5:29 PM, Bryan Pendleton <bpendleton.derby@gmail.com
> wrote:
> > One more question, though: the uncommitted insert comes up
> > in the subsequent select - is this data coming from the server,
> > from the active tx, or does the jdbc driver cache the data somehow?
>
> The results are coming from the server. The server shows you your own
> uncommitted updates, but won't show them to any other transaction
> until you commit or abort your transaction.
>
> To be precise, some of these behaviors are controllable: see the
> discussion of "isolation" in the docs:
> http://db.apache.org/derby/**docs/10.9/devguide/**cdevconcepts30291.html<http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts30291.html>
>
> thanks,
>
> bryan
>
>
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Bryan Pendleton <bp...@gmail.com>.
> One more question, though: the uncommitted insert comes up
> in the subsequent select - is this data coming from the server,
> from the active tx, or does the jdbc driver cache the data somehow?
The results are coming from the server. The server shows you your own
uncommitted updates, but won't show them to any other transaction
until you commit or abort your transaction.
To be precise, some of these behaviors are controllable: see the
discussion of "isolation" in the docs:
http://db.apache.org/derby/docs/10.9/devguide/cdevconcepts30291.html
thanks,
bryan
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Wujek Srujek <wu...@gmail.com>.
Ok, so I was wrong all along. There seems not to be any explicit method to
start a local transaction in jdbc, it is implicit when a previous
transaction is committed (in JTA, though, I need to start it myself if I
manage the tx boundaries myself, and that perplexed me ;d). When I change
the autoCommit setting, the current transaction is committed, and a new is
started. Also, in my code, I ignored the SQLException when
connection.close() was called (it's not production code, just some testing,
but still a bad thing to do), and derby does throw a meaningful exception
there (saying that the transaction is still active), which is very nice.
One more question, though: the uncommitted insert comes up in the
subsequent select - is this data coming from the server, from the active
tx, or does the jdbc driver cache the data somehow?
wujek
On Mon, Feb 18, 2013 at 4:48 PM, Bryan Pendleton <bpendleton.derby@gmail.com
> wrote:
> On 02/18/2013 07:40 AM, Wujek Srujek wrote:
>
>> Hi. But why is there any local transaction? I haven't started any, I just
>> set autoCommit to false
>>
>
> There is always a transaction; Derby won't let you ever
> access the database without one.
>
> What auto-commit does is to automatically commit the
> transaction after each statement is executed.
>
> But any time you issue a statement against the database,
> if there isn't already a transaction begun, one is started
> for you.
>
> thanks,
>
> bryan
>
>
>
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Bryan Pendleton <bp...@gmail.com>.
On 02/18/2013 07:40 AM, Wujek Srujek wrote:
> Hi. But why is there any local transaction? I haven't started any, I just set autoCommit to false
There is always a transaction; Derby won't let you ever
access the database without one.
What auto-commit does is to automatically commit the
transaction after each statement is executed.
But any time you issue a statement against the database,
if there isn't already a transaction begun, one is started
for you.
thanks,
bryan
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Wujek Srujek <wu...@gmail.com>.
Hi. But why is there any local transaction? I haven't started any, I just
set autoCommit to false, but never call begin().
I am using GlassFish 3, I don't know what they do on reclaiming the
connection, and I read that it is implementation specific in what happens
on close() when a tx is not committed. I will research further.
My jdbc foo is apparently lacking, as I get exactly the same results for
PostgreSQL 9.1...
On Mon, Feb 18, 2013 at 3:54 PM, Kristian Waagan <kristian.waagan@oracle.com
> wrote:
> On 18.02.2013 15:15, Wujek Srujek wrote:
>
>> What I don't understand:
>> 1. how is it possible that the insertion is visible in the next select?
>> there is no transaction, autoCommit is explicitly set to false, there is no
>> caching like EntityManager cache - or is there?
>>
>
> Hello,
>
> Because you're in the same local transaction and you haven't yet rolled
> back what you have done so far?
>
>
> 2. how is it possible that the insertion is made persistent after the
>> code executes, without the transaction ever being committed (either local
>> or global) - I can see in a db viewer that the row has been added? the
>> close() in finally seems to be doing magic things
>>
>>
> That I do not know (I've only looked at the code in your email).
> You could try to enable client side tracing on the Derby network driver to
> see what's going on [1]. Could it be that your application server /
> container is issuing a commit when it reclaims the connection?
>
> I have not verified that Derby doesn't do the above for XA, but normally
> Derby throws an exception if close is called and there is an active
> transaction.
>
>
> --
> Kristian
>
> [1] See https://builds.apache.org/job/**Derby-docs/**
> lastSuccessfulBuild/artifact/**trunk/out/adminguide/**
> cadminappsclienttracing.html<https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/adminguide/cadminappsclienttracing.html>. The simpler server side tracing may also be to some help: derby.language.
> **logStatementText=true
>
Re: Derby 10.8.2.2, JTA on GlassFish 3.1.2.2 and very strange behavior
Posted by Kristian Waagan <kr...@oracle.com>.
On 18.02.2013 15:15, Wujek Srujek wrote:
> What I don't understand:
> 1. how is it possible that the insertion is visible in the next
> select? there is no transaction, autoCommit is explicitly set to
> false, there is no caching like EntityManager cache - or is there?
Hello,
Because you're in the same local transaction and you haven't yet rolled
back what you have done so far?
> 2. how is it possible that the insertion is made persistent after the
> code executes, without the transaction ever being committed (either
> local or global) - I can see in a db viewer that the row has been
> added? the close() in finally seems to be doing magic things
>
That I do not know (I've only looked at the code in your email).
You could try to enable client side tracing on the Derby network driver
to see what's going on [1]. Could it be that your application server /
container is issuing a commit when it reclaims the connection?
I have not verified that Derby doesn't do the above for XA, but normally
Derby throws an exception if close is called and there is an active
transaction.
--
Kristian
[1] See
https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/adminguide/cadminappsclienttracing.html
. The simpler server side tracing may also be to some help:
derby.language.logStatementText=true