A new application is being developed and will be using a database that includes

Question

A new application is being developed and will be using a database that includes a relation about items: Item (item_id:integer, item_name:string, color:string, price:real).

Both the purchasing department in charge of obtaining raw material and the manufacturing department in charge of manufacturing the items can change the price of manufactured items according to changes that may happen in the raw material cost or production cost. The two departments use different transactions to update the price of items. The new application uses the following sequences of actions, listed in the order they are submitted to the DBMS:

Sequence S1: T1:R(X), T2:W(X), T2:W(Y), T3:W(Y), T1:W(Y), T1:Commit, T2:Commit, T3:Commit

Sequence S2: T1:R(X), T2:W(Y), T2:W(X), T3:W(Y), T1:W(Y), T1:Commit, T2:Commit, T3:Commit

For each of the following concurrency control mechanisms, describe how they will handle each of the sequences (S1 & S2).

a.Strict 2PL with timestamps used for deadlock prevention.

b. Conservative (and Strict, i.e., with locks held until end-of-transaction) 2PL.

Note: Assume that the DBMS processes actions in the order shown. If a transaction is blocked, assume that all its actions are queued until it is resumed; the DBMS continues with the next action (according to the listed sequence) of an unblocked transaction. Write any other assumption you are using for your answer.

Explanation / Answer

ANS:
a).1. Strict 2PL with timestamps used for deadlock prevention.

1. Expect we utilize Wait-Die approach.

Arrangement S1:

T1 procures shared-lock on X;

for a select lock on X, since T2 has a lower need, it will be prematurely ended When T2 inquires;

T3 now gets selective lock on Y;

At the point when T1 likewise requests an elite lock on Y, which is still held by T3, since T1 has higher need,

T1 will be blocked holding up;

T3 now completes compose, submits and discharges all the lock;

T1 awakens, gains the lock, continues and wraps up;

T2 now can be restarted effectively.

Grouping S2:

The grouping and result are the same with arrangement S1, aside from T2 could propel a

minimal more before it gets prematurely ended.

Wait_die:
T1 T2 T3
Read_lock (X)
Read_item (X)
TS (T2) > TS (T1): die
Abort (T2)
Write_lock (Y)
Write_item (Y)
TS (T1) < TS (T3): wait
Commit
Unlock (Y)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
Restart
Write_lock (X)
Write_item (X)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
Wound-wait

T1 T2 T3
Read_lock (X)
Read_item (X)
TS (T2) > TS (T1): wait
Write_lock (Y)
Write_item (Y)
TS (T1) < TS (T3): wound T3
Abort
Unlock (Y)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
Write_lock (X)
Write_item (X)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
Restart
Write_lock (Y)
Write_item (Y)
Commit
Unlock (Y)

Wait-die
T1
Read_lock (X)
Read_item (X)
Write_lock (Y)
Write_item (Y)
TS (T2) > TS (T1): die
Abort (T2)
Unlock (Y)
Write_lock (Y)
Write_item (Y)
TS (T1) < TS (T2): wait
Commit
Unlock (Y)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
Restart
T2
Write_lock (Y)
Write_item (Y)
Write_lock (X)
Write_item (X)
Commit
T3
Unlock (Y)
Unlock (X)
Wound-wait
T1   
Read_lock (X)
Read_item (X)
Write_lock (Y)
Write_item (Y)
TS (T2) > TS (T1): wait
TS (T3) > TS (T2): wait
TS (T1) < TS (T2): wound T2
Abort (T2)
Unlock (Y)
Write_lock (Y)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
T2
Write_lock (Y)
Write_item (Y)
Commit
Unlock (Y)
Restart
T3
Write_lock (Y)
Write_item (Y)
Write_lock (X)
Write_item (X)
Commit
Unlock (Y)
Unlock (X)

b)b. Conservative (and Strict, i.e., with locks held until end-of-transaction) 2PL.
ANS:

Succession S1: With moderate and strict 2PL, the grouping is simple.

T1 obtains lock on both X and Y, confers, discharges locks; then T2; then T3.

Grouping S2: Same as Sequence S1.

T1
Read_lock (X)
Write_lock (Y)
Read_item (X)
Write_item (Y)
Commit
Unlock (X)
Unlock (Y)
T2
Write_lock (Y)
Write_lock (X)
Write_item (Y)
Write_item (X)
Commit
Unlock (Y)
X
Y
Y
Unlock (X)
T3
Write_lock (Y)
Write_item (Y)
Commit
Unlock (Y)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.