There is no fundamental difference in the semantics of InnoDB's MVCC and PostgreSQL's MVCC, but there are minor differences. In PostgreSQL-8.1, the default transaction isolation level is READ COMMITTED (like in Oracle), while in InnoDB it is REPEATABLE READ (one can choose READ COMMITTED if one likes). I chose REPEATABLE READ as InnoDB's default isolation level, because then all consistent read SELECTs within one transaction see the same snapshot of the database, and give consistent results with respect to each other.
The implementation of MVCC is quite different in PostgreSQL and InnoDB. InnoDB keeps track of delete-marked records in indexes (the data of the row is in the clustered index), and removes them as soon as they are not needed in consistent read SELECTs. PostgreSQL does not keep track of deleted records and rows, and therefore you from time to time need to run the 'vacuum' over the entire database.
In the upcoming MySQL-5.0.30, we have improved InnoDB's scalability under multiple concurrent threads that insert, update, or query the database as fast as they can. It would be interesting to see the Tweakers' benchmark re-run with the new version.
Slashdot Mirror
There is no fundamental difference in the semantics of InnoDB's MVCC and PostgreSQL's MVCC, but there are minor differences. In PostgreSQL-8.1, the default transaction isolation level is READ COMMITTED (like in Oracle), while in InnoDB it is REPEATABLE READ (one can choose READ COMMITTED if one likes). I chose REPEATABLE READ as InnoDB's default isolation level, because then all consistent read SELECTs within one transaction see the same snapshot of the database, and give consistent results with respect to each other.
The implementation of MVCC is quite different in PostgreSQL and InnoDB. InnoDB keeps track of delete-marked records in indexes (the data of the row is in the clustered index), and removes them as soon as they are not needed in consistent read SELECTs. PostgreSQL does not keep track of deleted records and rows, and therefore you from time to time need to run the 'vacuum' over the entire database.
Hi!
In the upcoming MySQL-5.0.30, we have improved InnoDB's scalability under multiple concurrent threads that insert, update, or query the database as fast as they can. It would be interesting to see the Tweakers' benchmark re-run with the new version.
Best regards,
Heikki Tuuri
Innobase Oy / Oracle Corp.