<html><body>
<p><font size="2" face="sans-serif">I was waiting for this because I think I may have a slightly different (and outside of the box) view on how to approach a solution to this.</font><br>
<br>
<font size="2" face="sans-serif">Conceptually (at least in my mind) there isn't a whole lot of difference between how the example below (i.e. updates from two concurrent threads) is handled</font><br>
<font size="2" face="sans-serif">and how/if neutron wants to support a multi-master database scenario (which in turn lurks in the background when one starts thinking/talking about multi-region support).</font><br>
<br>
<font size="2" face="sans-serif">If neutron wants to eventually support multi-master database scenarios, I see two ways to go about it:</font><br>
<br>
<font size="2" face="sans-serif">1) Defer multi-master support to the database itself.</font><br>
<font size="2" face="sans-serif">2) Take responsibility for managing the conflict resolution inherent in multi-master scenarios itself.</font><br>
<br>
<font size="2" face="sans-serif">The first approach is certainly simpler in the near term, but it has the down side of restricting the choice of databases to those that have solved multi-master and further, may lead to code bifurcation based on possibly different solutions to the conflict resolution scenarios inherent in multi-master.</font><br>
<br>
<font size="2" face="sans-serif">The second approach is certainly more complex as neutron assumes more responsibility for its own actions, but it has the advantage that (if done right) would be transparent to the underlying databases (with all that implies)</font><br>
<br>
<font size="2" face="sans-serif">My reason for asking this question here is that if the community wants to consider #2, then these problems are the place to start crafting that solution - if we solve the conflicts inherent with the two conncurrent thread scenarios, then I think we will find that we've solved the multi-master problem essentially "for free".</font><br>
<br>
<font size="2" face="sans-serif">Ryan Moats</font><br>
<br>
<tt><font size="2">Mike Bayer <mbayer@redhat.com> wrote on 11/19/2014 12:05:35 PM:<br>
<br>
> From: Mike Bayer <mbayer@redhat.com></font></tt><br>
<tt><font size="2">> To: "OpenStack Development Mailing List (not for usage questions)" <br>
> <openstack-dev@lists.openstack.org></font></tt><br>
<tt><font size="2">> Date: 11/19/2014 12:05 PM</font></tt><br>
<tt><font size="2">> Subject: Re: [openstack-dev] [Neutron] DB: transaction isolation and<br>
> related questions</font></tt><br>
<tt><font size="2">> <br>
> On Nov 18, 2014, at 1:38 PM, Eugene Nikanorov <enikanorov@mirantis.com> wrote:</font></tt><br>
<tt><font size="2">> <br>
> Hi neutron folks,</font></tt><br>
<tt><font size="2">> <br>
> There is an ongoing effort to refactor some neutron DB logic to be <br>
> compatible with galera/mysql which doesn't support locking <br>
> (with_lockmode('update')).</font></tt><br>
<tt><font size="2">> <br>
> Some code paths that used locking in the past were rewritten to <br>
> retry the operation if they detect that an object was modified concurrently.</font></tt><br>
<tt><font size="2">> The problem here is that all DB operations (CRUD) are performed in <br>
> the scope of some transaction that makes complex operations to be <br>
> executed in atomic manner.</font></tt><br>
<tt><font size="2">> For mysql the default transaction isolation level is 'REPEATABLE <br>
> READ' which means that once the code issue a query within a <br>
> transaction, this query will return the same result while in this <br>
> transaction (e.g. the snapshot is taken by the DB during the first <br>
> query and then reused for the same query).</font></tt><br>
<tt><font size="2">> In other words, the retry logic like the following will not work:</font></tt><br>
<tt><font size="2">> <br>
> def allocate_obj():</font></tt><br>
<tt><font size="2">> with session.begin(subtrans=True):</font></tt><br>
<tt><font size="2">> for i in xrange(n_retries):</font></tt><br>
<tt><font size="2">> obj = session.query(Model).filter_by(filters)</font></tt><br>
<tt><font size="2">> count = session.query(Model).filter_by(id=obj.id<br>
> ).update({'allocated': True})</font></tt><br>
<tt><font size="2">> if count:</font></tt><br>
<tt><font size="2">> return obj</font></tt><br>
<tt><font size="2">> <br>
> since usually methods like allocate_obj() is called from within <br>
> another transaction, we can't simply put transaction under 'for' <br>
> loop to fix the issue.</font></tt><br>
<tt><font size="2">> <br>
> has this been confirmed? the point of systems like repeatable read <br>
> is not just that you read the “old” data, it’s also to ensure that <br>
> updates to that data either proceed or fail explicitly; locking is <br>
> also used to prevent concurrent access that can’t be reconciled. A <br>
> lower isolation removes these advantages. </font></tt><br>
<tt><font size="2">> <br>
> I ran a simple test in two MySQL sessions as follows:</font></tt><br>
<tt><font size="2">> <br>
> session 1:</font></tt><br>
<tt><font size="2">> <br>
> mysql> create table some_table(data integer) engine=innodb;</font></tt><br>
<tt><font size="2">> Query OK, 0 rows affected (0.01 sec)</font></tt><br>
<tt><font size="2">> <br>
> mysql> insert into some_table(data) values (1);</font></tt><br>
<tt><font size="2">> Query OK, 1 row affected (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> mysql> begin;</font></tt><br>
<tt><font size="2">> Query OK, 0 rows affected (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> mysql> select data from some_table;</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> | data |</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> | 1 |</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> 1 row in set (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> session 2:</font></tt><br>
<tt><font size="2">> <br>
> mysql> begin;</font></tt><br>
<tt><font size="2">> Query OK, 0 rows affected (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> mysql> update some_table set data=2 where data=1;</font></tt><br>
<tt><font size="2">> Query OK, 1 row affected (0.00 sec)</font></tt><br>
<tt><font size="2">> Rows matched: 1 Changed: 1 Warnings: 0</font></tt><br>
<tt><font size="2">> <br>
> then back in session 1, I ran:</font></tt><br>
<tt><font size="2">> <br>
> mysql> update some_table set data=3 where data=1;</font></tt><br>
<tt><font size="2">> <br>
> this query blocked; that’s because session 2 has placed a write <br>
> lock on the table. this is the effect of repeatable read isolation.</font></tt><br>
<tt><font size="2">> <br>
> while it blocked, I went to session 2 and committed the in-progress <br>
> transaction:</font></tt><br>
<tt><font size="2">> <br>
> mysql> commit;</font></tt><br>
<tt><font size="2">> Query OK, 0 rows affected (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> then session 1 unblocked, and it reported, correctly, that zero rows<br>
> were affected:</font></tt><br>
<tt><font size="2">> <br>
> Query OK, 0 rows affected (7.29 sec)</font></tt><br>
<tt><font size="2">> Rows matched: 0 Changed: 0 Warnings: 0</font></tt><br>
<tt><font size="2">> <br>
> the update had not taken place, as was stated by “rows matched":</font></tt><br>
<tt><font size="2">> <br>
> mysql> select * from some_table;</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> | data |</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> | 1 |</font></tt><br>
<tt><font size="2">> +------+</font></tt><br>
<tt><font size="2">> 1 row in set (0.00 sec)</font></tt><br>
<tt><font size="2">> <br>
> the code in question would do a retry at this point; it is checking <br>
> the number of rows matched, and that number is accurate.</font></tt><br>
<tt><font size="2">> <br>
> if our code did *not* block at the point of our UPDATE, then it <br>
> would have proceeded, and the other transaction would have <br>
> overwritten what we just did, when it committed. I don’t know that<br>
> read committed is necessarily any better here.</font></tt><br>
<tt><font size="2">> <br>
> now perhaps, with Galera, none of this works correctly. That would <br>
> be a different issue in which case sure, we should use whatever <br>
> isolation is recommended for Galera. But I’d want to potentially <br>
> peg it to the fact that Galera is in use, or not.</font></tt><br>
<tt><font size="2">> <br>
> would love also to hear from Jay Pipes on this since he literally <br>
> wrote the book on MySQL ! :)</font></tt><br>
<tt><font size="2">> <br>
> _______________________________________________<br>
> OpenStack-dev mailing list<br>
> OpenStack-dev@lists.openstack.org<br>
> <a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
</font></tt></body></html>