Lectures

a. Yup, was there.

b. Nothing really missing.

c. The extent to which transaction deadlocks were handled i found were a bit too much. I assume this is common knowledge to most of your students.

Exercise 1 of Chapter 2.3

$r_1(A)$; $r_2(B)$; $r_3(C)$; $w_1(B)$; $w_2(C)$; $w_3(A)$;

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(A)
$w_3(A)$
unlock(A)
unlock(C)

Deadlock.

E.G. T1 will wait for lock-s on B, which can only be released by T2 which waits on T3 which waits on T1

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(A)
$w_3(A)$
unlock(A)
unlock(C)

Still Deadlocks.

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(A)
$w_3(A)$
unlock(A)
unlock(C)

Still Deadlocks......

$r_1(A)$; $r_2(B)$; $r_3(C)$; $r_1(B)$; $r_2(C)$; $r_3(D)$; $w_1(C)$; $w_2(D)$; $w_3(E)$;

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
lock-X(C)
$w_1(C)$
unlock(C)
unlock(B)
unlock(A)
lock-X(D)
$w_2(D)$
unlock(D)
unlock(C)
unlock(B)
lock-X(E)
$w_3(E)$
unlock(E)
unlock(D)
unlock(C)

Deadlock after last read of T3, T1 wont be able to acquire the lock on resource C.

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
lock-X(C)
$w_1(C)$
unlock(C)
unlock(B)
unlock(A)
lock-X(D)
$w_2(D)$
unlock(D)
unlock(C)
unlock(B)
lock-X(E)
$w_3(E)$
unlock(E)
unlock(D)
unlock(C)

Still deadlock after last read of T3, T1 wont be able to acquire the lock on resource C.

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
lock-X(C)
$w_1(C)$
unlock(C)
unlock(B)
unlock(A)
lock-X(D)
$w_2(D)$
unlock(D)
unlock(C)
unlock(B)
lock-X(E)
$w_3(E)$
unlock(E)
unlock(D)
unlock(C)

Samesies.

$r_1(A)$; $r_2(B)$; $r_3(C)$; $r_1(B)$; $r_2(C)$; $r_3(A)$; $w_1(A)$; $w_2(B)$; $w_3(C)$;

$T_1$ $T_2$ $T_3$
lock-X(A)
$r_1(A)$
lock-X(B)
$r_2(B)$
lock-X(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(A)
$r_3(A)$
$w_1(A)$
unlock(A)
unlock(B)
$w_2(B)$
unlock(B)
unlock(C)
$w_3(C)$
unlock(C)
unlock(A)
Deadlock after first read of T3, T1 wont be able to acquire the lock-s on resource B.
$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(A)
$r_3(A)$
lock-X(A)
$w_1(A)$
unlock(A)
unlock(B)
lock-X(B)
$w_2(B)$
unlock(B)
unlock(C)
lock-X(C)
$w_3(C)$
unlock(C)
unlock(A)

Deadlock after second read of T3, T1 wont be able to acquire the lock-X on resource A.

$T_1$ $T_2$ $T_3$
lock-U(A)
$r_1(A)$
lock-U(B)
$r_2(B)$
lock-U(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(A)
$r_3(A)$
lock-X(A)
$w_1(A)$
unlock(A)
unlock(B)
lock-X(B)
$w_2(B)$
unlock(B)
unlock(C)
lock-X(C)
$w_3(C)$
unlock(C)
unlock(A)

Still deadlocks because the read locks wont be granted.

$r_1(A)$; $r_2(B)$; $r_3(C)$; $w_1(B)$; $w_2(C)$; $w_3(D)$;

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(D)
$w_3(D)$
unlock(D)
unlock(C)

No Deadlock! T3 will finish resulting in a finish of T2 resulting in a finish of T1

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(D)
$w_3(D)$
unlock(D)
unlock(C)

No Deadlock! Stayed the same.

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-X(B)
$w_1(B)$
unlock(B)
unlock(A)
lock-X(C)
$w_2(C)$
unlock(C)
unlock(B)
lock-X(D)
$w_3(D)$
unlock(D)
unlock(C)

No Deadlock! Stayed the same.

$r_1(A)$; $r_2(B)$; $r_3(C)$; $r_1(B)$; $r_2(C)$; $r_3(D)$; $w_1(A)$; $w_2(B)$; $w_3(C)$;

$T_1$ $T_2$ $T_3$
lock-X(A)
$r_1(A)$
lock-X(B)
$r_2(B)$
lock-X(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
$w_1(A)$
unlock(B)
unlock(A)
$w_2(B)$
unlock(C)
unlock(B)
$w_3(C)$
unlock(D)
unlock(C)

No Deadlock! T3 will be able to finish which makes T2 able to finish and then T1.

$T_1$ $T_2$ $T_3$
lock-S(A)
$r_1(A)$
lock-S(B)
$r_2(B)$
lock-S(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
lock-X(A)
$w_1(A)$
unlock(B)
unlock(A)
lock-X(B)
$w_2(B)$
unlock(C)
unlock(B)
lock-X(C)
$w_3(C)$
unlock(D)
unlock(C)

No Deadlock! All transactions will be able to run without waits following the schedule.

$T_1$ $T_2$ $T_3$
lock-U(A)
$r_1(A)$
lock-U(B)
$r_2(B)$
lock-U(C)
$r_3(C)$
lock-S(B)
$r_1(B)$
lock-S(C)
$r_2(C)$
lock-S(D)
$r_3(D)$
lock-X(A)
$w_1(A)$
unlock(B)
unlock(A)
lock-X(B)
$w_2(B)$
unlock(C)
unlock(B)
lock-X(C)
$w_3(C)$
unlock(D)
unlock(C)

No Deadlock! Schedule will follow the same wait schedule as the first of this schedule.

Exercise 2 of Chapter 2.3

insert into instrument (name, gid, comment) values ('mayonaise', uuid_generate_v4(), 'mayonaise is geen instrument');
delete from instrument where name='mayonaise');
select artist.name, track.name from track left join artist on track.artist_credit=artist.id where artist.id=1;