Forwarding this to the list, please keep the discussion there... -------- Original Message -------- Subject: Re: c3p0 ConnectionTester socket read stuck after SQLException Date: Tue, 8 Jan 2013 08:17:35 -0800 (PST) From: Tapomay Dey Reply-To: Tapomay Dey To: Hannes.Muehleisen@cwi.nl My operations are as follows: 1. Create connections with auto-commit=false 2. Read queries from an external module. 3. Execute queries on the connection acquired in step 1 4. Update external module status as per result of queries. 5. If all is well, commit the connection. 6. If commit fails(optimistic locking is the culprit here), revert changes to the external module. All of these steps are implemented in a command chain pattern code. Thus I need to be able to pass around the connection. Thanks and Regards, Tapomay. ------------------------------------------------------------------------ *From:* Hannes Mühleisen *To:* Communication channel for MonetDB users *Sent:* Thursday, January 3, 2013 4:19 PM *Subject:* Re: c3p0 ConnectionTester socket read stuck after SQLException Hello Tapomay, after looking at your code, I was wondering why you chose the double management of connections in your Main/JdbcMonetdbTestClient classes. C3P0 provides a way to define "connection customizers" (com.mchange.v2.c3p0.ComboPooledDataSource.setConnectionCustomizerClassName(String)), which you can use to disable autocommit on new connections. This should simplify your example considerably. Nevertheless, I will try to provoke the exceptions you have mentioned and see how the JDBC driver handles them together with C3P0. Best, Hannes On 20/12/12 15:41, Tapomay Dey wrote:

As the source code has many unrelated dependencies I tried to extract the relevant code. Please find attached an eclipse project for the same. However I haven't been able to reproduce the issue in this test project. It should give you a fair idea of the sequence of calls made. Plz note that a single insert query used in the live project is much much larger (10K approx.) than the one in the test. I reconfirmed that the issue is still there in the larger project.

PS: I had sent this code long back and it got moderated just now(due to some 40KB limit). Thus I have removed the dependencies monetdb-jdbc-2.6.jar and c3p0-0.9.1.2.jar from lib. Plz add them to lib folder and everything should work(after u set the correct DB names in the code).(Anyways this code doesn't reproduce the issue I am facing so I guess just a manual overview should suffice)

Regards, Tapomay.

------------------------------------------------------------------------ *From:* Hannes Mühleisen mailto:Hannes.Muehleisen@cwi.nl> *To:* Communication channel for MonetDB users mailto:users-list@monetdb.org> *Sent:* Thursday, December 20, 2012 2:17 PM *Subject:* Re: c3p0 ConnectionTester socket read stuck after SQLException

Hello Tapomay,

I will have a look later today, could you please also provide the source code that you have used to provoke the issue?

Thanks,

Hannes

On 20/12/12 08:26, Tapomay Dey wrote:

...

Please find the thread dump below. Running MonetDB Database Server v1.6 (Oct2012-SP1). Compiled from MonetDB-11.13.5 src distribution. C3P0 version: 0.9.1.2. Also tried with 0.9.2. Occurs only after SQLException sometimes: INSERT INTO xyz : table xyz doesn't exist. Otherwise it works great. Also tried setting c3p0's preferredQuery for ConnectionTester to "select 1;". Problem persists.

Name: pool-15-thread-2 State: RUNNABLE Total blocked: 0 Total waited: 1

Stack trace: java.net.SocketInputStream.socketRead0(Native Method) java.net.SocketInputStream.read(SocketInputStream.java:150) java.net.SocketInputStream.read(SocketInputStream.java:121) java.io.BufferedInputStream.fill(BufferedInputStream.java:235) java.io.BufferedInputStream.read1(BufferedInputStream.java:275) java.io.BufferedInputStream.read(BufferedInputStream.java:334) - locked java.io.BufferedInputStream@5cec5796

nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream._read(MapiSocket.java:846)

...

nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.readBlock(MapiSocket.java:895)

...

nl.cwi.monetdb.mcl.net.MapiSocket$BlockInputStream.read(MapiSocket.java:965)

...

sun.nio.cs.StreamDecoder.readBytes(StreamDecoder.java:283) sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:325) sun.nio.cs.StreamDecoder.read(StreamDecoder.java:177) - locked java.io.InputStreamReader@206a6883 java.io.InputStreamReader.read(InputStreamReader.java:184) java.io.BufferedReader.fill(BufferedReader.java:154) java.io.BufferedReader.readLine(BufferedReader.java:317) - locked java.io.InputStreamReader@206a6883 java.io.BufferedReader.readLine(BufferedReader.java:382)

nl.cwi.monetdb.mcl.io.BufferedMCLReader.readLine(BufferedMCLReader.java:119)

...

nl.cwi.monetdb.mcl.io.BufferedMCLReader.waitForPrompt(BufferedMCLReader.java:196)

...

- locked nl.cwi.monetdb.mcl.io.BufferedMCLReader@29f38115

nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2296)

...

- locked nl.cwi.monetdb.mcl.net.MapiSocket@319f9f37

nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:2274)

...

nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:508)

...

nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:349)

nl.cwi.monetdb.jdbc.MonetStatement.executeQuery(MonetStatement.java:525)

...

com.mchange.v2.c3p0.impl.DefaultConnectionTester.activeCheckConnection(DefaultConnectionTester.java:73)

...

com.mchange.v2.c3p0.impl.DefaultConnectionTester.statusOnException(DefaultConnectionTester.java:143)

...

com.mchange.v2.c3p0.AbstractConnectionTester.statusOnException(AbstractConnectionTester.java:82)

...

com.mchange.v2.c3p0.impl.NewPooledConnection.handleThrowable(NewPooledConnection.java:437)

...

- locked com.mchange.v2.c3p0.impl.NewPooledConnection@3340302c

com.mchange.v2.c3p0.impl.NewProxyStatement.executeUpdate(NewProxyStatement.java:78)

...

com.sokrati.moneta.mclient.JdbcMonetdbClient.executeUpdate(JdbcMonetdbClient.java:154)

...

com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:46)

...

com.sokrati.moneta.multiplexer.MonetdbClientCallable.call(MonetdbClientCallable.java:1)

...

java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) java.util.concurrent.FutureTask.run(FutureTask.java:166)

java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)

...

java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)

...

java.lang.Thread.run(Thread.java:722)

Regards, Tapomay.

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list