On 07/09/2013 09:51 PM, Robert Collins wrote:
On 10 July 2013 11:00, Jay Pipes <jaypi...@gmail.com
<mailto:jaypi...@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@lists.openstack.org
<mailto:OpenStack-dev@lists.openstack.org>
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
--
Robert Collins <rbtcoll...@hp.com <mailto:rbtcoll...@hp.com>>
Distinguished Technologist
HP Cloud Services
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
_______________________________________________
OpenStack-dev mailing list
OpenStack-dev@lists.openstack.org
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
