What are MySQL InnoDB intention locks used for?

Question

I have read the MySQL mannual about intention lock： http://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html#innodb-intention-locks

It says that "To make locking at multiple granularity levels practical", but how? It does not tell us about it.

Can anyone give a detailed explanation and a sample?

Answer 1

Think of the InnoDB data space as a collection of databases, each database being a collection of tables, and each table being a collection of rows. This forms a hierarchy, where lower and lower levels offer more and more granularity.

Now, when you want to update some part(s) of this tree in a transaction, how do you do it? Well, InnoDB employs multiple granularity locking (MGL). The mechanism in MGL is that you specify "intentions" to lock at a particular granularity level as shared or exclusive, then MGL combines all these intentions together and marches up the hierarchy until it finds the minimum spanning set that has to be locked given those intentions.

Without intention locks, you have high level shared and exclusive locks which really don't give you much flexibility: they're all or nothing, which is what we see in MyISAM. But MGL brings the concept of intended shared and intended exclusive, which it uses as I said above to provide "just enough" locking.

If you'd like to know about the specific C level implementation, refer to Introduction to Transaction Locks in InnoDB.