List:General Discussion« Previous MessageNext Message »
From:Harrison Fisk Date:March 26 2010 8:52pm
Subject:Re: High-level transaction isolation architecture of InnoDB
View as plain text  
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.


Harrison C. Fisk, Senior Principal Technical Support Engineer
MySQL @ Oracle, Inc.,

High-level transaction isolation architecture of InnoDBYang Zhang26 Mar
  • Re: High-level transaction isolation architecture of InnoDBHarrison Fisk26 Mar