JDBC Insert Data Not Commited

[auzi]

Hi,

I have an application that uses simple
JDBC to work with the database.
The application executes simple INSERT\SELECT
statements, using prepared statements.
I'm using a MS-Sql local-tx datasource to get
the connection.
For some reason the table I'm working with gets
locked right after I perform an insert, and it is not
freed, when I do a select after insert, the select
"hangs".
My mssql-ds.xml file has the following:

<local-tx-datasource>
<jndi-name>MyDs</jndi-name> <connection-url>jdbc:microsoft:sqlserver://mypc:1433</connection-url>
<connection-property name="DatabaseName">
mydb
</connection-property> <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
<user-name>xx</user-name>
yy
<min-pool-size>2</min-pool-size>
<max-pool-size>10</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<track-statements>true</track-statements>
</local-tx-datasource>

My code uses the following:

InitialContext ctx = InitialContextUtil.getContext();
DataSource ds = (DataSource) ctx.lookup("MyDs");
Connection con = ds.getConnection();

I get no errors during execution, however, after the insert,
every select on the same table either from within jboss or from
MS query analayzer hangs (queries on other objects work ok).

thanks,
Auzi

[mremond]

I am encountering the same problem. Insert of data (used with prepared statement) do not end up in the database.

Do you have found any answer on your problem ?

[hariv]

May be the connection has auto commit set to false once you pull from the connection poll. Try printing the value for the Auto Commit and check. If it is set to false call commit at the end of the transaction

[mremond]

I was wondering if JBoss 4.0 was not doing things in by back with some kind of optimisation-related interceptors. Could the problem be related to prepared statement pooling ? Has the behaviour regarding prepared statement changed between JBoss 3.x and JBoss 4 ?

[aloubyansky]

You should not touch auto-commit in a managed environment. Application server will commit local transaction when the global one is committed.

[mremond]

You should not touch auto-commit in a managed environment. Application server will commit local transaction when the global one is committed.

Ok. That what I thought. JBoss is acting as a wrapper regarding JDBC activity and transactions.

Now, what I would like to know what could be the reason for a write not to be committed to the database ? How to debug this stuff in Jboss ? What are the Jboss parameters to make less intrusive ?

Thank you for your help

--
Mickaël Rémond

[aloubyansky]

This is not JBoss-specific behaviour, this is the spec. auto-commit is set to false in a managed environment. I think, you should be able to reproduce this with pure JDBC standalone app and find out what's wrong.

[mremond]

This is not JBoss-specific behaviour, this is the spec. auto-commit is set to false in a managed environment. I think, you should be able to reproduce this with pure JDBC standalone app and find out what's wrong.

I know.

Something in the application prevent it from reading the result of inserts.
I am asking that because an application that was previously working in JBoss 3.x does no more work in 4.0. So I am assuming that some default behaviour have changed. But until now, I did not manage to figure out precisely what has changed.

I have seen nothing in the JBoss wiki (JBoss4FAQ) regarding those topics.

--
Mickaël

[aloubyansky]

Try JCA forum. Maybe Adrian can help you.