| P1a: | Q1.1 Q1.2 | I1.4 | |
| P1b: | Q1.1 Q1.2 I1.4 |
Assuming the same day and time are chosen in both programs, if the course is the same in both P1a abd P1b then P1a will get a primary key violation and crash. This is not a big problem, but if the course although the room is the same then both programs will succeed and the room will be double booked.
Putting the entire program in a transaction will solve the problem only if the isolation level is serializable. Unfortunately this is not a good idea because the user input (for choosing time) can take arbitrarily long time, and the database will essentially be deadlocked during that time. To solve it properly, the part after the input needs to start a new transaction and first check that the chosen time is still free and then insert the lecture.
There is a bug in this code that causes the postcondition to fail (i.e. courses may still be in too small rooms). The problem is that each room might be updated several times (once for each course in that room) and only the last change will be effective. To fix this we can either use GROUP BY Room and select MAX(nrStudents) or, slightly less elegant, we can SORT BY nrStudents. Both these solutions will work even if P2 is run in parallel with itself, however in the sorting solution an outside observer might see the capacity of some rooms go up and down several times.
If this program is run in parallel with a query that updates the capacity of a room, setting it to something higher than any course in the room, there may be a serialization problem. Particularly the program might reset the change by the other query and decrease the capacity of the course again. To prevent this we can put the entire program in a transaction and set the isolation level to either serializable or repeatable read (because inserting new rooms is not a problem).
Just like in Program 1, the basic problem is that the program makes a check (to see that the course is overfull), and then later it acts on the false assumption that this situation has not changed. By adding a transaction we make sure that the assumption is safe.
Without transactions, this program has the same problem of double booking rooms as ptrogram 1. However, unlike Program 1 a crash can be quite problematic because it may mean that D3.4 succeeds and I3.5 fails. That would cause the lecture to be lost. Preventing double booking requires a transaction with isolation level serializable, but preventing loss of data just needs any transaction (because atomicity is always guaranteed). Also, just changing the DELETE and INSERT into a single UPDATE will solve the atomicity problem.
Setting the transaction to start outside the loop and commit after it is finished (i.e. never) will cause major problems. Particularly the database will be blocked forever for any transaction which is not read uncommitted. Also since it's always possible to abort a transaction and roll back, if the program crashes after running for several years, the DBMS will roll back all the changes it has performed in that time! Setting the transaction to include the entire body of the while loop can be problematic since it will block while the program sleeps. Depending on how long the sleep is the database might "stutter" noticably or it may be blocked for long periods of time. The correct solution is to start the transaction after the sleep and end it at the end of the while loop.
Another peculiar error that might occur if P3 is run in parallel with another instance of P3 is that they may look at the same course but choose different random days. In that case both will execute the delete, one of them will just delete 0 rows, and both of them will execute the insert. In the end this will have duplicated the course. This is also solved by adding a transaction with serializable or by checking that the result of the executeUpdate call is not 0 for the DELETE.