MySQL Forums
Forum List  »  InnoDB

Re: A question for the developers about isolation level Repeatable-Read
Posted by: David Smith
Date: September 25, 2012 02:11AM

Hi Rick,

Hal Berenson coined the concept 'Snapshot Isolation' in 1995 in the aforementioned paper, where he used the 'First Committer Wins' rule to avoid lost updates. Innodb's Repeatable Read use X-locks to achieve the same goal: no concurrent transactions can update the same data item. So there are rumors Innodb's Repeatable Read is just 'Snapshot Isolation' implemented differently. I am pretty sure it is and just want the developers to confirm that for me.

'Serializable Snapshot Isolation' is an advancement to the 'Snapshot Isolation' algorithm which only allows serializable execution of transactions by aborting ones that might cause trouble. The theoretical and implementation details are in Michael Cahill's aforementioned paper. The possible favors of 'Serializable Snapshot Isolation' over Innodb's Repeatable Read and Serializable are as follows:

Innodb's Repeatable Read allows non-serializable execution history like 'Write Skew Anomaly'(A form of 'Write Skew Anomaly' can be found on the 2nd page of Michael Cahill's paper). For applications where performance and data consistency are both important, people need to use techniques like 'Static Dependency Graph', 'Materialization', 'Promotion' to make sure their program's execution is serializable(all these techniques are described in Alan Fekete's paper 'Making Snapshot Isolation Serializable'). PostgreSQL has implemented their Serializable Isolation Level using 'Serializable Snapshot Isolation' since 9.1 and hence freed application developers from the 'Static Dependency Graph', 'Materialization', 'Promotion' shackles.

'Serializable Snapshot Isolation' doesn't use locks, hence might have performance advantages over lock-based implementation of Serializable Isolation Level in high lock-contention scenarios. This has been shown to be true on BerkeleyDB in Michael Cahill's paper although BerkeleyDB uses page-level locks which might amplify lock contention comparing to peers that use row-level locks like Innodb.

Cheers,
Alex



Edited 1 time(s). Last edit at 09/25/2012 09:29PM by David Smith.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: A question for the developers about isolation level Repeatable-Read
1318
September 25, 2012 02:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.