Skip site navigation (1) Skip section navigation (2)

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

From: Joao Rui Leal <joao(dot)leal(at)ciengis(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Deadlock while using getNotifications() and Statement.executeQuery()
Date: 2008-03-24 17:58:06
Message-ID: 200803241758.07286.joao.leal@ciengis.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Stephen DenneDate: 2008-03-24 20:57:24
Subject: Re: Non-ORM layers over JDBC
Previous:From: Craig RingerDate: 2008-03-24 17:40:55
Subject: Re: Non-ORM layers over JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group