Thread: Connection breaks with in transaction with a commit

Hi,

I am new to postgres and jdbc.
I am facing a very strange problem. I initiate a transaction with
con.setAutocommitOn(false). My transaction contains two prepare
statements (first one is select, second one is update query) and then a
batch query of prepared insert statements. It executes first two
statements successfully and then while preparing the next
prepareStatement, the connection breaks and commits the data updated so
far in the transaction. I am using connection pooling feature provided
by provided by Tomcat.
The most annoying part is, it does not happen always. It just happens
some times and keeps on giving error for next few trials and then
suddenly starts working correctly.

I do not understand why connection is breaking suddenly and why is it
committing the data before the transaction completes.

The connection always break at the same point in code. if accidentally
the same connection were passed to concurrent threads and it were being
closed and committed by some other thread, it could have broken at any
point in the code. But it always happens on the same line of code
whenever it happens.

Am I missing setting certain required parameters?

first few lines from the trace,
java.sql.SQLException: Connection is closed. at
org.apache.commons.dbcp.DelegatingConnection.checkOpen(DelegatingConnection.java:245)
at
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:185)


Please suggest me a solution.

Thanks in advance,
Pushker Chaubey




The information contained in this electronic message and any attachments to this message are intended for the exclusive
useof the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended
recipient,you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy
theoriginal message all copies of this message and any attachments. 
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for
thepresence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. 

Pushker Chaubey wrote:
> Hi,
>
> I am new to postgres and jdbc.
> I am facing a very strange problem. I initiate a transaction with
> con.setAutocommitOn(false). My transaction contains two prepare
> statements (first one is select, second one is update query) and then a
> batch query of prepared insert statements. It executes first two
> statements successfully and then while preparing the next
> prepareStatement, the connection breaks and commits the data updated so
> far in the transaction.
If the connection is really breaking, shouldn't that result in an
automatic ROLLBACK on the server side?

Try enabling log_statement and log_connection in the PostgreSQL server
logs, running your tests, and examining the server logs. Your database
access tools might be doing a commit behind your back.

--
Craig Ringer