Q1Suppose you have this query SELECT Pname Price Color FROM

Q1.Suppose you have this query

SELECT Pname, Price, Color

FROM PRODUCT

WHERE Price < 50 OR Color = ‘Red’;

Which technique can be used to improve this query ?

Q2.With a neat state transition diagram explain the states for transaction execution.

Q3. Consider the four transactions T1, T2, T3, T4 and the schedules S given below.

T1: w1(A); w1(B)

T2: r2(A); r2(C); w2(D)

T3: r3(B); w3(E)

T4: r4(D), w4(E)

S: w1(A), r2(A), w1(B), r2(C), r3(B), w2(D), w3(E), r4(D), w4(E)

A. Draw the serializibility (precedence) graph for S

B. If S is serializible, write down the equivalent serial schedule(s).

Q4.What are Essential components in Two-Phase Locking Techniques? draw conflict matrix

Solution

Two-Phase Locking Techniques
* Locking is an operation which secures
* (a) permission to Read
* (b) permission to Write a data item for a transaction.
* Example:
* Lock (X). Data item X is locked in behalf of the requesting
transaction.
* Unlocking is an operation which removes these permissions
from the data item.
* Example:
* Unlock (X): Data item X is made available to all other
transactions.
* Lock and Unlock are Atomic operations.
Two-Phase Locking Techniques: Essential components
* Two locks modes:
* (a) shared (read) (b) exclusive (write).
* Shared mode: shared lock (X)
* More than one transaction can apply share lock on X for
reading its value but no write lock can be applied on X by any
other transaction.
* Exclusive mode: Write lock (X)
* Only one write lock on X can exist at any time and no shared
lock can be applied by any other transaction on X

Two-Phase Locking Techniques: Essential components
The following code performs the lock operation:
B: if LOCK (X) = 0 (*item is unlocked*)
then LOCK (X) 1 (*lock the item*)
else begin
wait (until lock (X) = 0) and
the lock manager wakes up the transaction);
goto B
end;