Exercise 6.6.1 a) void PrintModelPrice(int speedIn, int ramIn) { EXEC SQL BEGIN DECLARE SECTION; int speed,ram; int myModel,myPrice; EXEC SQL END DECLARE SECTION; speed = speedIn; ram = ramIn; EXEC SQL SET TRANSACTION READ-ONLY ISOLATION LEVEL READ UNCOMMITTED (or REPEATABLE READ); EXEC SQL BEGIN TRANSACTION; EXEC SQL DECLARE myCursor CURSOR FOR SELECT model, price FROM Product prod, PC p WHERE prod.model = p.model AND speed = :speed AND ram = :ram; EXEC SQL OPEN pcInfo; EXEC SQL FETCH FROM myCursor INTO :myModel, :myPrice; WHILE (MORE_TUPLES) Print(:modelNo, :price); EXEC SQL FETCH FROM myCursor INTO :myModel, :myPrice; ENDWHILE; EXEC SQL CLOSE myCursor; EXEC SQL COMMIT; } b) void deleteModel(int modelNoIn) { /* get the model number from the user and store it in modelNo */ EXEC SQL BEGIN DECLARE SECTION; int modelNo; EXEC SQL END DECLARE SECTION; modelNo = modelNoIn; EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; (or EXEC SQL SET TRANSACTION READ WRITE ISOLATION LEVEL READ UNCOMMITTED; ) EXEC SQL BEGIN TRANSACTION; EXEC SQL DELETE FROM Product WHERE model = :modelNo; EXEC SQL DELETE FROM PC WHERE model = :modelNo; EXEC SQL COMMIT; } c) void increasePrice(int modelNoIn) { EXEC SQL BEGIN DECLARE SECTION; int modelNo; EXEC SQL END DECLARE SECTION; modelNo = modelNoIn; EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; EXEC SQL BEGIN TRANSACTION; EXEC SQL UPDATED PC SET PRICE = PRICE - 100 WHERE model = :modelNo; EXEC SQL COMMIT; } d) void enterModel(int makerIn, int modelIn, int speedIn, int ramIn, int hdIn, int priceIn) { EXEC SQL BEGIN DECLARE SECTION; int myMaker; int myModel; int mySpeed; int myRam; int myHd; int myPrice; int myCount; EXEC SQL END DECLARE SECTION; myMaker = makerIn; myModel = modelIn; mySpeed = speedIn; myRam = ramIn; myHd = hdIn; myPrice = priceIn; EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; EXEC SQL BEGIN TRANSACTION; EXEC SQL SELECT COUNT(*) INTO :myCount FROM PRODUCT WHERE MAKER = :myMaker AND MODEL = :myModel; if (myCount == 0) { EXEC SQL INSERT INTO PRODUCT(MAKER,MODEL) VALUES (:myMaker,:myModel); EXEC SQL INSERT INTO PC(MODEL,SPEED,RAM,HD,PRICE) VALUES (:myModel,:mySpeed,:myRam,:myHd,:myPrice); EXEC SQL COMMIT; } else { EXEC SQL RAISE ERROR(msg); /* assume that this raises a local exception as well that should be handle somewhere above in invocation chain */ } } Exercise 6.6.2 a) Read only process, only side effect is that list displayed wouldn't be complete. b) If no transaction code is inserted then it is possible for a PC to exist without a corresponding Product tuple for it. However, well established constraints should prevent this from happening. With transaction code then no deletion will be final until both are. c) Single statement with no possibility of suffering atomicity problems. d) If no transaction code is inserted then Product data might go in without its corresponding PC data insertion. If the user tries to run it again then it will fail claiming that there is a product with that number in the DB already. With transaction code, it should be ok as neither insertion will be final. Exercise 6.6.3 a) Read Uncommited: It will get all tuples in table, even those inserted while others were being read. However it might display tuples that were not finalized, thus displaying data to the user that might actually not have made it permanently into the DB. Serializable: It will only display data available at the beginning of the transaction. Although this ensures that all data displayed is in the DB, it won't allow insertions while running this possibly long transaction. b) Read Uncommited: It might try to delete an element that hasn't been permanently set in the DB. If element is committed after this transaction, then the delete would have appeared to not happen. Serializable: Will delete the element if it exists when the transaction begins. c) Read Uncommited: Because the price update is based on the current price it might update it based on an uncommited price which is potentially dangerous. The committed price is 400, but its currently "dirty" at 500, our trans runs and sets it to 400, both commit and it might appear as though no change happened. Serializable: Safer because it ensures that new value is based on current commited value and that no one else is accessing the value. d) Read Uncommited: The insertion might not be done if someone else is currently inserting the same value, but if that other trans aborts then none gets done even though our transaction signaled that data was already there. Serializable: Safe no significant side effects. Exercise 6.6.4 It makes no sense to run T serializably. Once T starts, it would never see any changes to the database. In practice, a long-running transaction like T would have to be aborted by the system periodically. That would have the fortunate effect of allowing T to restart and see any new PC's that were added to the database. For T's purposes, it is sufficent if it runs with isolation level repeatable-read. That will guarantee that whenever it sees a tuple, it will continue to see that tuple. However, it will also be allowed to see new tuples as they are added to the database. The potential problem with repeatable-read is that the transaction of Exercise 6.6.1(c), which lowers the price of a PC, might not be able to run while T is running, because it would change the tuple with the old price, which T is required to see. On the other hand, systems that support ``multiversion'' concurrency control would allow T to see the old tuple, while other transactions can see the new one. T can also run at isolation level read-committed, and this choice is probably best. It will eventually see any new or updated tuple that the transactions of Exercise 6.6.1(c) or (d) produce, while not putting any constraints on these transactions. Isolation level read-uncommitted for T is a possibility, although it would have the consequence that a new PC could be inserted into the database very briefly, while the person inserting the PC has a change of heart and aborts the change at the terminal, while T has already seen the PC's information.