| A First Course in Database Systems |
|
| A First Course in Database Systems |
NO_MORE_TUPLES as defined in
the section.
void closestMatchPC() {
EXEC SQL BEGIN DECLARE SECTION;
char manf[30], SQLSTATE[6];
int targetPrice, /* holds price given by user */
speedOfClosest, modelOfClosest, priceOfClosest,
/* for closest price found so far */
tempSpeed, tempModel, tempPrice;
/* for tuple just read from PC */
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor CURSOR FOR
SELECT model, price, speed FROM PC;
EXEC SQL OPEN pcCursor;
/* ask user for target price and read the answer into variable
targetPrice */
/* Initially, the first PC is the closest to the target price.
If PC is empty, we cannot answer the question, and so abort. */
EXEC SQL FETCH FROM pcCursor INTO :modelOfClosest, :priceOfClosest,
:speedOfClosest;
if(NO_MORE_TUPLES) /* print message and exit */ ;
while(1) {
EXEC SQL FETCH pcCursor INTO :tempModel, :tempPrice,
:tempSpeed;
if(NO_MORE_TUPLES) break;
if( /* tempPrice closer to targetPrice than is priceOfClosest */) {
modelOfClosest = tempModel;
priceOfClosest = tempPrice;
speedOfClosest = tempSpeed;
}
}
/* Now, modelOfClosest is the model whose price is closest to
target. We must get its manufacturer with a single-row select */
EXEC SQL SELECT maker
INTO :manf
FROM Product
WHERE model = :modelOfClosest;
printf("manf =%s, model = $d, speed = $d\n", :manf, :modelOfClosest,
:speedOfClosest);
EXEC SQL CLOSE CURSOR pcCursor;
}
Again, the macro NO_MORE_TUPLES is used to test for the end
of the relation.
void lowerPrices() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor INSENSITIVE CURSOR FOR PC;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH FROM pcCursor;
if(NO_MORE_TUPLES) break;
EXEC SQL UPDATE PC
SET price = price - 100
WHERE CURRENT OF execCursor;
}
EXEC SQL CLOSE pcCursor;
}
void twoMoreExpensive() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6], cd[5], cd1[5];
int model, model1, speed, speed1, ram, ram1, price, price1;
float hd, hd1; /* we use the variables cd1, model1, etc, to
read the tuple 2 positions later, to see if the speed has
not changed (in which case there are at least two PC's
with the same speed and at least as high a price) */
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor SCROLL CURSOR FOR
SELECT * FROM PC ORDER BY speed, price;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH NEXT FROM pcCursor INTO :model, :speed, :ram,
:hd, :cd, :price;
if(NO_MORE_TUPLES) break;
EXEC SQL FETCH RELATIVE +2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1;
if(NO_MORE_TUPLES) break;
if(speed1 == speed) /* print the tuple :model, :speed, etc. */ ;
EXEC SQL FETCH RELATIVE -2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1;
/* line above just to reset the cursor to where it was */
}
EXEC SQL CLOSE pcCursor;
EXEC SQL BEGIN DECLARE SECTION;
int modelNo;
EXEC SQL END DECLARE SECTION;
void deleteModel() {
/* get the model number from the user and store it in
modelNo
*/
EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
EXEC SQL DELETE FROM Product
WHERE model = :modelNo;
EXEC SQL DELETE FROM PC
WHERE model = :modelNo;
EXEC SQL COMMIT;
}
We think that it is wise to set the isolation level to serializable,
even though no tuples are written by this transaction.
The potential problem is that another transaction could read one of the
two tuples for a given model, either from Product or
PC, while this transaction was deleting the other tuple.
On the other hand, that transaction, if it really needed to be sure that
either both tuples or none were present, could set its own isolation
level to serializable.
Since there is no harm that can come to our transaction if one
and not the other tuple is present, we think it is also acceptable to
replace the SET-TRANSACTION statement by:
EXEC SQL SET TRANSACTION READ WRITE
ISOLATION LEVEL READ UNCOMMITTED;
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 7.2.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 7.2.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.