<div dir="ltr">I have our DBAs looked into this and got some findings and feedback: <div><br></div><div><span style="color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">I see an deadlock for:</span><br style="color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px"><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">(1) = delete from records where domain_id=217<br></p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">(2) = delete from records where domain_id=217</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">So something tries to delete everything for domain=217 while other transactions have a lock on some of the records.</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">Adding an ORDER BY to the delete query for whole domains might prevent that from happening.</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)"><br></p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)">I searched PowerDNS 4.0.5 source code and find the following occurrences of the matching SQL statements. are they configurable or we have to patch the source code in order to test?</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;background-color:rgb(245,245,245)"><br></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000" style=""><span class="gmail-Apple-converted-space" style=""> </span>declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove all empty non-terminals from zone", "delete from records where domain_id=:domain_id and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-empty-non-terminal-query", "delete empty non-terminal from zone", "delete from records where domain_id=:domain_id and name=:qname and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-zone-query", "", "delete from records where domain_id=:domain_id");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-rrset-query", "", "delete from records where domain_id=:domain_id and name=:qname and type=:qtype");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-names-query", "", "delete from records where domain_id=:domain_id and name=:qname");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove all empty non-terminals from zone", "delete from records where domain_id=? and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-empty-non-terminal-query", "delete empty non-terminal from zone", "delete from records where domain_id=? and name=? and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-zone-query","", "delete from records where domain_id=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-rrset-query","","delete from records where domain_id=? and name=? and type=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-names-query","","delete from records where domain_id=? and name=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove all empty non-terminals from zone", "delete from records where domain_id=:domain_id and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-empty-non-terminal-query", "delete empty non-terminal from zone", "delete from records where domain_id=:domain_id and name=:qname and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-zone-query", "", "delete from records where domain_id=:domain_id");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-rrset-query", "", "delete from records where domain_id=:domain_id and name=:qname and type=:qtype");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-names-query", "", "delete from records where domain_id=:domain_id and name=:qname");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "remove-empty-non-terminals-from-zone-query", "remove all empty non-terminals from zone", "delete from records where domain_id=? and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix, "delete-empty-non-terminal-query", "delete empty non-terminal from zone", "delete from records where domain_id=? and name=? and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-zone-query","", "delete from records where domain_id=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-rrset-query","","delete from records where domain_id=? and name=? and type=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-names-query","","delete from records where domain_id=? and name=?");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"remove-empty-non-terminals-from-zone-query", "remove all empty non-terminals from zone", "delete from records where domain_id=$1 and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-empty-non-terminal-query", "delete empty non-terminal from zone", "delete from records where domain_id=$1 and name=$2 and type is null");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-zone-query","", "delete from records where domain_id=$1");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000"><span class="gmail-Apple-converted-space"> </span>declare(suffix,"delete-rrset-query","","delete from records where domain_id=$1 and name=$2 and type=$3");</font></span></p><p style="margin:10px 0px 0px;padding:0px;font-family:Arial,sans-serif">
</p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000" style=""><span class="gmail-Apple-converted-space" style=""> </span>declare(suffix,"delete-names-query","","delete from records where domain_id=$1 and name=$2");</font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><span class="gmail-s1" style="font-variant-ligatures:no-common-ligatures;background-color:rgb(255,255,255)"><font color="#000000" style=""><br></font></span></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><font color="#000000"><span style="font-variant-ligatures:no-common-ligatures">Thanks,</span></font></p><p class="gmail-p1" style="margin:0px;font-style:normal;font-variant-caps:normal;font-variant-numeric:normal;font-variant-east-asian:normal;font-weight:normal;font-stretch:normal;line-height:normal;font-family:"Andale Mono""><font color="#000000"><span style="font-variant-ligatures:no-common-ligatures">Steve</span></font></p></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Feb 6, 2018 at 3:26 PM, Steve Zeng <span dir="ltr"><<a href="mailto:steve.zeng@booking.com" target="_blank">steve.zeng@booking.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word">Hi,<br><br>Our PDNS environment is setup on Centos 7 + PDNS Authoritative 4.0.5 + <span>mysql-community 5.7</span><div><br>It is noticed that there are regular PowerDNS failure logs below:<br><br>2018-02-04T04:29:07.193742+01:<wbr>00 <a href="http://mpdns.example.com" target="_blank">mpdns.example.com</a> pdns_server[40761]: Feb 04 04:29:07 Unable to AXFR zone ‘<a href="http://lom.example.com" target="_blank">lom.example.com</a>' from remote ‘x.x.x.x:53' (PDNSException): Database failed to start transaction: Could not execute mysql statement: delete from records where domain_id=?: Lock wait timeout exceeded; try restarting transaction<div><br></div><div>I looked at PowerDNS list of settings at <a href="https://doc.powerdns.com/md/authoritative/backend-generic-mysql/" target="_blank">https://doc.powerdns.com/<wbr>md/authoritative/backend-<wbr>generic-mysql/</a> and do not see any configurable settings for it. Our correct pdns.conf can be seen below:</div></div><div><br></div><span>launch=gmysql<br>gmysql-host=y.y.y.y<br>gmysql-user=auser<br>gmysql-dbname=pdns<br>gmysql-password=********<br>gmysql-timeout=0<br></span><span><br></span><div><span>not sure if this cause is on PowerDNS side or MySQL database side. can anybody shed a light here? </span></div><div><span><br></span></div><div><span>Thanks,</span></div><div><span>Steve</span></div><div><span><br></span></div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><span style="display:block;font-size:11.0pt;font-family:Century Gothic;color:#003580"><div style="color:rgb(0,53,128);font-family:Arial,Helvetica,sans-serif;font-weight:bold;font-size:15px"><div>Steve Zeng</div><div style="font-weight:normal;font-size:13px;color:rgb(0,174,239)">Linux System Administrator<br><br></div><div style="font-weight:normal;font-size:13px;color:rgb(102,102,102)">Booking.com (USA) Inc.<br>220 W. Mercer St. #500 Seattle WA 98119 United States of America</div><div style="font-weight:normal;font-size:13px;color:rgb(102,102,102)"><span style="color:rgb(0,174,239)">Direct </span>+12062094634<br></div><div style="font-weight:normal;font-size:13px;color:rgb(102,102,102)"><div style="font-weight:bold;font-size:16px;color:rgb(0,53,128)"><a href="http://www.booking.com/" style="color:rgb(0,127,255);background-image:initial;background-position:initial;background-repeat:initial" target="_blank"><img src="http://bstatic.com/static/img/siglogo.jpg" alt="Booking.com" title="Booking.com"></a></div><span style="font-size:11px">The world's #1 accommodation site <br>43 languages, 198+ offices worldwide, 120,000+ global destinations, 1,550,000+ room nights booked every day <br>No booking fees, best price always guaranteed <br>Subsidiary of the Priceline Group (NASDAQ: PCLN) </span></div></div></span></div>
</div>