[Pgpool-general] JDBC autocommit and hanging locks
Tatsuo Ishii
ishii at sraoss.co.jp
Sat Feb 27 15:42:03 UTC 2010
I'm confused. Even if pgpool-II sends sync message to one of the
backend servers, backend will not close the transaction if it's in an
explicit transaction. Thus the shared lock will remain, right? I think
just sending sync message to all backend servers does not solve the
whole problem. IMO what we need is, not to send parse/bind message to
unneccessary backends in load balance mode.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> 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
> >
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
More information about the Pgpool-general
mailing list