Hi, can anyone give a simple example of a db transaction using an Intentional Shared lock, and (if can) one using an intentional exclusive lock.
I'm studying them and trying to understand.
Thanks
+1
A:
Intent locks are needed because the lock manager does not understand the physical structure of the entities locked. If one transaction S-locks a record, say R1, and another transaction asks for an X-lock on a page, say P1, what happens if R1 is actually located on P1? the lock manager should not honor the P1 request until R1 is released, but to do so it would have to understand that R1 is contained in P1.
Since the lock manager clearly cannot know the structural details of the objects locked, the intent lock were introduced. The first transaction will place an IS-lock on P1 then an S-lock on R1. When the second transaction requests for X-lock on P1, it will conflict with the IS-lock placed by the first transaction.
Remus Rusanu
2009-11-25 04:52:31
Thank you Remus!
Sheldon
2009-11-25 05:13:45
So Intent locks are not locks as it, but ways to define wether certain data can be locked or not, and if can, how to lock it? as a top-down tree, with granularity gown down on the three as greater.
Sheldon
2009-11-25 05:15:41
From the lock manager point of view the locks are of 4 types: IS, IX, S, X (ignoring U locks and other types). LM understand them all as locks and has a compatibility matrix that makes IS-IX and IX-IX compatible (but not S-IX, X-IS and X-IX). The 'accessor' (which understand the structure of the hierarchy tree) will navigate the tree, asking for I locks as it goes down the hierarhy, until it finds the actual item of interests on which it requests an S or X lock.
Remus Rusanu
2009-11-25 05:22:47
You can best think at I locks as breadcrumbs. You leave them behind so that someone else does not cut the branch you're standing on. If Gretel ever climbs into a tree, that is... sort of.
Remus Rusanu
2009-11-25 05:27:40
Thank you very much, now I have it clear!
Sheldon
2009-11-25 05:52:24