intention locks - what is a main purpose?

MySQL & MariaDB Other MySQL® Questions
1

Hi there,

I have a (may be) stupid question about intention locks. For example I have hierarchy DB - TABLE - ROW and two transaction T1 and T2.

T1: if I modify ROW I place IX on DB and TABLE and X lock on ROW. T2: if I want to delete table I place X on DB => it must wait for T1 because T1 has IX on DB. Can I simulate similar behavior only using S/X locks? Consider same example:

T1: I place S lock on DB and TABLE and X lock on ROW. T2: I place X lock on DB => it must wait for T1 because T1 has S on DB. => Does it need a intention lock?

Thanks for explanation.

J.

2

Intention locks implement MGL, [URL=“http://Multiple granularity locking - Wikipedia”]http://en.wikipedia.org/wiki/Multiple_granularity_locking[/URL]

3

I known this MGL wiki (but I don’t see need for simple hierarchical locking), but question was another. Using only S/X locks (for example: S(DB) then S(TABLE) then X(ROW)) would work as hierarchical locking. Or Not? Or Intention lock are only lock escalation? thanks

J.