[Pgpool-general] JDBC autocommit and hanging locks

[Mike Bresnahan]

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();
>        }
> }
>