Transcript Concurrency Control

Concurrency Control
Managing Hierarchies of Database Elements (18.6)
Presented by
Priyank
(204)
1
Agenda
•
•
•
•
•
•
Managing Hierarchies of Database Elements
Locks with Multiple Granularity
Warning (Intention) Locks
Database Elements Organized in Hierarchy
Rules of Warning Protocol
Group Modes of Intention Locks
2
Managing Hierarchies of Database
Elements
• Two problems that arise with locks when there is a tree
structure to the data are:
• When the tree structure is a hierarchy of lockable elements
• Determine how locks are granted for both large
elements (relations) and smaller elements (blocks
containing tuples or individual tuples)
• When the data itself is organized as a tree (B-tree indexes)
• This will be discussed in the next section
Locks with Multiple
Granularity
• A database element can be a relation, block or a tuple
• Different systems use different database elements to
determine the size of the lock
• Thus some may require small database elements such as
tuples or blocks and others may require large elements such
as relations
Example of Multiple Granularity
Locks
• Consider a database for a bank
• Choosing relations as database elements means we
would have one lock for an entire relation
• If we were dealing with a relation having account
balances, this kind of lock would be very inflexible and
thus provide very little concurrency
• Why? Because balance transactions require exclusive
locks and this would mean only one transaction occurs
for one account at any time
• But as each account is independent of others we could
perform transactions on different accounts
simultaneously
…(contd.)
• Thus it makes sense to have block element for the lock
so that two accounts on different blocks can be updated
simultaneously
• Another example is that of a document
• With similar arguments as above, we see that it is
better to have large element (a complete document) as
the lock in this case
• SHARED (S) Used for read operations that do not change or update
data, such as a SELECT statement.
• Exclusive - Used for data-modification operations, such as INSERT,
UPDATE, or DELETE. Ensures that multiple updates cannot be made
to the same resource at the same time
• INTENT - Used to establish a lock hierarchy. The types of intent
locks are: intent shared (IS), intent exclusive (IX)
7
• The Database Engine uses intent locks to protect placing a shared
(S) lock or exclusive (X) lock on a resource lower in the lock
hierarchy. Intent locks are named intent locks because they are
acquired before a lock at the lower level, and therefore signal intent
to place locks at a lower level.
• Intent locks serve two purposes:
• To prevent other transactions from modifying the higher-level
resource in a way that would invalidate the lock at the lower level.
• To improve the efficiency of the Database Engine in detecting lock
conflicts at the higher level of granularity.
8
Warning (Intention) Locks
• These are required to manage locks at different granularities
• In the bank example, if the a shared lock is obtained
for the relation while there are exclusive locks on
individual tuples, unserializable behavior occurs
• The rules for managing locks on hierarchy of database
elements constitute the warning protocol
Database Elements Organized in
Hierarchy
Rules of Warning Protocol
• These involve both ordinary (S and X) and warning (IS and IX)
locks
• The rules are:
• Begin at the root of hierarchy
• Request the S/X lock if we are at the desired element
• If the desired element id further down the hierarchy,
place a warning lock (IS if S and IX if X)
• When the warning lock is granted, we proceed to the
child node and repeat the above steps until desired
node is reached
Compatibility Matrix for Shared,
Exclusive and Intention Locks
IS
IX
S
X
IS
Yes
Yes
Yes
No
IX
Yes
Yes
No
No
S
Yes
No
Yes
No
X
No
No
No
No
• The above matrix applies only to locks held by
other transactions
Group Modes of Intention Locks
• An element can request S and IX locks at the same time if they
are in the same transaction (to read entire element and then
modify sub elements)
• This can be considered as another lock mode, SIX, having
restrictions of both the locks i.e. No for all except IS
• SIX serves as the group mode
Example
• Consider a transaction T1 as follows
• Select * from table where attribute1 = ‘abc’
• Here, IS lock is first acquired on the entire relation;
then moving to individual tuples (attribute = ‘abc’), S
lock in acquired on each of them
• Consider another transaction T2
• Update table set attribute2 = ‘def’ where attribute1 =
‘ghi’
• Here, it requires an IX lock on relation and since T1’s IS
lock is compatible, IX is granted
• On reaching the desired tuple (ghi), as there is no lock, it gets X
too
• If T2 was updating the same tuple as T1, it would have to wait
until T1 released its S lock
Thank You!!!