<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><br class=""><div><blockquote type="cite" class=""><div class="">On Nov 18, 2014, at 1:38 PM, Eugene Nikanorov <<a href="mailto:enikanorov@mirantis.com" class="">enikanorov@mirantis.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div dir="ltr" class="">Hi neutron folks,<div class=""><br class=""></div><div class="">There is an ongoing effort to refactor some neutron DB logic to be compatible with galera/mysql which doesn't support locking (with_lockmode('update')).</div><div class=""><br class=""></div><div class="">Some code paths that used locking in the past were rewritten to retry the operation if they detect that an object was modified concurrently.</div><div class="">The problem here is that all DB operations (CRUD) are performed in the scope of some transaction that makes complex operations to be executed in atomic manner.</div><div class="">For mysql the default transaction isolation level is 'REPEATABLE READ' which means that once the code issue a query within a transaction, this query will return the same result while in this transaction (e.g. the snapshot is taken by the DB during the first query and then reused for the same query).</div><div class="">In other words, the retry logic like the following will not work:</div><div class=""><br class=""></div><div class="">def allocate_obj():</div><div class=""> with session.begin(subtrans=True):</div><div class=""> for i in xrange(n_retries):</div><div class=""> obj = session.query(Model).filter_by(filters)</div><div class=""> count = session.query(Model).filter_by(id=<a href="http://obj.id/" class="">obj.id</a>).update({'allocated': True})</div><div class=""> if count:</div><div class=""> return obj</div><div class=""><br class=""></div><div class="">since usually methods like allocate_obj() is called from within another transaction, we can't simply put transaction under 'for' loop to fix the issue.</div></div></div></blockquote><div><br class=""></div><div>has this been confirmed? the point of systems like repeatable read is not just that you read the “old” data, it’s also to ensure that updates to that data either proceed or fail explicitly; locking is also used to prevent concurrent access that can’t be reconciled. A lower isolation removes these advantages. </div><div><br class=""></div><div>I ran a simple test in two MySQL sessions as follows:</div><div><br class=""></div><div>session 1:</div><div><br class=""></div><div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> create table some_table(data integer) engine=innodb;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 0 rows affected (0.01 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53); min-height: 16px;" class=""><br class=""></div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> insert into some_table(data) values (1);</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 1 row affected (0.00 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53); min-height: 16px;" class=""><br class=""></div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> begin;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 0 rows affected (0.00 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53); min-height: 16px;" class=""><br class=""></div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> select data from some_table;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">| data |</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">| 1 |</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">1 row in set (0.00 sec)</div><div class=""><br class=""></div><div class=""><br class=""></div><div class="">session 2:</div><div class=""><br class=""></div><div class=""><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> begin;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 0 rows affected (0.00 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53); min-height: 16px;" class=""><br class=""></div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> update some_table set data=2 where data=1;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 1 row affected (0.00 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Rows matched: 1 Changed: 1 Warnings: 0</div></div><div class=""><br class=""></div><div class="">then back in session 1, I ran:</div><div class=""><br class=""></div><div class=""><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> update some_table set data=3 where data=1;</div></div><div class=""><br class=""></div><div class="">this query blocked; that’s because session 2 has placed a write lock on the table. this is the effect of repeatable read isolation.</div><div class=""><br class=""></div><div class="">while it blocked, I went to session 2 and committed the in-progress transaction:</div><div class=""><br class=""></div><div class=""><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> commit;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 0 rows affected (0.00 sec)</div></div></div><div><br class=""></div><div>then session 1 unblocked, and it reported, correctly, that zero rows were affected:</div><div><br class=""></div><div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Query OK, 0 rows affected (7.29 sec)</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">Rows matched: 0 Changed: 0 Warnings: 0</div><div class=""><br class=""></div></div><div>the update had not taken place, as was stated by “rows matched":</div><div><br class=""></div><div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">mysql> select * from some_table;</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">| data |</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">| 1 |</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">+------+</div><div style="margin: 0px; font-family: Consolas; color: rgb(200, 198, 202); background-color: rgb(6, 7, 53);" class="">1 row in set (0.00 sec)</div></div><div><br class=""></div><div>the code in question would do a retry at this point; it is checking the number of rows matched, and that number is accurate.</div><div><br class=""></div><div>if our code did *not* block at the point of our UPDATE, then it would have proceeded, and the other transaction would have overwritten what we just did, when it committed. I don’t know that read committed is necessarily any better here.</div><div><br class=""></div><div>now perhaps, with Galera, none of this works correctly. That would be a different issue in which case sure, we should use whatever isolation is recommended for Galera. But I’d want to potentially peg it to the fact that Galera is in use, or not.</div><div><br class=""></div><div>would love also to hear from Jay Pipes on this since he literally wrote the book on MySQL ! :)</div><div><br class=""></div><div><br class=""></div></div></body></html>