Information Technology Reference
In-Depth Information
The addresses of the buffer control blocks are stored in a hash table indexed by the
page identifiers. In this way it can be efficiently resolved whether or not a given
page resides in the buffer and which buffer frame has been assigned to the page.
The implementation of an
SQL
query includes a series of pairs of calls to
fix
and
unfix
for the pages touched by the query. These fixings permit the query processor
to operate on the pages touched by the query.
Example 2.1
The execution of an
SQL
query
select sum
.V /
from
r
involves scanning the data pages of relation r in their physical order. For every data
page p, the following steps (among others) are performed:
1.
fix
.p/.
2. Retrieve from the buffer frame of page p the value of the V attribute of every
r -tuple and add it to the sum being counted.
3.
unfix
.p/.
In fact, in order to determine the processing strategy for the query and to locate
the data pages, some data-dictionary pages must first be fixed and inspected. Besides
being fixed, each data page p (as well as each data-dictionary page inspected) must
also be
read-latched
for the duration of the inspection of the page contents, so
that no other process thread can simultaneously update the page. Moreover, if the
transaction is wanted to be run in full isolation from other transactions, the relation
r or all of its tuples must be
read-locked
for the transaction for commit duration.
Latching is explained later in this chapter, and lock-based concurrency control is
discussed in Chap.
6
.
t
Example 2.2
The execution of an
SQL
update statement
update
r
set
V
D
V
C
1
where
X
D
x
contains, among others, the following steps:
1.
fix
.q/ and
unfix
.q/ calls on pages q in an index of relation r based on attribute
X , in order to locate the data page p containing the r -tuple with X
D
x.
2.
fix
.p/.
3. Locate the r -tuple with X
D
x in page p and increment the value of its attribute
V by 1.
4. Log the update for the transaction and stamp the
LSN
of the log record in the
P
AGE
-LSN
field of page p.
5.
unfix
.p/.
Again, some data-dictionary pages must first be fixed (and read-latched) in order
to find out that the index on r exists. Besides fixing the page p to be updated,
the page must also be
write-latched
for the process thread for the duration of the
update, so that no other process thread can simultaneously read or update the page.
Moreover, in order to guarantee sufficient transactional isolation, the r -tuple to be
updated must be
write-locked
for the transaction for commit duration.
t
