[Pgpool-general] JDBC autocommit and hanging locks

[Tatsuo Ishii]

Theoretically you could reproduce the problem without pgpool-II. 

Session A parses SELECT * FROM foo. Then another session B parses
TRUNCATE foo (or whatever query which tries to grab conflicting lock
against AccessShare lock held by parse), bind and execute it. Session
B will wait until session A finishes the connection. Therefore if the
session A and B are executed in a same thread, it will stuck as you
saw with pgpool-II. The reason why you never observ it with PostgreSQL
is just JDBC interface does not provide such a fine grain API.

To be honest I don't see any point that pgpool-II issues parse message
to *all* DB nodes even in load balance mode. As you suggested it's the
source of the problem. But the implementation decision was made long
time ago by someone else. I would like to rewrite the implementation
in the near future to make the code cleaner and to avoid many problems
including yours.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> I downloaded and compiled pgpool-II-2.3.2.2 and observed the following behavior:
> 
> replication_mode = true
> load_balance_mode = true
> replicate_select = true
> 
> The select statement is sent to both servers and the sync message is
> sent to one server. Deadlock occurs.
> 
> replication_mode = true
> load_balance_mode = true
> replicate_select = false
> 
> The parse message is sent to both servers, the select statement is
> sent to one server, and the sync message is sent to one server.
> Deadlock occurs.
> 
> replication_mode = true
> load_balance_mode = false
> replicate_select = false
> 
> The parse message is sent to one server, the select statement is sent
> to one server, and the sync message is sent to one server. Test is
> successful, i.e. no deadlock.
> 
> I also downloaded, compiled, and tested pgpool 3.4.1 and it does not
> exhibit the bug.
> 
> Therefore, I think I have discovered a serious bug in pgpool-II that
> is caused when both replication and load balance modes are enabled. I
> looked over the source code and it is not obvious to me where the bug
> is. I'm guessing there is a logic error in the handling of the global
> variable in_load_balance.
> 
> Any help would be greatly appreciated.
> 
> 
> On Thu, Feb 25, 2010 at 2:41 PM, Mike Bresnahan <gudujarlson at gmail.com> wrote:
> > I am using the PostgreSQL 8.4 JDBC driver to connect to a pgpool
> > instance in replication mode fronting a pair of PostgreSQL 8.4
> > servers. When autocommit is true (the default), select statements
> > leave shared locks hanging in PostgreSQL until the connection is
> > closed. The following test case illustrates the problem. Test1
> > succeeds, but test2 deadlocks. When I look in pg_locks when test2 is
> > deadlocked I see that connection1 is holding a shared lock on the
> > table "layer". It appears that the autocommit mechanism is not working
> > properly when going through pgpool. Any help would be greatly
> > appreciated.
> >
> > import org.junit.*;
> > import org.postgresql.jdbc3.Jdbc3PoolingDataSource;
> >
> > public class PGPoolBugTest {
> >
> >        @Before
> >        public void before() throws Exception {
> >                Class.forName("org.postgresql.Driver");
> >                java.sql.Connection connection =
> > java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
> > "zzz", "qqq");
> >                java.sql.Statement statement = connection.createStatement();
> >                statement.executeUpdate("truncate table layer");
> >                statement.executeUpdate("insert into layer values (1,'foo',1,1)");
> >        }
> >
> >        @Test
> >        public void test1() throws Exception {
> >                java.sql.Connection connection1 =
> > java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
> > "zzz", "qqq");
> >                java.sql.Connection connection2 =
> > java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
> > "zzz", "qqq");
> >                connection1.setAutoCommit(false);
> >                java.sql.Statement statement1 = connection1.createStatement();
> >                java.sql.Statement statement2 = connection2.createStatement();
> >                java.sql.ResultSet resultSet1 = statement1.executeQuery("select *
> > from layer");
> >                while (resultSet1.next());
> >                resultSet1.close();
> >                statement1.close();
> >                connection1.commit();
> >                statement2.executeUpdate("truncate table layer");
> >                connection1.close();
> >                connection2.close();
> >        }
> >
> >        @Test
> >        public void test2() throws Exception {
> >                java.sql.Connection connection1 =
> > java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
> > "zzz", "qqq");
> >                java.sql.Connection connection2 =
> > java.sql.DriverManager.getConnection("jdbc:postgresql://xxx:9999/yyy",
> > "zzz", "qqq");
> >                java.sql.Statement statement1 = connection1.createStatement();
> >                java.sql.Statement statement2 = connection2.createStatement();
> >                java.sql.ResultSet resultSet1 = statement1.executeQuery("select *
> > from layer");
> >                while (resultSet1.next());
> >                resultSet1.close();
> >                statement1.close();
> >                statement2.executeUpdate("truncate table layer");
> >                connection1.close();
> >                connection2.close();
> >        }
> > }
> >
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general