Operations on the Physical Database - Transaction Processing - page 41

As is evident from the above examples, we can use the simple read-write
transaction model with pages in place of the abstract uninterpreted data items to
model transaction processing at the physical level: for page p, the action RŒp fixes
and read-latches page p and reads its contents, and the action WŒpfixes and write-
latches page p and reads and/or writes its contents. In the parlance of multi-level
transactions , the “transactions” at the physical level can be viewed as a kind of
open nested transactions , meaning subtransactions that commit independently of
their parent transactions.
Problems
2.1 Assume that the size of the database buffer is 1,000 buffer frames for database
pages of size 4 kilobytes. The tuples of relation r occupy 10,000 data pages and the
tuples of relation s 500 data pages. In the beginning, there are no pages in the buffer,
and no transactions are active. The first transaction to be run is T 1 :
exec sql update r set A D A C 100.
exec sql commit .
How many data pages of r are fetched from disk into the buffer, and how many data
pages are flushed from the buffer onto disk while T 1 is running? How does the disk
version of the database differ from the current version of the database at the time T 1
commits?
Immediately after the commit of T 1 , another transaction, T 2 , is run:
exec sql update s set B D B C 200;
exec sql commit .
How many data pages of r and s are fetched from disk, and how many data
pages are flushed onto disk while T 2 is running? How does the disk version of the
database differ from the current version of the database at the time T 2 commits? No
checkpoints are taken.
Assuming a disk with average seek and rotation time 10 ms and sequential
transfer rate of 10 megabytes per second, how long does it take to perform T 1
followed by T 2 ? You may assume that the pages of both relations are stored
sequentially on the disk.
2.2 Explain why a page-action-consistent database state is not necessarily action
consistent and why an action-consistent state is not necessarily transaction consis-
tent. Also explain why the disk version of a physical database is not necessarily
action consistent even though no transactions are in progress.
2.3 Recall from your operating systems course how LRU buffering is implemented.
2.4 Assume that a server-process thread fails while keeping a page write-latched.
Obviously, we have to regard the contents of the page in the buffer as corrupted ,that
is, non-page-action consistent. A corrupted page should not be flushed onto disk.