The minimum requirement for active attendance: 3 tasks done
This is the last set of exercises. The exam will be held according to the schedule indicated: Friday, November 2, 15:00 to 19:00 in Porthania I. Contents of the exam: topics from lectures and exercise sessions. I will check the corresponding parts in E&N and indicate them on the English course page.
1. An integrity constraint states that data items X and Y must always have the same value. Consider the following schedule of the transaction set {T1, T2, T3}:
T1: read-item(X,v); a) Are the transactions consistency preserving? T1: v:=v+1; T1: write-item(X,v); b) What is the isolation level of each transaction? T2: read-item(X,w); T2: write-item(Y,w); c) Which log records are generated, T2: commit; when initially X = Y = Z = 0 in the database. T3: read-item(Y,p); T1: abort; d) Will the integrity of the database be T3: write-item(Z,p); preserved? T3: commit.e) How strict two-phase locking prevents the creation of the schedule of the previous problem?
2. a) Under what conditions can a read lock be upgraded to a write lock?
b) How should lock downgrading from a write lock to a read lock be implemented? Sketch the changes in the algorithms of Fig. 20.2 (EN, p. 665). Is downgrading needed in practice?
3. Suppose that transactions T1 and T2 have the following schedule:
1 T1: read_item(X);
2 T2: read_item(X);
3 T1: write_item(X);
4 T1: read_item(Y);
5 T2: read_item(X);
6 T2: write_item(X);
7 T1: write-item(Y);
8 T1: commit;
9 T2: commit;
a) Which pairs of operations in the schedule are conflicting operations?
Name the isolation violation corresponding to each conflicting pair.
Violations handled on the course are dirty write, dirty read, and
unrepeatable read (cf. EN, p. 655; conflicting operations are explained
on p. 647). EN does not mention dirty write; it means a situation where
a transaction T2 writes the same data item as another transaction T1 has
already written although transaction T1 has not yet committed (and not
aborted either).
b) Give three examples of non-conflicting pairs of operations in the above schedule, and explain why they are not conflicting (three different types of examples, a different condition for each).
4. Consider the use of a) short-term, b) long-term locks in the following schedule. Are there any problems in a) or b)?
T1: read-item(X); T2: read-item(X); T1: write-item(X); T2: write-item(X); T1: commit; T2: commit;A lock is a short-term lock if it is released after its use, i.e. without waiting for the commit time of the transaction (as in Fig. 20.3, EN, p. 666). The long-term locks are released just when the transaction commits.
5. Suppose the query
select * from employee where dno=5;a) How the granularity of a lock should be decided? (EN, 20.5)
b) In some systems the granularity of a lock may be dynamic, i.e. locking may be changed even during the query execution e.g. from page locking to row locking or vice versa). Which situation might be the reason to this change? (Hint: The system might collect some simple statistical data during the query execution.)
6. (**) The DBMS employs strict two-phase locking. The data items to be locked are pages. What locks are acquired during the execution of the following transaction, when
a) there exists no index to the relation EMPLOYEE on attribute dno,
b) there exists a hash-table index to EMPLOYEE on dno (either as a primary access path or as a secondary index)?
begin transaction; update EMPLOYEE set salary = salary + 500 where dno = 5; commit transaction.c) What locks are needed in another update which increases the salary of all employees with name 'Smith'? Suppose that there is an ISAM index based on last name?
7. Fill in the form of teaching evaluation:
ilmo.cs.helsinki.fi/kurssit/servlet/Valinta
For technical reasons, the English version of the form may be
unavailable for the moment (but it should appear).
The Finnish lecture notes are available via the Finnish course page ('Luentokalvot') in pdf-format or in a course folder in room A412 (on paper).