[Pgpool-general] JDBC autocommit and hanging locks

[Mike Bresnahan]

I think you may have missed where the problem is. Pgpool-II never
sends the sync message to one of the backend servers subsequent to the
parse message, therefore the shared locks remain until the connection
is closed (i.e. the transaction is not autocommitted as it should be).
And as far as I can tell, there is no way to end the transaction short
of closing the connection. Calling connection.commit() does not result
in anything being sent to pgpool-II, presumably because the JDBC
driver thinks there is no transaction open. This becomes a serious
problem when connection pooling is also in the picture, because the
connection is returned to the pool instead of closed.  The reason I
don't see this with Pgpool-I and straight JDBC is (presumably) that
the sync message is sent after the select statement is excecuted and
therefore there are no hanging locks.

On Sat, Feb 27, 2010 at 7:51 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 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
>