[Pgpool-general] JDBC autocommit and hanging locks
Tatsuo Ishii
ishii at sraoss.co.jp
Sat Feb 27 23:35:36 UTC 2010
I know we have problem with autocommit is on. My point is, we have
problem with explicit transaction as well if we do not commit the
transaction. Wait... in this case PostgreSQL has same problem as
pgpool-II. Ok, I think I understand your problem.
Still I think following is neccessary, but in the mean time I will
check why pgpool fails to forward sync message sent from client.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> IMO what we need is, not to send parse/bind message to
> unneccessary backends in load balance mode.
> It's not an explicit transaction. The JDBC driver is operating in
> "autocommit" mode (i.e. connection.setAutoCommit(true) which is the
> default). What happens is that pgpool-II sends a parse message to both
> backend servers but, when the implicit transaction is complete, it
> only sends a sync mesasge to one of the backend servers. Note that if
> I set autocommit to false and issue the commit myself, there is no
> problem, but then the select is not load balanced because pgpool
> assumes the select is part of a larger transaction.
>
> On Sat, Feb 27, 2010 at 9:42 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > 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