Information Technology Reference
In-Depth Information
even the database level does not need to be part of the granule hierarchy of a normal
transaction. Usually, an administrator's manual for the database management system
specifies what kinds of locks are acquired on data items at which level of the granule
hierarchy and for which actions.
Example 9.4
A past database management system used a multi-granular locking
protocol with a page as the finest granule to be locked. According to that protocol,
locks and intention locks were acquired using the following rules:
1. For an
SQL
select
query that touches relations r
1
;:::;r
n
, an IS lock is acquired
on each r
i
and an S lock on data pages to be read from r
1
;:::;r
n
.However,ifthe
query touches over 10 (or a specified maximum number of) pages of relation r
i
,
the IS lock on r
i
is upgraded to an S lock and the S locks on the pages are
released.
2. For the
SQL
operations
update
,
insert
,or
delete
, an IX lock is acquired on the
relation r to be updated and an X lock on the data pages to be updated of r .Ifthe
update touches more than 10 (or a specified maximum number of) pages of r ,the
IX lock on r is upgraded to an X lock and the X locks on the pages are released.
Possible other pages or relations that the operation touches (but does not update)
are IS- or S-locked as in a
select
query.
3. For an operation that changes the physical structure of relation r (e.g., from a
hash table into a B-tree), an X lock is acquired on r .
4. For the operation
create table
r.:::/, an X lock is acquired on the relation r to
be created.
5. For the operation
drop table
r , an X lock is acquired on the relation r to be
dropped.
The lock upgrades in (1) and (2) are called
lock escalation
.
t
9.3
Logical Locking vs. Physical Locking
Logical locking is natural, because the purpose of locking is to protect the read
and update actions done by transactions on the data items of the logical database.
Logical locks are completely independent of the physical location of the data item
to be locked. Thus, a logically locked data item can be moved without having to
modify the lock at all: when a tuple .x;
v
/ on a B-tree leaf page is logically locked
by a transaction T , and while T is still active, the tuple is moved to another page in
a structure modification such as a page split (either as a result of T 's own or some
other transaction's actions), the logical lock held by T on x remains unchanged.
Another advantage of logical locks is that they can often be acquired before the
target data item is actually located or stored in the physical database. The commit-
duration X lock needed for a write action WŒx;
u
;
v
can be acquired at the start of
the execution of the action, before the location of the tuple .x;
u
/ is known. For the
insert action IŒx;
v
, the commit-duration X lock can also be acquired at the start of
