Thread: Locking on PGStream.ReceiveChar(PGStream.java:256)
Hello. JDBC version: 8.1-407 JDBC 3 <http://jdbc.postgresql.org/download/postgresql-8.1-407.jdbc3.jar> PostgreSQL version: 8.1.5-1 for Windows Database encoding: UTF8 I observed a lock at at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) Thread dump: "Thread-1" prio=6 tid=0x02ea9008 nid=0x638 runnable [0x0388f000..0x0388f9e8] at java.net.SocketInputStream.socketRead0(Native Method) at java.net.SocketInputStream.read(Unknown Source) at java.io.BufferedInputStream.fill(Unknown Source) at java.io.BufferedInputStream.read(Unknown Source) - locked <0x10bc1d48> (a java.io.BufferedInputStream) at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) - locked <0x10bc1d98> (a org.postgresql.core.v3.QueryExecutorImpl) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340) at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:217) at org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionIsolation(AbstractJdbc2Connection.java:737) It looks like it wait forever for next char from database (but it never comes) at jdbc driver code: public int ReceiveChar() throws IOException { int c = pg_input.read(); if (c < 0) throw new EOFException(); return c; } It happens after about hour of our perfomance test (high workload - 50 threads are executing various short operations on database) In our aplication it lock at following code: connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); which is executed after taking connection from pool. It is possible that this code is executing serveral times for the same connection. What can I do to avoid this problem? Regards Marcin Waldowski
On Fri, 1 Dec 2006, Marcin Waldowski wrote: > Hello. > > JDBC version: 8.1-407 JDBC 3 > PostgreSQL version: 8.1.5-1 for Windows > Database encoding: UTF8 > > I observed a lock at at > org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) > > It happens after about hour of our perfomance test (high workload - 50 > threads are executing various short operations on database) > > What can I do to avoid this problem? > This sort of problem has been attributed to the windows port of postgresql. This thread here resulted in a fix that will be included in the 8.1.6 release and may fix the problem. http://archives.postgresql.org/pgsql-hackers/2006-10/thrd7.php#00289 Kris Jurka
You may want to confirm that no more than one thread is using a Connection (or a statement/resultset off of that connection) at the same time. A couple of folks on this list have reported similar stalls that were resolved by single-threading access to each Connection. -- Mark On Fri, 2006-12-01 at 14:16 +0100, Marcin Waldowski wrote: > Hello. > > JDBC version: 8.1-407 JDBC 3 > <http://jdbc.postgresql.org/download/postgresql-8.1-407.jdbc3.jar> > PostgreSQL version: 8.1.5-1 for Windows > Database encoding: UTF8 > > I observed a lock at at > org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) > > Thread dump: > "Thread-1" prio=6 tid=0x02ea9008 nid=0x638 runnable [0x0388f000..0x0388f9e8] > at java.net.SocketInputStream.socketRead0(Native Method) > at java.net.SocketInputStream.read(Unknown Source) > at java.io.BufferedInputStream.fill(Unknown Source) > at java.io.BufferedInputStream.read(Unknown Source) > - locked <0x10bc1d48> (a java.io.BufferedInputStream) > at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1163) > at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188) > - locked <0x10bc1d98> (a org.postgresql.core.v3.QueryExecutorImpl) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340) > at > org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLUpdate(AbstractJdbc2Connection.java:217) > at > org.postgresql.jdbc2.AbstractJdbc2Connection.setTransactionIsolation(AbstractJdbc2Connection.java:737) > > It looks like it wait forever for next char from database (but it never > comes) at jdbc driver code: > public int ReceiveChar() throws IOException > { > int c = pg_input.read(); > if (c < 0) > throw new EOFException(); > return c; > } > > It happens after about hour of our perfomance test (high workload - 50 > threads are executing various short operations on database) > > In our aplication it lock at following code: > connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > which is executed after taking connection from pool. It is possible that > this code is executing serveral times for the same connection. > > What can I do to avoid this problem? > > Regards > Marcin Waldowski > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
Yes, I'm sure that no more than one thread is using a Connection at the same time. Im almost sure that unfortunately it is bug of PostgreSQL Windows port (http://archives.postgresql.org/pgsql-hackers/2006-10/msg00289.php) as Kris Jurka previously said. Marcin Mark Lewis wrote: > You may want to confirm that no more than one thread is using a > Connection (or a statement/resultset off of that connection) at the same > time. A couple of folks on this list have reported similar stalls that > were resolved by single-threading access to each Connection. > > -- Mark >
I don't know a protocol yet, but perhaps it is possible to detect such problem with PostgreSQL database and throw SQLException. In this way stablity of Postgresql jdbc driver will be independent from stability of PostgreSQL (jdbc will not hang). If you tell me that it is possible and worth doing then I will try to find a solution and make a patch. Marcin Waldowski Kris Jurka wrote: > > > On Fri, 1 Dec 2006, Marcin Waldowski wrote: > >> Hello. >> >> JDBC version: 8.1-407 JDBC 3 PostgreSQL version: 8.1.5-1 for Windows >> Database encoding: UTF8 >> >> I observed a lock at at >> org.postgresql.core.PGStream.ReceiveChar(PGStream.java:256) >> >> It happens after about hour of our perfomance test (high workload - >> 50 threads are executing various short operations on database) >> >> What can I do to avoid this problem? >> > > This sort of problem has been attributed to the windows port of > postgresql. This thread here resulted in a fix that will be included > in the 8.1.6 release and may fix the problem. > > http://archives.postgresql.org/pgsql-hackers/2006-10/thrd7.php#00289 > > Kris Jurka > >
On Fri, 1 Dec 2006, Marcin Waldowski wrote: > I don't know a protocol yet, but perhaps it is possible to detect such > problem with PostgreSQL database and throw SQLException. In this way > stablity of Postgresql jdbc driver will be independent from stability of > PostgreSQL (jdbc will not hang). If you tell me that it is possible and > worth doing then I will try to find a solution and make a patch. > From the driver's perspective, it's just waiting on the results of a query. It is unable to tell the difference between a query that takes a very, very long time to execute and a query that isn't returning because it is hung in the backend. Perhaps you could try setting statement_timeout in the backend so it would abort after a certain period of time. I'm not sure if this would be able to "unhang" a stuck process, but it's the only thing that I can think of that might work. Kris Jurka