The minimum requirement for active attendance: 3 tasks done
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); T1: v:=v+1; T1: write-item(X,v); T2: read-item(X,w);
T2: write-item(Y,w); T2: commit; T3: read-item(Y,p); T1: abort;
T3: write-item(Z,p); T3: commit.
Are the transactions consistency preserving? What is the isolation level
of each transaction? What log records are generated, when initially
X = Y = Z = 0 in the database. Will the integrity of the database be
preserved?
Isolation levels of transactions: Transaction T is of isolation level
`read uncommitted', if it does no dirty writes. T is of isolation
level `read committed', if it does no dirty writes nor dirty reads.
T is of isolation level `repeatable read', if it does no dirty
writes nor dirty reads nor unrepeatable reads.
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); T2: write-item(Y); T1: read-item(Y);
T2: read-item(X); T1: commit; T2: commit.
b) T1: read-item(X); T2: read-item(X); T1: write-item(X);
T2: write-item(X); T1: 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.