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.

Explanation / Answer

Part A)

In this case we used Wait-Die approach.

During 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.

While Grouping S2:

The grouping and result both are same within the case of 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)

Part B)

I this case we starts from the following:-

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.