'What are MySQL InnoDB intention locks used for?
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?
Solution 1:[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.
Solution 2:[2]
From this link
the table-level intention locks are still not released so other transactions cannot lock the whole table in S or X mode.
I think the existance of intention lock is to allow table locking more effective(Mysql don't have to travse the entrie tree to see if there is a conflicted lock).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Nick Allen |