Tutorial 5

HT2016 Exam

The following transaction calculates the total number of entries in UserStatus as the sum of the number of logged-in and not logged-in users.

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT
(SELECT COUNT(*) FROM UserStatus WHERE loggedin = True)
+
(SELECT COUNT(*) FROM UserStatus WHERE loggedin = False ) ;
COMMIT;
The used transaction isolation level is not sufficient to ensure an accurate count of entries in UserStatus. Why not? Give all isolation levels that are sufficient so that the query works as expected. (4p)

Question

We will keep working with the similar domain, where we have the following tables:

You can also assume these two helper tables, to specify what days and times are valid for classes:

ClassDays contains Monday-Saturday and "Someday", the latter is for courses that have not been assigned a day yet.

Later we shall consider what can happen when these programs are run concurrently with each other, and with other instances of themselves.


Program 1 ("booking classes"):

// get a {COURSE} and a {DAY} from the user
{ROOM} = "SELECT room FROM Courses C, Teachers T WHERE teacher = T.name AND C.name ={COURSE}"  //  Q1.1 

{AVAILABLE_HOURS} = "SELECT * FROM ClassTimes WHERE time NOT IN (SELECT hour FROM Classes A, Courses C, Teachers T WHERE course = C.name AND teacher = T.name AND room = {ROOM} AND day = {DAY})";  //  Q1.2 

if ({AVAILABLE_HOURS} is empty){ 
	{AVAILABLE_HOURS} = "SELECT * FROM ClassTimes"); //  Q1.3  
	{DAY} = "someday" 
}        
// print avaiable time slots {HOURS}
// get an {HOUR} from the user (let the user pick one of the options)
"INSERT INTO Classes VALUES ({COURSE},{DAY},{HOUR})" //  I1.4 

Program 2 ("adapt room sizes")

{NAME}, {NRSTUDENT} = "SELECT R.name, nrStudents FROM Courses C, Teachers T, Rooms R WHERE teacher = T.name AND room = R.name AND nrStudents > nrSeats"; // Q2.1
iterate each room {
	"UPDATE Rooms SET nrSeats = {NRSTUDENT} WHERE name = {NAME}" // U2.2 
}

Program 3 ("Reschedule classes")

{DAYS} = "SELECT * FROM ClassDays WHERE day != 'Someday'" //Q3.1
while (true) {
	/* Sleep for a random time */
	{COURSE}, {ROOM}, {HOUR} = "SELECT C.name, room, hour FROM Classes A, Courses C, Teachers T WHERE day='Someday' AND course=C.name AND teacher=T.name"; //  Q3.2  
	iterate for each result {
	// random i 
	{DAY} = {DAYS}[i];
	Q3.3 ="SELECT * FROM Classes A, Courses C, Teachers T WHERE course = C.name AND teacher = T.name AND day = {DAY} AND hour = {HOUR} AND room={ROOM}" //  Q3.3 

	if ( Q3.3 is not empty ) {
	    	"DELETE FROM Classes WHERE day = 'Someday' AND hour = {HOUR} AND course = {COURSE}" // D3.4
	    	"INSERT INTO Classes VALUES ({COURSE},{DAY},{HOUR})" // I3.5
		}
	}
}

Transactions

For each of the programs above, consider what could happen if it is run

Consider what happens when they are run in each of the four levels of isolation, i.e.

For each program, suggest a proper level of isolation to get the desired behavior, or if that is impossible, suggest how to solve it instead.

Book exercises

The following exercises from "Database Systems: The Complete Book, 2/E" are relevant:

If you have the first edition of the textbook, the following exercises are similar:


Last Modified: Feb 2017 by Hamid Ebadi