<font size=2 face="sans-serif">Hello Mr. Ishii,</font>
<br>
<br><font size=2 face="sans-serif">we have attempted to create a selfcontained
testcase, but have been unsuccessful so far. We understand how pgpool acquires
locks in theory, but it seems that what we are seeing is different. We
have summarized our findings here: </font><a href=http://pastebin.com/9f6gjxLA><font size=2 face="sans-serif">http://pastebin.com/9f6gjxLA</font></a>
<br>
<br><font size=2 face="sans-serif">It seems that pgpool child 7606 communicates
with process 26453 on the .202 node and process 17789 on the .204 node.
We can see from the output from postgres that process 26453 is waiting
for the lock on the .202 server, while 17789 has the lock on the .204 server.
This means that pgpool child 7606 will wait until process 26453 can obtain
the lock on the .202 server. </font>
<br>
<br><font size=2 face="sans-serif">At the same time, we can see that pgpool
child 7681 communicates with process 23451 on the .202 server and process
12464 on the .204 server. We can see from the output from postgres that
the process 23451 has its lock on the .202 server but process 12464 is
waiting for it on the .204 server. This means that pgpool child 7681 will
wait until process 12464 can obtain the lock on the .204 server. </font>
<br>
<br><font size=2 face="sans-serif">Since pgpool child 7606 via process
17789 has the lock on the .204 server, it blocks pgpool child 7681 from
completing. Since pgpool child 7681 via process 23451 has the lock on the
.202 server, it blocks pgpool child 7606 from completing. This seems to
be a classic deadlock.</font>
<br>
<br><font size=2 face="sans-serif">We have spent a fair amount of time
debugging this situation, and we would really appreciate feedback on our
situation.</font>
<br>
<br><font size=2 face="sans-serif">Is there any information that would
aid you in providing us with this kind of feedback?</font>
<br>
<br><font size=2 face="sans-serif">Kind regards,</font>
<br><font size=2 face="sans-serif">Fredrik & Friends</font>
<br>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td width=40%><font size=1 face="sans-serif"><b>Tatsuo Ishii <ishii@postgresql.org></b>
</font>
<p><font size=1 face="sans-serif">2013/01/16 07:54</font>
<td width=59%>
<table width=100%>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">To</font></div>
<td><font size=1 face="sans-serif">Fredrik.HuitfeldtMadsen@schneider-electric.com</font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">cc</font></div>
<td><font size=1 face="sans-serif">tgl@sss.pgh.pa.us, nagata@sraoss.co.jp,
pgsql-general@postgresql.org, pgpool-general@pgpool.net</font>
<tr valign=top>
<td>
<div align=right><font size=1 face="sans-serif">Subject</font></div>
<td><font size=1 face="sans-serif">Re: [pgpool-general: 1315] Re: [GENERAL]
Database connections seemingly hanging</font></table>
<br>
<table>
<tr valign=top>
<td>
<td></table>
<br></table>
<br>
<br>
<br><tt><font size=2>> It seems that the root cause was that pgpool
acquired the locks in the <br>
> wrong order. If the resource is called A it seems that pgpool allows
child <br>
> X to acquire A on node1 and at the same time, child Y acquires A on
node2. <br>
> This leaves X wanting A on node2 and Y wanting A on node1. This leaves
<br>
> both children hanging indefinitely. It also leaves both postgres'es
<br>
> blissfully unaware of the deadlock, whereby it escapes postgres'es
<br>
> deadlock detection.<br>
<br>
That's hard to believe for me. For any query, pgpool sends it to the<br>
master node (node1 in your case) first and waits until the node<br>
returns response by using select(2) on the socket to PostgreSQL<br>
backend. After someting comes from the socket, then pgpool issues to<br>
node2. So pgpool never sends query to master node(node1) and node2<br>
concurrently. This is a classical technique to avoid a cross node<br>
dead lock situation.<br>
<br>
If your explation is correct, pgpool easily goes into dead lock<br>
situation even by using simple pgbench query.<br>
<br>
Could you please show me self-contained test case?<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: </font></tt><a href=http://www.sraoss.co.jp/index_en.php><tt><font size=2>http://www.sraoss.co.jp/index_en.php</font></tt></a><tt><font size=2><br>
Japanese: </font></tt><a href=http://www.sraoss.co.jp/><tt><font size=2>http://www.sraoss.co.jp</font></tt></a><tt><font size=2><br>
<br>
______________________________________________________________________<br>
This email has been scanned by the Symantec Email Security.cloud service.<br>
______________________________________________________________________<br>
</font></tt>
<br>