[openstack-dev] [Keystone] Best way to do something MySQL-specific?

Adam Young ayoung at redhat.com
Thu Jul 11 02:14:45 UTC 2013

On 07/09/2013 09:51 PM, Robert Collins wrote:
> On 10 July 2013 11:00, Jay Pipes <jaypipes at gmail.com 
> <mailto:jaypipes at gmail.com>> wrote:
>     >   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.
>     InnoDB and PostgreSQL behave in very different manners regarding
>     locking and transaction isolation, even though they both implement
>     a version of MVCC. In InnoDB's case, its implementation of MVCC is
>     optimized more for storage space (it allows using a series of log
>     records to reconstruct or undo a record to a particular "version"
>     of the record) vs. PostgreSQL, which stores every version of every
>     record in its data space.
> PostgreSQL only keeps enough versions live to ensure the oldest 
> transaction can still read all the versions that were live at it's 
> start: once the transaction is closed any obsolete version kept alive 
> by it can be gc'd or overwritten. I think the distinction you are 
> drawing is that InnoDB only stores deltas between record versions - an 
> interesting tweak. I guess that TOAST mitigates the temporary storage 
> overhead: as a particular TOAST value is immutable, there's no need to 
> copy that data when updating a different value in a row. That won't 
> help with 'update one int in each of a billion rows' case though.
>     AFAIK, PostgreSQL won't issue gap locks like InnoDB because it
>     will simply write a new version of the records -- a version that
>     marks the record as deleted -- to its data files, instead of gap
>     locking to ensure that rows affected by a DELETE statement with an
>     open-ended (or non-existent) WHERE clause can be properly isolated
>     (and rolled back properly in the case of a failure).
> PostgreSQL doesn't do gap locks, but instead you have to deal with 
> http://wiki.postgresql.org/wiki/SSI : the transaction that is deleting 
> 1M rows, for instance, will have a query that may return rows which 
> another transaction is adding; if so one of the two will be rolled 
> back. This is in many ways equivalent from the point of view of 
> writing good SQL that will work well on both systems.

This is not a problem with token cleanup path, though. Tokens are 
cleaned up based on expiry time, a value that is written and never 
changed.  Tokens should never be removed from the database until their 
expiry has been hit, or valid tokens will be denied.

>     In any case, MySQL is certainly a production-capable database like
>     PostgreSQL. It has its quirks and downsides, as does any system,
>     including PostgreSQL. Biases and false assumptions should be set
>     aside. ;)
>     Best,
>     -jay
>     _______________________________________________
>     OpenStack-dev mailing list
>     OpenStack-dev at lists.openstack.org
>     <mailto:OpenStack-dev at lists.openstack.org>
>     http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> -- 
> Robert Collins <rbtcollins at hp.com <mailto:rbtcollins at hp.com>>
> Distinguished Technologist
> HP Cloud Services
> _______________________________________________
> OpenStack-dev mailing list
> OpenStack-dev at lists.openstack.org
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130710/e7ab43f7/attachment.html>

More information about the OpenStack-dev mailing list