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=arch...@jab.org
