<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix"><br>
Tokens are, for the most part, immutable. Once they are written,
they don't change except if they get revoked. This is a fairly
rare occurance, but it does happen.<br>
<br>
Deleting tokens based on age should be fairly straight forward,
and locks should not need to be held for a significant amount of
time.<br>
<br>
My guess, however, is that the problem is SQL Alchemy:<br>
<br>
query = session.query(TokenModel)<br>
query = query.filter(TokenModel.expires <
timeutils.utcnow())<br>
query.delete(synchronize_session=False)<br>
<br>
If it is doing a fetch and then the delete, then the rows would be
held for a short period of time.<br>
<br>
Direct SQL might be a better approach: prepare a statement:<br>
<br>
"delete from token where expires < $1"<br>
<br>
and then bind and execute in one command.<br>
<br>
However, it seems to me that the conflict detection is the
problem. I don't know if there is a way to state "ignore any
future queries that would match this criteria." It does seem to
me that even doing this degree of conflict detection is somewhat
violating the principal of Isolation.<br>
<br>
There might be an approach using table partitioning as well, where
you only write to partition one, and delete from partition 2, and
then swap.<br>
<br>
<br>
<br>
<br>
<br>
On 07/08/2013 09:13 PM, Robert Collins wrote:<br>
</div>
<blockquote
cite="mid:CAJ3HoZ1XJzCCDHcEwSbsCzt41+VOskaj1-J5JxPXEa=wbTErrQ@mail.gmail.com"
type="cite">
<div class="gmail_quote">On 9 July 2013 12:32, Adam Young <span
dir="ltr"><<a moz-do-not-send="true"
href="mailto:ayoung@redhat.com" target="_blank">ayoung@redhat.com</a>></span>
wrote:<br>
<div> </div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im">
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">* I am
asking about MySQL.. presumably a "real" database.<br>
</blockquote>
</div>
I have to admit I am a bit of a Postgresql Bigot. I don't
really consider MySQL a real database, althought it has
improved a lot over the years. I am not up to speed
on"InnoDB's gap locking behavior" but it is not something I
would expect to be a problem in Postgresql.<br>
</blockquote>
<div><br>
PostgreSQL has similar but different characteristics,
particular the latest iteration of isolation behaviour where
locks are held on *the result of a query*, not on 'specific
rows returned' - the difference being that adding a new row
that matches the query for rows to delete, would encounter a
conflict. You also need to delete small numbers of rows at a
time, though the reason in the plumbing is different. There
are some nasty interlocks you can cause with very large
deletes and autovacuum too - if you trigger deadlock detection
it still takes /minutes/ to detect and cleanup, whereas we
want sub-second liveness.<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">once every
second would be strange indeed. I would think maybe once
every five minutes or so. Schedule your clean up IAW your
deployment and usage.<br>
</blockquote>
<div><br>
5m intervals exacerbate the issue until it's solved. If the
cleanup deletes no more than (say) 1000 rows per iteration, it
could run every 5 minutes but when run keep going until the db
is cleaned.<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
Deleting a chunk of tokens in bulk would be preferable to
doing client side iteration, I can;t see how that would not be
the case.<br>
</blockquote>
<div><br>
right, so I think Clint prefers that too, the question is how
to get sqlalchemy to output the appropriate sql for postgresql
and mysql, which is different. <br>
</div>
<div><br>
-Rob<br>
</div>
</div>
<br>
-- <br>
Robert Collins <<a moz-do-not-send="true"
href="mailto:rbtcollins@hp.com" target="_blank">rbtcollins@hp.com</a>><br>
Distinguished Technologist<br>
HP Cloud Services
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
OpenStack-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev@lists.openstack.org">OpenStack-dev@lists.openstack.org</a>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
</pre>
</blockquote>
<br>
</body>
</html>