<div dir="ltr"><div>Hi, Moshe,<br><br></div>I think you can try the following way to
change the isolation_level of a specific transaction when using oslo.db.
We apply it to solve the concurrency issue happen in Senlin project.
Hope that will be helpful.<br><br>1272 session = _session(context)<br>1273 session.begin()<br>1274 session.connection(<br>1275 execution_options={'isolation_level': 'SERIALIZABLE'})<br><br><br><a href="https://review.openstack.org/#/c/252231/11/senlin/db/sqlalchemy/api.py">https://review.openstack.org/#/c/252231/11/senlin/db/sqlalchemy/api.py</a> #Line1214<br><br><br><div class="gmail_extra"><br><div class="gmail_quote">2015-12-08 21:13 GMT+08:00 ELISHA, Moshe (Moshe) <span dir="ltr"><<a href="mailto:moshe.elisha@alcatel-lucent.com" target="_blank">moshe.elisha@alcatel-lucent.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="white" link="blue" vlink="purple" lang="EN-US">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">Thank you, Gordon for your reply.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">In your patch I see that you are setting the isolation_level to REPEATABLE_READ. We are trying to change the isolation level to READ_COMMITTED.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">I like to refer you to this performance article[1] that determines that READ_COMMITTED is usually better than REPEATABLE_READ.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">In addition, maybe an additional reason for your slowness is that you do it when you get the connection – so you do an extra DB command that sets the isolation
level.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">My patch is changing the default isolation level for all connections upfront.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d">[1]
<a href="https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/" target="_blank">
https://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/</a><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1f497d"><u></u> <u></u></span></p>
<div>
<div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext"> gord chung [mailto:<a href="mailto:gord@live.ca" target="_blank">gord@live.ca</a>]
<br>
<b>Sent:</b> Tuesday, December 08, 2015 2:50 PM<br>
<b>To:</b> <a href="mailto:openstack-dev@lists.openstack.org" target="_blank">openstack-dev@lists.openstack.org</a><br>
<b>Subject:</b> Re: [openstack-dev] [oslo.db][sqlalchemy][mistral] Configuring default transaction isolation level<u></u><u></u></span></p>
</div>
</div><div><div class="h5">
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal" style="margin-bottom:12.0pt">we had this in ceilometer events. you can see it here:
<a href="https://github.com/openstack/ceilometer/commit/898cd3d036c4358aa16f7b3e2028365dc9829213" target="_blank">
https://github.com/openstack/ceilometer/commit/898cd3d036c4358aa16f7b3e2028365dc9829213</a><br>
<br>
note, that patch is removing it because it slowed everything way down because of locking. if you can avoid it, avoid it.<u></u><u></u></p>
<div>
<p class="MsoNormal">On 08/12/2015 7:28 AM, Renat Akhmerov wrote:<u></u><u></u></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<p class="MsoNormal">Hi, <u></u><u></u></p>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">Moshe, thanks a lot for bringing this up. I remember I tried to find a way to change isolation level per connection but also was unable to do that.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<div>
<p class="MsoNormal">An advice from oslo team would be very helpful.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">Renat Akhmerov<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal">@ Mirantis Inc.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal">On 08 Dec 2015, at 13:41, ELISHA, Moshe (Moshe) <<a href="mailto:moshe.elisha@alcatel-lucent.com" target="_blank">moshe.elisha@alcatel-lucent.com</a>> wrote:<u></u><u></u></p>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Hi,<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">We at Mistral want to move from the default transaction isolation level of REPEATABLE READ to READ COMMITTED as part of a bugfix[1].<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">I did not find a way to pass the isolation level to sqlachemy using oslo.db and the current solution is to use monkey-patching[2] that adds the “isolation_level” property.<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Is there currently a better way to set the default isolation level?<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">If not – I will create a BP for it.<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">Thanks.<u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">[1]<span> </span><a href="https://review.openstack.org/#/c/253819" target="_blank">https://review.openstack.org/#/c/253819</a><u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif"">[2]<span> </span><a href="https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py" target="_blank">https://review.openstack.org/#/c/253819/11/mistral/db/sqlalchemy/base.py</a><u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif""> <u></u><u></u></span></p>
</div>
<p class="MsoNormal"><span style="font-size:9.0pt;font-family:"Helvetica","sans-serif"">__________________________________________________________________________<br>
OpenStack Development Mailing List (not for usage questions)<br>
Unsubscribe:<span> </span></span><a href="mailto:OpenStack-dev-request@lists.openstack.org" target="_blank">OpenStack-dev-request@lists.openstack.org</a><span style="font-size:9.0pt;font-family:"Helvetica","sans-serif"">?subject:unsubscribe<br>
</span><a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank"><span style="font-size:9.0pt;font-family:"Helvetica","sans-serif";color:purple">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</span></a><u></u><u></u></p>
</div>
</blockquote>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
<p class="MsoNormal"><br>
<br>
<br>
<u></u><u></u></p>
<pre>__________________________________________________________________________<u></u><u></u></pre>
<pre>OpenStack Development Mailing List (not for usage questions)<u></u><u></u></pre>
<pre>Unsubscribe: <a href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe" target="_blank">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a><u></u><u></u></pre>
<pre><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><u></u><u></u></pre>
</blockquote>
<p class="MsoNormal"><br>
<br>
<u></u><u></u></p>
<pre>-- <u></u><u></u></pre>
<pre>gord<u></u><u></u></pre>
</div></div></div>
</div>
<br>__________________________________________________________________________<br>
OpenStack Development Mailing List (not for usage questions)<br>
Unsubscribe: <a href="http://OpenStack-dev-request@lists.openstack.org?subject:unsubscribe" rel="noreferrer" target="_blank">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" rel="noreferrer" target="_blank">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature">Best regards,<div><br></div><div>Yanyan</div></div>
</div></div>