[pgpool-general: 1320] Re: [GENERAL] Database connections seemingly hanging

[Tatsuo Ishii]

> It seems that the root cause was that pgpool acquired the locks in the 
> wrong order. If the resource is called A it seems that pgpool allows child 
> X to acquire A on node1 and at the same time, child Y acquires A on node2. 
> This leaves X wanting A on node2 and Y wanting A on node1. This leaves 
> both children hanging indefinitely. It also leaves both postgres'es 
> blissfully unaware of the deadlock, whereby it escapes postgres'es 
> deadlock detection.

That's hard to believe for me. For any query, pgpool sends it to the
master node (node1 in your case) first and waits until the node
returns response by using select(2) on the socket to PostgreSQL
backend. After someting comes from the socket, then pgpool issues to
node2.  So pgpool never sends query to master node(node1) and node2
concurrently.  This is a classical technique to avoid a cross node
dead lock situation.

If your explation is correct, pgpool easily goes into dead lock
situation even by using simple pgbench query.

Could you please show me self-contained test case?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp