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