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
