HW#4 --- last modified January 01 1970 00:00:00..
Solution set.
Due date: Nov 18
Files to be submitted:
problems.zip
code.zip
Purpose: To gain practice with concurrency and serializability
concepts. To write a few transactions that have commit
and rollback's and to do experiments with them.
Specification:
Do problems 19.23 and 19.24 out of the book and submit them in Problems.zip.
For the programming part of the assignment you will write two transactions
but rewrite them using three different techniques for talking to an
Oracle DB: using JDBC, using SQLJ, and in C using embedded SQL. Your goal
will then be to do some experiments with your programs. The JDBC program should make non-trivial use of PreparedStatement. Your transactions will perform
their operations on the following table:
CREATE TABLE COUNTER
(
firstDigit INT,
secondDigit INT
);
This table will initially only be populated with one row: (0, 0).
Both your transactions update the row. The first transaction reads
firstDigit, adds one to it, and updates the row with this new value.
If firstDigit is less than 10 after the increment the transaction commits.
If firstDigit, however, is a 10 it sets it to 0 and updates.
In this case, it then reads secondDigit. If second digit is less than 9
it then increments second digit and update the row again and commit.
If secondDigit is a 9 the transaction is rolled back.
The second transaction is like the first except it tries to decrement the
two digit number. That is, if firstDigit is bigger than 0 subtract 1
from it, update, and commit. If the firstDigit is a 0 change it to a 9
update the row, and read the secondDigit. If this is bigger than 0 decrement
1 from it update, and commit. Otherwise, rollback.
Given these two transactions you need to :
(1) Design an experiment to determine the average time each
transaction takes for each of the three methods:
JDBC, SQLJ, and embedded SQL. Make sure to perform enough
trials to get a reasonable answer. You should compute this
time for where only a single digit changes, in the case both
digits are affected and there is a rollback, and in the case both
digits are affected and the transaction commits.
(2) Given this you should try to rank the different techniques and comment
on whether there is a significant time difference between the methods.
(3) Next write a program which uses your JDBC transactions in two threads.
Thread 1 sleeps a random, short (say half a transaction time) interval
and then tries to execute the increment transaction. After
it finishes the transaction it does the same thing again. Thread 2 is like
Thread 1 except it executes the decrement transaction. Make a driver
program that allows a 1000 total transactions to be run by these threads.
It also keeps track of what it thinks the row value should be. Run an
experiment to see if your program matches with what actually is the final
row value in the Oracle table. Discuss your results.
Your experiments and results should be in the file code.zip. This file
should contain a readme.txt file explaining how using the programs
in code.zip to replicate your experiments. It should also contain
a file output.txt which contains a print-out of the output of all your
experiments together with commentary on your results.
Point Breakdown
Departmental coding guidelines for Java followed | 1pt |
Problems out of book (1.5 pts each) |
3pts |
Code for each transaction in each of the
three schemes above works (1/2 pt each) | 3pts |
(1) above | 1pt |
(2) above | 1pt |
(3) above | 1pt |
Total | 10pts |
|