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: Wednesday, November 1, 14:00 to 18: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); Are the transactions consistency preserving? T1: v:=v+1; T1: write-item(X,v); What is the isolation level of each transaction? T2: read-item(X,w); T2: write-item(Y,w); What log records are generated, T2: commit; when initially X = Y = Z = 0 in the database. T3: read-item(Y,p); T1: abort; Will the integrity of the database be T3: write-item(Z,p); preserved? T3: commit.2. Show how strict two-phase locking prevents the creation of the schedule of the previous problem.
3. Under what conditions can a read lock be upgraded to a write lock?
4. 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 lock are needed in another update which increases the salary of all employees with name 'Smith', and there is an ISAM index based on last name?
5. Are the following schedules possible in strict two-phase locking?
a) T1: write-item(X); b) T1: read-item(X); T2: write-item(Y); T2: read-item(X); T1: read-item(Y); T1: write-item(X); T2: read-item(X); T2: write-item(X); T1: commit; T1: commit; T2: commit. T2: commit;If a schedule is not possible, in what way is its creation prevented? What kind of schedules make it possible for the transactions to complete their execution, that is, to reach their commit points?
6. The DBMS employs a locking protocol that guarantees at least the isolation level 'read uncommitted' for each transaction. The data items to be locked are pages. Consider the following transaction T1:
set transaction isolation level L; begin transaction; select sum(salary) from employee; commit transaction:What locks are acquired and when are they released, when
a) L = 'repeatable read', b) L = 'read committed'?
In what way may the result produced by the transaction in case (a) differ from that produced in case (b)? We assume that there is only one other transaction, T2, executing concurrently. T2 updates the salary of two employees.
7. Fill in the form of teaching evaluation: www.cs.helsinki.fi/kurssit/kyselyt/syksy_2000
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).