<div dir="ltr">Wow, lots of feedback in a matter of hours.<div><br></div><div>First of all, reading postgres docs I see that READ COMMITTED is the same as for mysql, so it should address the issue we're discussing:</div><div><br></div><div>"<i class="" style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px">Read Committed</i><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> is the default isolation level in </span><span class="" style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px">PostgreSQL</span><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px">. When a transaction uses this isolation level, a </span><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">SELECT</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> query (without a </span><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">FOR UPDATE/SHARE</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> clause) <b>sees only data committed before the query began (not before TX began - Eugene)</b>; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a </span><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">SELECT</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> query sees a snapshot of the database as of the instant the query begins to run. However, </span><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">SELECT</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> does see the effects of previous updates executed within its own transaction, even though they are not yet committed. <b>Also note that two successive </b></span><b><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">SELECT</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px"> commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first </span><tt class="" style="font-size:12px;color:rgb(0,0,0);line-height:18.2399997711182px">SELECT</tt><span style="color:rgb(0,0,0);font-family:verdana,sans-serif;font-size:12px;line-height:18.2399997711182px">. "</span></b></div><div><font color="#000000" face="verdana, sans-serif"><span style="font-size:12px;line-height:18.2399997711182px"><a href="http://www.postgresql.org/docs/8.4/static/transaction-iso.html">http://www.postgresql.org/docs/8.4/static/transaction-iso.html</a></span></font><br></div><div><br></div><div>So, in my opinion, unless neutron code has parts that rely on 'repeatable read' transaction isolation level (and I believe such code is possible, didn't inspected closely yet), switching to READ COMMITTED is fine for mysql.</div><div><br></div><div>On multi-master scenario: it is not really an advanced use case. It is basic, we need to consider it as a basic and build architecture with respect to this fact. </div><div>"Retry" approach fits well here, however it either requires proper isolation level, or redesign of whole DB access layer.</div><div><br></div><div>Also, thanks Clint for clarification about example scenario described by <span style="font-family:arial,sans-serif;font-size:13px">Mike Bayer.</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">Initially the issue was discovered with concurrent tests on multi master environment with galera as a DB backend.</span></div><div><br></div><div>Thanks,</div><div>Eugene</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Nov 20, 2014 at 12:20 AM, Mike Bayer <span dir="ltr"><<a href="mailto:mbayer@redhat.com" target="_blank">mbayer@redhat.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"><br><div><span class=""><blockquote type="cite"><div>On Nov 19, 2014, at 3:47 PM, Ryan Moats <<a href="mailto:rmoats@us.ibm.com" target="_blank">rmoats@us.ibm.com</a>> wrote:</div><br><div><div><p><font face="sans-serif">> </font><br>
<tt><font>BTW, I view your examples from oslo as helping make my argument for</font></tt><br>
<tt><font>me (and I don't think that was your intent :) )</font></tt><br></p></div></div></blockquote><div><br></div></span>I disagree with that as IMHO the differences in producing MM in the app layer against arbitrary backends (Postgresql vs. DB2 vs. MariaDB vs. ???) will incur a lot more “bifurcation” than a system that targets only a handful of existing MM solutions. The example I referred to in oslo.db is dealing with distinct, non MM backends. That level of DB-specific code and more is a given if we are building a MM system against multiple backends generically. </div><div><br></div><div>It’s not possible to say which approach would be better or worse at the level of “how much database specific application logic do we need”, though in my experience, no matter what one is trying to do, the answer is always, “tons”; we’re dealing not just with databases but also Python drivers that have a vast amount of differences in behaviors, at every level. On top of all of that, hand-rolled MM adds just that much more application code to be developed and maintained, which also claims it will do a better job than mature (ish?) database systems designed to do the same job against a specific backend.</div><div><br></div><div><br></div><div><br><blockquote type="cite"><div><div><div class="h5"><div><p>
<br>
<tt><font>> > 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 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”.</font></tt><br>
<tt><font>> <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. Where’sall that ?</font></tt><br>
<br>
<tt><font>Boiled down, what you are talking about here w.r.t. concurrent</font></tt><br>
<tt><font>transactions is really conflict resolution, which is the hardest</font></tt><br>
<tt><font>part of implementing multi-master (as a side note, using locking in</font></tt><br>
<tt><font>this case is the equivalent of option #1). </font></tt><br>
<br>
<tt><font>All I wished to point out is that there are other ways to solve the</font></tt><br>
<tt><font>conflict resolution that could then be leveraged into a multi-master</font></tt><br>
<tt><font>scenario.</font></tt><br>
<br>
<tt><font>As for the parts that I glossed over, once conflict resolution is</font></tt><br>
<tt><font>separated out, replication turns into a much simpler problem with</font></tt><br>
<tt><font>well understood patterns and so I view that part as coming</font></tt><br>
<tt><font>"for free."</font></tt><br>
<br>
<tt><font>Ryan</font></tt></p></div></div></div>_______________________________________________<span class=""><br>OpenStack-dev mailing list<br><a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.org</a><br><a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br></span></div></blockquote></div><br></div><br>_______________________________________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br></blockquote></div><br></div>