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)
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:
Later we shall consider what can happen when these programs are run concurrently with each other, and with other instances of themselves.
// 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
{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 }
{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 } } }
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.
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: