High-level transaction isolation architecture of InnoDB

I've noticed that Innodb seems to exhibit true serializability for the
serializable transaction isolation level. Does this mean it implements
predicate locking? Also out of curiosity, is it possible to set a
snapshot isolation transaction isolation level (is Innodb implemented
using MVCC)? Thanks in advance.
--
Yang Zhang
http://www.mit.edu/~y_z/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: High-level transaction isolation architecture of InnoDB

Hi Yang,

On Mar 26, 2010, at 4:28 PM, Yang Zhang wrote:

> I've noticed that Innodb seems to exhibit true serializability for the
> serializable transaction isolation level. Does this mean it implements
> predicate locking?

Kinda, but not exactly. In serializable, all reads will use shared
locks on the tree as it accesses the rows.

It doesn't have true predicate locking, since it doesn't lock non-
existent rows, but instead locks gaps where rows could go. For
example, if you do try to read the non-existent row 1000, it may also
prevent 999 from being inserted while with true predicate locking it
would be allowed.

In addition, it locks based on access path, so there can be additional
locks from that as well.

This does make it truly mathematically serializable, but does have
additional locks than would be required by 'real' predicate locking.

> Also out of curiosity, is it possible to set a
> snapshot isolation transaction isolation level (is Innodb implemented
> using MVCC)? Thanks in advance.

Yes, it is MVCC. InnoDB in repeatable read will use a 'snapshot' that
is taken at the beginning of the transaction for all of the normal non-
locking reads it performs during the transaction.

Regards,

Harrison
--
Harrison C. Fisk, Senior Principal Technical Support Engineer
MySQL @ Oracle, Inc., http://www.mysql.com/







--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org