[Pgpool-general] JDBC autocommit and hanging locks

[Mike Bresnahan]

Correction, I am using postgresql.x86_64 8.3.7-1.fc9 and
postgresql-pgpool-II.x86_64 2.2.2-1.fc9 on Fedora 9.

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