<div dir="ltr">Comments inline:<div class="gmail_extra"><br><div class="gmail_quote">On Thu, Nov 20, 2014 at 4:34 AM, Jay Pipes <span dir="ltr"><<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><br>
</blockquote>
<br>
So while the SELECTs may return different data on successive calls when you use the READ COMMITTED isolation level, the UPDATE statements will continue to return 0 rows affected **if they attempt to change rows that have been changed since the start of the transaction**<br>
<br>
The reason that changing the isolation level to READ COMMITTED appears to work for the code in question:<br>
<br>
<a href="https://github.com/openstack/neutron/blob/master/neutron/plugins/ml2/drivers/helpers.py#L98" target="_blank">https://github.com/openstack/<u></u>neutron/blob/master/neutron/<u></u>plugins/ml2/drivers/helpers.<u></u>py#L98</a><br>
<br>
is likely because the SELECT ... LIMIT 1 query is returning a different row on successive attempts (though since there is no ORDER BY on the query, the returned row of the query is entirely unpredictable (line 112)). Since data from that returned row is used in the UPDATE statement (line 118 and 124), *different* rows are actually being changed by successive UPDATE statements.<br></blockquote><div><br></div><div>Not really, we're updating the same row we've selected. It's ensured by '<span style="color:rgb(51,51,51);font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace;line-height:16.7999992370605px;white-space:pre">raw_segment' </span><span style="color:rgb(51,51,51);line-height:16.7999992370605px;white-space:pre"><font face="arial, helvetica, sans-serif">which actually contains</font></span> 'gre_id' (or similar) attribute.</div><div>So in each iteration we're working with the same row, but in different iterations READ COMMITTED allows us to see different data and hence work with a different row.</div><div> <br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<br>
What this means is that for this *very particular* case, setting the transaction isolation level to READ COMMITTTED will work presumably most of the time on MySQL, but it's not an appropriate solution for the generalized problem domain of the SELECT FOR UPDATE. If you need to issue a SELECT and an UPDATE in a retry loop, and you are attempting to update the same row or rows (for instance, in the quota reservation or resource allocation scenarios), this solution will not work, even with READ COMMITTED. This is why I say it's not really appropriate, and a better general solution is to use separate transactions for each loop in the retry mechanic.</blockquote><div> </div><div>By saying 'this solution will not work' what issues do you mean what exactly? </div><div>Btw, I agree on using separate transaction for each loop, the problem is that transaction is usually not 'local' to the method where the retry loop resides.</div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><br>
The issue is about doing the retry within a single transaction. That's not what I recommend doing. I recommend instead doing short separate transactions instead of long-lived, multi-statement transactions and relying on the behaviour of the DB's isolation level (default or otherwise) to "solve" the problem of reading changes to a record that you intend to update.<br></blockquote><div> </div><div>" instead of long-lived, multi-statement transactions" - that's really what would require quite large code redesign. </div><div>So far finding a way to bring retry logic upper to the stack of nesting transactions seems more appropriate.</div><div><br></div><div>Thanks,</div><div>Eugene.</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<br>
Cheers,<br>
-jay<br>
<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="">
Also, thanks Clint for clarification about example scenario described by<br>
Mike Bayer.<br>
Initially the issue was discovered with concurrent tests on multi master<br>
environment with galera as a DB backend.<br>
<br>
Thanks,<br>
Eugene<br>
<br>
On Thu, Nov 20, 2014 at 12:20 AM, Mike Bayer <<a href="mailto:mbayer@redhat.com" target="_blank">mbayer@redhat.com</a><br></span>
<mailto:<a href="mailto:mbayer@redhat.com" target="_blank">mbayer@redhat.com</a>>> wrote:<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span class="">
On Nov 19, 2014, at 3:47 PM, Ryan Moats <<a href="mailto:rmoats@us.ibm.com" target="_blank">rmoats@us.ibm.com</a><br></span><div><div class="h5">
<mailto:<a href="mailto:rmoats@us.ibm.com" target="_blank">rmoats@us.ibm.com</a>>> wrote:<br>
<br>
><br>
BTW, I view your examples from oslo as helping make my argument for<br>
me (and I don't think that was your intent :) )<br>
<br>
</div></div></blockquote><div><div class="h5">
<br>
I disagree with that as IMHO the differences in producing MM in the<br>
app layer against arbitrary backends (Postgresql vs. DB2 vs. MariaDB<br>
vs. ???) will incur a lot more “bifurcation” than a system that<br>
targets only a handful of existing MM solutions. The example I<br>
referred to in oslo.db is dealing with distinct, non MM backends.<br>
That level of DB-specific code and more is a given if we are<br>
building a MM system against multiple backends generically.<br>
<br>
It’s not possible to say which approach would be better or worse at<br>
the level of “how much database specific application logic do we<br>
need”, though in my experience, no matter what one is trying to do,<br>
the answer is always, “tons”; we’re dealing not just with databases<br>
but also Python drivers that have a vast amount of differences in<br>
behaviors, at every level. On top of all of that, hand-rolled MM<br>
adds just that much more application code to be developed and<br>
maintained, which also claims it will do a better job than mature<br>
(ish?) database systems designed to do the same job against a<br>
specific backend.<br>
<br>
<br>
<br>
</div></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><div class="h5">
<br>
> > My reason for asking this question here is that if the community<br>
> > wants to consider #2, then these problems are the place to start<br>
> > crafting that solution - if we solve the conflicts inherent<br>
with the<br>
> > two conncurrent thread scenarios, then I think we will find that<br>
> > we've solved the multi-master problem essentially "for free”.<br>
><br>
> Maybe I’m missing something, if we learn how to write out a row such<br>
> that a concurrent transaction against the same row doesn’t throw us<br>
> off, where is the part where that data is replicated to databases<br>
> running concurrently on other IP numbers in a way that is atomic<br>
> come out of that effort “for free” ? A home-rolled “multi master”<br>
> scenario would have to start with a system that has multiple<br>
> create_engine() calls, since we need to communicate directly to<br>
> multiple database servers. From there it gets really crazy.<br>
Where’sall that ?<br>
<br>
Boiled down, what you are talking about here w.r.t. concurrent<br>
transactions is really conflict resolution, which is the hardest<br>
part of implementing multi-master (as a side note, using locking in<br>
this case is the equivalent of option #1).<br>
<br>
All I wished to point out is that there are other ways to solve the<br>
conflict resolution that could then be leveraged into a multi-master<br>
scenario.<br>
<br>
As for the parts that I glossed over, once conflict resolution is<br>
separated out, replication turns into a much simpler problem with<br>
well understood patterns and so I view that part as coming<br>
"for free."<br>
<br>
Ryan<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br></div></div>
<mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</blockquote><span class="">
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br></span>
<mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><span class=""><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
<br>
</span></blockquote><div class=""><div class="h5">
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</div></div></blockquote></div><br></div></div>