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 <[email protected]> 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 <[email protected]> > >> 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 > >> [email protected] > >> http://pgfoundry.org/mailman/listinfo/pgpool-general > > > _______________________________________________ > Pgpool-general mailing list > [email protected] > http://pgfoundry.org/mailman/listinfo/pgpool-general _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
