Computer Engineering - Data Bases 2

Full exam

Databases 2 - exam - September 12, 2023 -Dur. 2h S. Comai, P. Fraternali, D. Martinenghi A. Concurrency Control (7 points) Classify the following scheduleSwith respect to VSR, CSR, 2PL, Strict 2PL, TS-mono, and TS-multi. Motivate all your answers. •If it belongs to VSR, provide allthe possible serializations. •If it does not belong to 2PL and/or 2PL-strict, explain which lock/unlock requests cause this. S=r 3( X)w 3( Y)w 2( Y)w 1( Z)r 3( Z)w 4( X)r 4( Y)w 1( X)r 5( Y)w 5( X) If additional space is required, please use the last page. Solution. Detailed analysis for the CSR class Not CSR: there are cycles in the resource graph.( 2)( 2)T 2T 5T 4T 3T 1Detailed analysis for the VSR class Not VSR: the schedule is not CSR and no permutation/relocation of its blind writes{w 4( X), w 1( X), w 3( Y) } makes it CSR. Detailed analysis for the 2PL and Strict 2PL classes Not 2PL or Strict 2PL asSis not CSR. In particular,w 1( X)cannot anticipate the lock beforew 4( X)and w1( Z)must release the lock beforer 3( Z). Similarly,r 3( Z)cannot anticipate the lock request andw 3( Y) must release the lock beforew 2( Y). The table below shows a pair of incompatible requests (circled in red). 1 2 3 4 5 6 7 8 9 10X↑ 3 r 3↓ 3↑ 4 w 4↓ 4↑ 1w 1↓ 1↑ 5 w 5↓ 5 Y↑ 3 w 3↓ 3↑ 2 w 2↓ 2↑ 4 r 4↓ 4↑ 5 r 5↓ 5 Z↑ 1 w 1↓ 1↑ 3 r 3↓ 3 245Legend ↑ : read lock request ↑ : write lock request ↓: unlock request For Strict 2PL, pairs of incompatible mandatory unlock requests and commit times are shown below. 1 2 3 4 5 6 7 8 9 10X r 3w 4w 1 ●∣1 w5Y w 3↓ 3 w 2r 4r 5Z w 1↓ 1 r 3 ●∣3Legend ↓: mandatory unlock request ●∣ : commit time Detailed analysis for the TS Mono class Not TS-mono, sincew 1( X)occurs afterw 4( X)andw 2( Y)afterw 3( Y).OperationRTM (X)WTM (X)RTM (Y)WTM (Y)RTM (Z)WTM (Z)Killed transactions r 3( X)3 (0)00 (0)00 (0)0 w 3( Y)3 w 2( Y)2 as 2