Deadlock while using getNotifications() and Statement.executeQuery()

Hello!

I'm using more than one thread in my application. In one thread I listen
to notifications from the database and another one checks if the
connection is alive by doing a "select 1".
Sometimes I get a deadlock!!!
I'm using postgresql-8.2-508.jdbc4.jar.

The thread dump gives me this:

Java stack information for the threads listed above:
===================================================
"DB connection select 1":
        at
org.postgresql.core.v3.ProtocolConnectionImpl.setTransactionState(ProtocolConnectionImpl.java:191)
        - waiting to lock <0xb1a00f88> (a
org.postgresql.core.v3.ProtocolConnectionImpl)
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveRFQ(QueryExecutorImpl.java:1654)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1410)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:193)
        - locked <0xb1a033e0> (a org.postgresql.core.v3.QueryExecutorImpl)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:236)
        at
database.DatabaseManager$SelectRunnable.run(DatabaseManager.java:483)
        at java.lang.Thread.run(Thread.java:619)
"Database tables listener":
        at
org.postgresql.core.v3.QueryExecutorImpl.processNotifies(QueryExecutorImpl.java:547)
        - waiting to lock <0xb1a033e0> (a
org.postgresql.core.v3.QueryExecutorImpl)
        at
org.postgresql.core.v3.ProtocolConnectionImpl.getNotifications(ProtocolConnectionImpl.java:68)
        - locked <0xb1a00f88> (a
org.postgresql.core.v3.ProtocolConnectionImpl)
        at
org.postgresql.jdbc2.AbstractJdbc2Connection.getNotifications(AbstractJdbc2Connection.java:1013)
        at
database.DbListenerHandler$DbListener.run(DbListenerHandler.java:134)
        at java.lang.Thread.run(Thread.java:619)

Found 1 deadlock.

I've marked the places where the deadlock occurs with  "\\<<<<<<<<<<<".

In the "DB connection select 1" thread I have:
====================================
success = false;
Statement stmt = null;
try {
   int i = 0;
   stmt = getCon().createStatement();
   ResultSet rs = stmt.executeQuery("SELECT 1");\\<<<<<<<<<<<

   if (rs.next()) {
      i = rs.getInt(1);
   }
    if (i == 1) {
      success = true;
   }
 } catch (SQLException ex) {
 } finally {
   if (stmt != null) {
      try {
         stmt.close();
       } catch (SQLException ex) {
       }
   }
 }
  
====================================

And in the "Database tables listener" thread I have:
====================================
while (...) {
   try {
      PGNotification notifications[]
=pgconn.getNotifications();\\<<<<<<<<<<<
      if (notifications != null) {
	....
      }
      Thread.sleep(300);
   } catch (SQLException sqle) {
	....
   } catch (InterruptedException ie) {
	....
   }
}
====================================

Is this a bug in jdbc-postgres?

Joao Leal

-
Sent via pgsql-jdbc mailing list (pgsql-jdbc@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc