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

Robert Collins robertc at robertcollins.net
Wed Jul 10 01:51:48 UTC 2013

On 10 July 2013 11:00, Jay Pipes <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.

> 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 <OpenStack-dev at lists.openstack.org>
> http://lists.openstack.org/**cgi-bin/mailman/listinfo/**openstack-dev<http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev>

Robert Collins <rbtcollins at hp.com>
Distinguished Technologist
HP Cloud Services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20130710/e04f61ff/attachment.html>

More information about the OpenStack-dev mailing list