|
Chris Pollett >
Old Classes
> |
HW4 Solutions Page19.23 Serializability graph for
a b c d e f g h i j
S1:r1(X), r2(Z), r1(Z), r3(X), r3(Y), w1(X), w3(Y), r2(Y), w2(Z), w2(Y)
T1 --> T2
^ ^
\ /
T3
Arc Explanations T1-->T2 because of c and i above. T3-->T1 because of d and a above. T3-->T2 because of g and h above.
No cycles, so serializable. Serializability graph for
a b c d e f g h i j
S2:r1(X), r2(Z), r3(X), r1(Z), r2(Y), r3(Y), w1(X), w2(Z), w3(Y), w2(Y)
T1 --> T2--|
^ ^ |
\ / |
T3<----/
Arc Explanations T1-->T2 because of d and h above. T2-->T3 because of e and i above. T3-->T1 because of c and g above. T3-->T2 because of i and j above.
There is a cycle, so not serializable.
a b c d e f g h i j
S3:r1(X),r2(Z),r1(Z),r3(X),r3(Y),w1(X),c1,w3(Y),c3,r2(Y),w2(Z),w2(Y),c2.
This schedule is same as S1 in 19.23 except commits added so will be serializable. Pretending any of c1 or c2 or c3 were aborts would not force us to abort another transaction so this schedule is recoverable . This schedule does not read from any uncommitted but written values so cascadeless. In fact, it is strict as never read or write a committed transaction.
a b c d e f g h i j
S4:r1(X),r2(Z),r1(Z),r3(X),r3(Y),w1(X),w3(Y),r2(Y),w2(Z),w2(Y),c1,c2,c3.
This schedule is same as S1 in 19.23 except commits added so will be serializable. Pretending T3 aborts at its end would force us to abort T2 so schedule is not recoverable . This schedule is not strict and not cascadeless as we read in h from a value written in g by an uncommitted transaction.
a b c d e f g h i j
S5:r1(X),r2(Z),r3(X),r1(Z),r2(Y),r3(Y),w1(X),c1,w2(Z),w3(Y),w2(Y),c3,c2
This schedule is same as S2 in 19.23 except commits added so will be unserializable. Pretending any of c1 or c2 or c3 were aborts would not force us to abort another transaction so this schedule is recoverable . This schedule does not read from any uncommitted but written values so cascadeless. This schedule is not strict as we write in j a value written in i by an uncommitted transaction.
Here is what running my scripts looked like at home:
C:\classes\157b.12.02f\Hw4Transactions>runhw4
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM RunHw4.bat
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM This is the DOS batch file used
to do all the experiments of Hw4
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM To choose which experiments
you'd like to do rather than run all of them
C:\classes\157b.12.02f\Hw4Transactions>REM you can put remarks in front of
experiments don't want.
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM This batch file asks you to hit
a key between each test.
C:\classes\157b.12.02f\Hw4Transactions>REM This is so oracle has time to
garbage collect old transactions
C:\classes\157b.12.02f\Hw4Transactions>REM so that it does abort any
operations in a trial.
C:\classes\157b.12.02f\Hw4Transactions>REM So maybe wait a minute before
hitting continue.
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM To convert this batch file to a
UNIX script do the follwoing kinds of replacem
ents:
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM REM --> #
C:\classes\157b.12.02f\Hw4Transactions>REM --> {$ENVIRONMENT_VARIABLE}
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>SET JAVA=java
C:\classes\157b.12.02f\Hw4Transactions>REM SET
CONNECT=jdbc:oracle:thin:@sigma.mathcs.sjsu.edu:1521:cs157b
C:\classes\157b.12.02f\Hw4Transactions>SET
CONNECT=jdbc:oracle:thin:@localhost:1521:epsilon
C:\classes\157b.12.02f\Hw4Transactions>SET
EMBEDCONNECT=localhost:1521:epsilon
C:\classes\157b.12.02f\Hw4Transactions>SET SERVICE_NAME=ep
C:\classes\157b.12.02f\Hw4Transactions>SET LOGIN=cpollett
C:\classes\157b.12.02f\Hw4Transactions>SET PASSWORD=mypassword
C:\classes\157b.12.02f\Hw4Transactions>SET TRIALS=50
C:\classes\157b.12.02f\Hw4Transactions>SET CTRIALS=3
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>REM The next variables are for the
thread test. I am setting the default
C:\classes\157b.12.02f\Hw4Transactions>REM number of trials to be 10
(which is quick). Change to a 1000 for HW experiment
C:\classes\157b.12.02f\Hw4Transactions>REM VERBOSE mode is on. Which means
all timestamps are printed,which if the number
of
C:\classes\157b.12.02f\Hw4Transactions>REM of trials is large may be
tiresome to look at. You can shut off timestamp prin
ting
C:\classes\157b.12.02f\Hw4Transactions>REM by setting this variable false
C:\classes\157b.12.02f\Hw4Transactions>REM
C:\classes\157b.12.02f\Hw4Transactions>SET THREADTRIALS=10
C:\classes\157b.12.02f\Hw4Transactions>SET VERBOSE=true
C:\classes\157b.12.02f\Hw4Transactions>java Hw4TransactionTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime JD
BCIncrementTransaction 50
Avg. time to update when row value (0 , 0) : 39.68
Avg. time to update when row value (9 , 0) : 64.98
Avg. time to update when row value (0 , 1) : 57.2
Avg. time to update when row value (9 , 9) : 55.62
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157b.12.02f\Hw4Transactions>java Hw4TransactionTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime JD
BCDecrementTransaction 50
Avg. time to update when row value (0 , 0) : 40.92
Avg. time to update when row value (9 , 0) : 57.82
Avg. time to update when row value (0 , 1) : 61.24
Avg. time to update when row value (9 , 9) : 57.48
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157b.12.02f\Hw4Transactions>java Hw4TransactionTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime SQ
LJIncrementTransaction 50
Avg. time to update when row value (0 , 0) : 44.38
Avg. time to update when row value (9 , 0) : 64.7
Avg. time to update when row value (0 , 1) : 58.78
Avg. time to update when row value (9 , 9) : 61.86
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157B12~1.02F\HW4TRA~1>java Hw4TransactionTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime SQLJDecr
ementTransaction 50
Avg. time to update when row value (0 , 0) : 25.3
Avg. time to update when row value (9 , 0) : 79.98
Avg. time to update when row value (0 , 1) : 61.88
Avg. time to update when row value (9 , 9) : 57.8
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157B12~1.02F\HW4TRA~1>IncrementTest ep cpollett ptime 3
Avg. time to update when row value (0, 0) in ms : 1567
Avg. time to update when row value (9, 0) in ms : 2328
Avg. time to update when row value (0, 1) in ms : 2328
Avg. time to update when row value (9, 9) in ms : 2323
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157b.12.02f\Hw4Transactions>DecrementTest ep cpollett ptime 3
Avg. time to update when row value (0, 0) in ms : 1583
Avg. time to update when row value (9, 0) in ms : 2328
Avg. time to update when row value (0, 1) in ms : 2338
Avg. time to update when row value (9, 9) in ms : 2323
C:\classes\157b.12.02f\Hw4Transactions>PAUSE
Press any key to continue . . .
C:\classes\157b.12.02f\Hw4Transactions>java Hw4ThreadTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime JDBCInc
rementTransaction JDBCDecrementTransaction 10 true
Random8ThreadThread[Thread-0,5,main]
Random20ThreadThread[Thread-1,5,main]
Random8ThreadThread[Thread-0,5,main]
Random14ThreadThread[Thread-1,5,main]
Random22ThreadThread[Thread-0,5,main]
Random11ThreadThread[Thread-1,5,main]
Random11ThreadThread[Thread-0,5,main]
Random1ThreadThread[Thread-1,5,main]
Random7ThreadThread[Thread-0,5,main]
Random25ThreadThread[Thread-1,5,main]
Thread done.Thread[Thread-1,5,main]
Random10ThreadThread[Thread-0,5,main]
Thread done.Thread[Thread-0,5,main]
Trans1 timestamps
start1038618822668
stop1038618822746
start1038618822762
stop1038618822887
start1038618822903
stop1038618823043
start1038618823059
stop1038618823168
start1038618823199
stop1038618823324
start1038618823340
stop1038618823449
Trans2 timestamps
start1038618822684
stop1038618822824
start1038618822840
stop1038618822965
start1038618822981
stop1038618823106
start1038618823121
stop1038618823246
start1038618823262
stop1038618823387
Estimated count should be:1
C:\classes\157b.12.02f\Hw4Transactions>REM you can run the same test using
the SQLJ code by uncommenting the next line
C:\classes\157b.12.02f\Hw4Transactions>REM java Hw4ThreadTest
jdbc:oracle:thin:@localhost:1521:epsilon cpollett ptime SQL
JIncrementTransaction SQLJDecrementTransaction 10 true
C:\classes\157B12~1.02F\HW4TRA~1>sqlplus
SQL*Plus: Release 9.0.1.3.0 - Production on Fri Nov 29 17:22:56 2002
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: cpollett@ep
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> select * from COUNTER;
FIRSTDIGIT SECONDDIGIT
---------- -----------
1 0
SQL>
Some comments on these results. I was timing the whole transaction
including connection times. Some people might not have done this and just
timed the time of the actual transaction. (The latter may have actually
been a better choice especially in the case of embedded sql.) The
embedded sql result seemed to take a very long time (much longer than
Java) and not vary reliably based the row checked. I suspect maybe
something to do with the connection process or not providing hints to the
compiler about single row selects? Both JDBC and SQLJ seemed to take
comparable times to perform the various transactions. For increment
transactions for JDBC and SQLJ, changing a single digit was faster than
changing two digits which was faster than doing everything and rolling
back. For decrement transaction the rollback situation was when the row
was initially (0, 0). This actually was the fastest time. However, if one
looks at the was the decrement operation was described in the HW
description, it essentially just reads the two digits without doing
an update then rolls back without having updated anything. So it actually
involves fewer operations than the other transactions. So the fact it was
fastest makes sense. The thread test used timestamps that were written
before and after each transaction to figure out the final count.
Basically, it was assumed the transaction only took effect when it
completed. Thus, looking at the sequence of ending time timestamps for
each thread, knowing what transaction the given thread was trying to
perform at each time, and also what the estimated current value was at
that time, a final value of the count was estimated. This seemed to match
with Oracle when onlya few hundred transaction were considered. In the
case above it is verified when the number of trials was 10. When the
number of trials was a 1000, it was usually the case that oracle would
abort one of the transaction while it was writing a checkpoint a couple
times in the sequence. This would show up to my program as though (since I
didn't check if the trnasaction finished normally or because of an
exception) the transaction had gone through. thus, my count would be off
by one or two in this situation.
Here is my actual code for this homework:
//ReadMe.txt
Instructions
============
To create this project:
(1) Use HW4MakeTable.sql to create the one Oracle table used by this project
(2) Edit then run either MakeProject.bat (DOS) or Makefile (Unix) to compile the project
(3) Edit then run RunHw4.bat to actually perform the trials of this project.
/*
HW4MakeTable.sql
This was the SQL file used to generate the only ORACLE table used in this project
*/
DROP TABLE COUNTER;
CREATE TABLE COUNTER(
firstDigit INTEGER,
secondDigit INTEGER
);
INSERT INTO COUNTER VALUES (0, 0);
REM MakeProject.bat
REM
REM DOS Batch file to make the HW4 Project
REM
REM Note for sqlj I added the following two paths to my classpath
REM e:\oracle\designer\sqlj\lib\translator.lib
REM e:\oracle\designer\sqlj\lib\runtime12ee.zip
REM
REM
REM You should change the path to the next batch file to where it is on your machine
REM This sets up the environment variables for CL.exe
REM
CALL "C:\Program Files\Microsoft Visual Studio .NET\Vc7\bin\vcvars32.bat"
REM
REM You should also modify the next files to match your system
REM
SET JAVAC=javac
SET SQLJ="e:\oracle\designer\bin\sqlj"
SET PROC="e:\oracle\designer\bin\proc"
SET CC=CL
SET INC="/IE:\oracle\designer\precomp\public"
SET LIBRARY="E:\oracle\designer\precomp\LIB\msvc\ORASQL9.LIB"
SET OUT=/o
%SQLJ% SQLJIncrementTransaction.sqlj
%SQLJ% SQLJDecrementTransaction.sqlj
%JAVAC% *.java
%PROC% iname=IncrementTransaction.pc
%PROC% iname=DecrementTransaction.pc
%PROC% iname=TransactionTest.pc
%CC% %INC% TransactionTest.c IncrementTransaction.c %LIBRARY% %OUT%IncrementTest.exe
%CC% %INC% TransactionTest.c DecrementTransaction.c %LIBRARY% %OUT%DecrementTest.exe
REM
REM MakeClean.bat
REM
REM DOS BATCH file to clean project
REM
REM deletes all target and intermediate files for HW4 Project
DEL *.class *.c *.o *.obj SQLJ*.java
Note if you want to create this project on a UNIX system you will probably
want to modify the following Makefile:
#
# Program to make HW4 assuming have GNU make and rm on your machine.
# Since I have cygwin at home I had this files so was using this file
# for development.
#
# This file is probably easiest to modify to build the project on a
# UNIX system.
#
# If change the paths to values appropriate for your machine then command
# (should set variable CC equal to gcc on UNIX):
#
# make
#
# should compile this project and
#
# make clean
#
# will get rid of intermediates and executables.
#
# note for sqlj I added the following two paths to my classpath
# e:\oracle\designer\sqlj\lib\translator.lib
# e:\oracle\designer\sqlj\lib\runtime12ee.zip
#
JAVAC = javac
SQLJ = "e:\oracle\designer\bin\sqlj"
PROC = "e:\oracle\designer\bin\proc"
CC = CL
INC = "/IE:\oracle\designer\precomp\public"
LIBRARY = "E:\oracle\designer\precomp\LIB\msvc\ORASQL9.LIB"
LINK =
OUT = /o
all: test base jdbc sqlj proc thread
test: Hw4TransactionTest.class
base: Hw4Transaction.class
jdbc: JDBCIncrementTransaction.class JDBCDecrementTransaction.class
sqlj: SQLJIncrementTransaction.class SQLJDecrementTransaction.class
proc: IncrementTest.exe DecrementTest.exe
thread: Hw4ThreadTest.class
IncrementTest.exe: TransactionTest.c IncrementTransaction.c
{$CC} {$INC} TransactionTest.c IncrementTransaction.c {$LIBRARY} {$OUT}$@
DecrementTest.exe: TransactionTest.c DecrementTransaction.c
{$CC} {$INC} TransactionTest.c DecrementTransaction.c {$LIBRARY} {$OUT}$@
%.class: %.java
$(JAVAC) $<
%.java: %.sqlj
$(SQLJ) $<
%.c: %.pc
$(PROC) iname=$<
clean:
rm -rf *.class *.c *.o *.obj SQLJ*.java
rebuild: clean all
REM
REM RunHw4.bat
REM
REM
REM This is the DOS batch file used to do all the experiments of Hw4
REM
REM To choose which experiments you'd like to do rather than run all of them
REM you can put remarks in front of experiments don't want.
REM
REM This batch file asks you to hit a key between each test.
REM This is so oracle has time to garbage collect old transactions
REM so that it does abort any operations in a trial.
REM So maybe wait a minute before hitting continue.
REM
REM To convert this batch file to a UNIX script do the follwoing kinds of replacements:
REM
REM REM --> #
REM %ENVIRONMENT_VARIABLE% --> {$ENVIRONMENT_VARIABLE}
REM
SET JAVA=java
REM SET CONNECT=jdbc:oracle:thin:@sigma.mathcs.sjsu.edu:1521:cs157b
SET CONNECT=jdbc:oracle:thin:@localhost:1521:epsilon
SET EMBEDCONNECT=localhost:1521:epsilon
SET SERVICE_NAME=ep
SET LOGIN=cpollett
SET PASSWORD=mypassword
SET TRIALS=50
SET CTRIALS=3
REM
REM The next variables are for the thread test. I am setting the default
REM number of trials to be 10 (which is quick). Change to a 1000 for HW experiment
REM VERBOSE mode is on. Which means all timestamps are printed,which if the number of
REM of trials is large may be tiresome to look at. You can shut off timestamp printing
REM by setting this variable false
REM
SET THREADTRIALS=10
SET VERBOSE=true
%JAVA% Hw4TransactionTest %CONNECT% %LOGIN% %PASSWORD% JDBCIncrementTransaction %TRIALS%
PAUSE
%JAVA% Hw4TransactionTest %CONNECT% %LOGIN% %PASSWORD% JDBCDecrementTransaction %TRIALS%
PAUSE
%JAVA% Hw4TransactionTest %CONNECT% %LOGIN% %PASSWORD% SQLJIncrementTransaction %TRIALS%
PAUSE
%JAVA% Hw4TransactionTest %CONNECT% %LOGIN% %PASSWORD% SQLJDecrementTransaction %TRIALS%
PAUSE
IncrementTest %SERVICE_NAME% %LOGIN% %PASSWORD% %CTRIALS%
PAUSE
DecrementTest %SERVICE_NAME% %LOGIN% %PASSWORD% %CTRIALS%
PAUSE
%JAVA% Hw4ThreadTest %CONNECT% %LOGIN% %PASSWORD% JDBCIncrementTransaction JDBCDecrementTransaction %THREADTRIALS%
%VERBOSE%
REM you can run the same test using the SQLJ code by uncommenting the next line
REM %JAVA% Hw4ThreadTest %CONNECT% %LOGIN% %PASSWORD% SQLJIncrementTransaction SQLJDecrementTransaction
%THREADTRIALS% %VERBOSE%
// Hw4TransactionTest.java -- program to test JDBC and SQLJ transactions for HW4
import java.io.*;
import java.sql.*;
/**
This class consists of methods to test the runtime of the JDBC and SQLJ
transactions described in HW4.
This program should be run with a line like:
java Hw4TransactionTest connect login password transaction trials
connect - connect string for Oracle DB
login - your login on oracle
password - your password on oracle
transaction - name of class containing transaction note flexible could
be implemented with JDBC, SQLJ or Proc and native interface (didn't do last)
trials - number of trials would like to test
The transactions are assumed to be subclasses of HW4Transaction.
@author Chris Pollett
@version 1.0.2002
*/
public class Hw4TransactionTest
{
/**
Creates an instance of this class given the supplied parameters and
loads the OracleDriver
@param connectString - string used to connect the oracle host:port:dbname
@param login - login name on oracle
@param password - password to log into oracle
@param transaction name of class file with transaction
@param trials - string containing number of trials to run
*/
public Hw4TransactionTest(String connectString, String login,
String password, String transaction, String trials)
{
this.connectString = connectString;
this.login = login;
this.password = password;
this.trials = Double.parseDouble(trials);
try
{
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
currentTransaction = (Hw4Transaction)Class.forName(transaction).newInstance();
currentTransaction.create(connectString, login, password);
}
catch(Exception e)
{
System.err.println("Unable to find classes for transaction.");
e.printStackTrace();
}
}
/**
Used to set the row of the counter table to a fixed value
@param first - value to set firstDigit to
@param second - value to set second digit to
*/
public void setRow(int first, int second)
{
try
{
Connection conn =
DriverManager.getConnection(connectString, login, password);
PreparedStatement pstmt = conn.prepareStatement(
"UPDATE COUNTER SET firstDigit = ?, secondDigit = ? ");
pstmt.setInt(1, first);
pstmt.setInt(2, second);
pstmt.executeUpdate();
pstmt.close();
conn.close();
}
catch(Exception e)
{
System.err.println("Error setting row to fixed value");
e.printStackTrace();
System.exit(1);
}
}
/**
Tests how long the transaction takes for various starting row values
Outputs the results to the screen
*/
public void doTrials()
{
//probably should switch to reading these from a file
trial(0,0);
trial(9,0);
trial(0,1);
trial(9,9);
}
/**
Find the average time in milliseconds of the transaction
from the given starting row value over the stored number of trials
@param firstDigit - firstDigit of row in Counter Table
@param secondDigit - secondDigit of row in Counter Table
*/
public void trial(int firstDigit, int secondDigit)
{
//Time setRow operation
long startTime = System.currentTimeMillis();
for(int i=0; i< trials; i++)
{
setRow(firstDigit, secondDigit);
}
long deltaRowTime = System.currentTimeMillis()-startTime;
startTime = System.currentTimeMillis();
for(int i=0; i< trials; i++)
{
setRow(firstDigit, secondDigit);
currentTransaction.execute();
}
long totalTimeTrials =
System.currentTimeMillis() - startTime - deltaRowTime;
System.out.println("Avg. time to update when row value ("+
firstDigit+" , "+secondDigit+") : "+totalTimeTrials/trials);
}
/**
Create an instance of this Hw4TransactionTest class and runs it.
@param args - array of command line arguments
*/
public static void main (String [] args)
{
if(args.length < 5)
{
System.out.println("This program should be run with a line like:\n");
System.out.print("java Hw4TransactionTest connect login password ");
System.out.println("transaction trials\n\nwhere\n");
System.out.println("connect - connect string for Oracle DB");
System.out.println("login - your login on oracle");
System.out.println("password - your password on oracle");
System.out.println("transaction - name of class containing transaction");
System.out.println("trials - number of trials would like to test");
}
else
{
Hw4TransactionTest hw4=
new Hw4TransactionTest(args[0],args[1],args[2],args[3],args[4]);
hw4.doTrials();
}
}
Hw4Transaction currentTransaction; //the transaction we're testing
String connectString; // string to connect to Oracle host:port:dbname
String password; // password to connect to Oracle
String login; // login to connect to Oracle
double trials; // number of trials to perform
}
// Hw4TransactionTest.java -- program to test JDBC and SQLJ transactions for HW4
import java.io.*;
import java.util.*;
import java.sql.*;
/**
This class consists of methods to perform the thread tesets of
transactions described in HW4.
This program should be run with a line like:
java Hw4ThreadTest connect login password transaction1 transaction2 trials
connect - connect string for Oracle DB
login - your login on oracle
password - your password on oracle
transaction1 - name of class containing increment transaction (note flexible could
be implemented with JDBC, SQLJ or Proc and native interface (didn't do last)
transaction2 - name of class containing decrement transaction
trials - number of trials would like to test
The transactions are assumed to be subclasses of HW4Transaction.
@author Chris Pollett
@version 1.0.2002
*/
public class Hw4ThreadTest
{
/**
Initializes the test class. Loads oracle driver and says if verbose or not mode
@param connectString - string used to connect the oracle host:port:dbname
@param login - login name on oracle
@param password - password to log into oracle
@param transaction1 - name of a class file with increment transaction
@param transaction2 - name of a class file with decrement transaction
@param trials - string containing number of trials to run
*/
public Hw4ThreadTest(String connectString, String login,
String password, String t1, String t2,
String trials, String vbose)
{
this.connectString = connectString;
this.login = login;
this.password = password;
this.trials = Double.parseDouble(trials);
this.verbose = Boolean.valueOf(vbose).booleanValue();
try
{
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
Hw4Transaction transaction1 = (Hw4Transaction)Class.forName(t1).newInstance();
transaction1.create(connectString, login, password);
thread1 = new Hw4Thread(transaction1);
Hw4Transaction transaction2 = (Hw4Transaction)Class.forName(t2).newInstance();
transaction2.create(connectString, login, password);
thread2 = new Hw4Thread(transaction2);
count = 0;
Hw4TransactionTest hw4 = new Hw4TransactionTest(connectString, login,
password, t1, trials);
hw4.setRow(0,0);
}
catch(Exception e)
{
System.err.println("Unable to find classes for transaction.");
e.printStackTrace();
}
}
/**
This method actually performs the tests. The main thread checks every two seconds
whether the two threads doing transactions are done.
Depending on the mode. It then prints out a list of time stamps and what it
thought the final value of the row should be. This last is based on current
calculated value and the stop times of the stored time stamps.
*/
public synchronized void doTrials()
{
thread1.start();
thread2.start();
while(thread1.undone() || thread2.undone() )
{
try
{
wait(2000);
}
catch(InterruptedException ie)
{
ie.printStackTrace();
}
}
ArrayList stamps1 = thread1.getTimeStamps();
ArrayList stamps2 = thread2.getTimeStamps();
if (verbose) printStamps(stamps1, stamps2);
int i=0;
int j=0;
int cnt = 0;
while (i < stamps1.size() || j < stamps2.size())
{
if(i >= stamps1.size())
{
j+=2;
cnt = (cnt>0)? cnt-1 : 0;
}
else if(j >= stamps2.size())
{
i+=2;
cnt = (cnt<100)? cnt+1 : 99;
}
else
{
String s1 = (String)stamps1.get(i);
String s2 = (String)stamps2.get(j);
if(s1.compareTo(s2) < 0)
{
i+=2;
cnt = (cnt<100)? cnt+1 : 99;
}
else
{
j+=2;
cnt = (cnt>0)? cnt-1 : 0;
}
}
}
System.out.println("Estimated count should be:"+cnt);
}
/**
Helper function of doTrials(). Called when in verbose mode.
Prints out the array of time stamps the two threads.
@param l1 - thread1's time stamps (presumably)
@pram l2 - thread2's time stamps;
*/
void printStamps(ArrayList l1, ArrayList l2)
{
System.out.println("Trans1 timestamps");
for(int i=0; i < l1.size(); i++)
{
System.out.println(""+l1.get(i));
}
System.out.println("Trans2 timestamps");
for(int i=0; i < l2.size(); i++)
{
System.out.println(""+l2.get(i));
}
}
/**
Increments the count of the total number of transactions that have taken place.
*/
void incrementCount()
{
count++;
}
/**
@return - the total number of transactions that have taken place so far in the
trial
*/
int getCount()
{
return count;
}
/**
Creates an instance of the Hw4ThreadTest classes and uses it to run the test
@param args - array of command line arguments
*/
public static void main (String [] args)
{
if(args.length < 6)
{
System.out.println("This program should be run with a line like:\n");
System.out.print("java Hw4ThreadTest connect login password ");
System.out.println("transaction1 transaction2 trials\n\nwhere\n");
System.out.println("connect - connect string for Oracle DB");
System.out.println("login - your login on oracle");
System.out.println("password - your password on oracle");
System.out.println("transaction1 - name of class containing increment transaction");
System.out.println("transaction2 - name of class containing decrement transaction");
System.out.println("trials - number of trials would like to test");
}
else
{
String vbose;
if(args.length < 7) vbose= "false";
else vbose=args[6];
Hw4ThreadTest hw4=
new Hw4ThreadTest(args[0],args[1],args[2],args[3],args[4],args[5], vbose);
hw4.doTrials();
}
}
public static final int halfTime=30; // roughly half the length of a transaction in mS
Hw4Thread thread1; // two threads used to generate random transaction for Oracle
Hw4Thread thread2;
String connectString; // string to connect to oracle host:port:dbname
String password; //password on Oracle
String login; // login on Oracle
double trials; // number of trnasaction that should occur in this test
int count = 0; // how many transactions have already occurred
boolean verbose; // is verbose mode for results on
/**
This class uses a threads to at random intervals execute a given
transaction on Oracle
*/
class Hw4Thread extends Thread
{
/**
*/
Hw4Thread(Hw4Transaction transaction)
{
this.transaction = transaction;
timeStamps = new ArrayList();
undone = true;
}
/**
Main code executed by thread. Repeats until enough transaction have
occurred the process of executing a transaction and sleep a random amount of time.
The start and stop time of a transaction's execution are recorded.
*/
public void run()
{
while(count < trials)
{
timeStamps.add("start"+System.currentTimeMillis());
transaction.execute();
timeStamps.add("stop"+System.currentTimeMillis());
incrementCount();
int random = new Random().nextInt(halfTime);
try
{
sleep(random);
if(verbose) System.out.println("Random"+random+"Thread"+this);
}
catch(InterruptedException ie)
{
ie.printStackTrace();
}
}
undone = false;
System.out.println("Thread done."+this);
}
/**
@return - whether this thread is done executing.
*/
public boolean undone()
{
return undone;
}
/**
@return - list of time stamps generated by this thread whever a transaction is
performed
*/
public ArrayList getTimeStamps()
{
return timeStamps;
}
boolean undone; // flag to check if thread is done
ArrayList timeStamps; // list of this threads time stamps
Hw4Transaction transaction; // transaction to be used by this thread
}
}
//HW4Transaction.java
import java.io.*;
import java.sql.*;
/**
This abstract class serve as a base class for all the JDBC and SQLJ transactions measured
in this project. Subclasses override execute to make their specific transaction.
*/
public abstract class Hw4Transaction
{
/**
Default constructor for this object. Actually, does nothing. All work done
in create method.
*/
Hw4Transaction()
{
}
/**
Sets up the database connection properties for this transaction
@param connectString - string to connect to Oracle host:port:dbname
@param login - user's login name on Oracle
@param password - user's password on Oracle
*/
void create(String connectString, String login, String password)
{
this.connectString = connectString;
this.login = login;
this.password = password;
}
/**
This method overriden in subclasses to acutal do the given transaction
*/
abstract void execute();
String connectString;
String password;
String login;
double trials;
}
//JDBCIncrementTransaction.java
import java.io.*;
import java.sql.*;
/**
This class implements the increment transaction as decribed in the HW4
description using JDBC.
The execute() method is the method that actually does the transaction
*/
public class JDBCIncrementTransaction extends Hw4Transaction
{
/**
Performs increment transaction as described in HW4
*/
void execute()
{
try
{
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
Connection conn =
DriverManager.getConnection(connectString, login, password);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT firstDigit FROM COUNTER");
rset.next();
int first = rset.getInt(1);
rset.close();
PreparedStatement pstmt = conn.prepareStatement(
"UPDATE COUNTER SET firstDigit = ?");
pstmt.setInt(1, ++first);
pstmt.executeUpdate();
pstmt.close();
if( first < 10) conn.commit();
else
{
stmt = conn.createStatement();
rset = stmt.executeQuery("SELECT secondDigit FROM COUNTER");
rset.next();
int second = rset.getInt(1);
rset.close();
if(second < 9)
{
pstmt = conn.prepareStatement(
"UPDATE COUNTER SET firstDigit = ?, secondDigit = ?");
pstmt.setInt(1, 0);
pstmt.setInt(2, ++second);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
}
else conn.rollback();
}
conn.close();
}
catch(Exception e)
{
System.err.println("Error setting row to fixed value");
e.printStackTrace();
}
}
}
//JDBCDecrementTransaction.java
import java.io.*;
import java.sql.*;
/**
This class implements the decrement transaction as decribed in the HW4
description using JDBC.
The execute() method is the method that actually does the transaction
*/
public class JDBCDecrementTransaction extends Hw4Transaction
{
/**
Performs decrement transaction as described in HW4
*/
void execute()
{
PreparedStatement pstmt;
try
{
DriverManager.registerDriver(
new oracle.jdbc.OracleDriver());
Connection conn =
DriverManager.getConnection(connectString, login, password);
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("SELECT firstDigit FROM COUNTER");
rset.next();
int first = rset.getInt(1);
rset.close();
if(first >0)
{
pstmt = conn.prepareStatement(
"UPDATE COUNTER SET firstDigit = ?");
pstmt.setInt(1, --first);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
}
else
{
stmt = conn.createStatement();
rset = stmt.executeQuery("SELECT secondDigit FROM COUNTER");
rset.next();
int second = rset.getInt(1);
rset.close();
if(second > 0)
{
pstmt = conn.prepareStatement(
"UPDATE COUNTER SET firstDigit = ?, secondDigit = ?");
pstmt.setInt(1, 9);
pstmt.setInt(2, --second);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
}
else conn.rollback();
}
conn.close();
}
catch(Exception e)
{
System.err.println("Error setting row to fixed value");
e.printStackTrace();
}
}
}
//SQLJIncrementTransaction.sqlj
import java.io.*;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;
/**
This class implements the increment transaction as decribed in the HW4
description using SQLJ.
The execute() method is the method that actually does the transaction
*/
public class SQLJIncrementTransaction extends Hw4Transaction
{
/**
Performs increment transaction as described in HW4
*/
void execute()
{
try
{
Oracle.connect(connectString, login, password, false);
int first;
#sql{ SELECT firstDigit INTO :first FROM COUNTER };
first++;
#sql{UPDATE COUNTER SET firstDigit = :first};
if( first < 10) #sql{ COMMIT RELEASE};
else
{
int second;
#sql{SELECT secondDigit INTO :second FROM COUNTER};
if(second < 9)
{
first = 0;
second++;
#sql{UPDATE COUNTER SET firstDigit = :first, secondDigit = :second};
#sql{ COMMIT RELEASE};
}
else #sql{ ROLLBACK RELEASE};
}
Oracle.close();
}
catch(Exception e)
{
System.err.println("Error setting row to fixed value");
e.printStackTrace();
}
}
}
//SQLJDecrementTransaction.sqlj
import java.io.*;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;
/**
This class implements the decrement transaction as decribed in the HW4
description using SQLJ.
The execute() method is the method that actually does the transaction
*/
public class SQLJDecrementTransaction extends Hw4Transaction
{
/**
Performs decrement transaction as described in HW4
*/
void execute()
{
try
{
Oracle.connect(connectString, login, password, false);
int first;
#sql{ SELECT firstDigit INTO :first FROM COUNTER };
if(first >0)
{
first--;
#sql{UPDATE COUNTER SET firstDigit = :first};
#sql{ COMMIT RELEASE};
}
else
{
int second;
#sql{SELECT secondDigit INTO :second FROM COUNTER};
if(second > 0)
{
first = 9;
second--;
#sql{UPDATE COUNTER SET firstDigit = :first, secondDigit = :second};
#sql{ COMMIT RELEASE};
}
else #sql{ ROLLBACK RELEASE};
}
Oracle.close();
}
catch(Exception e)
{
System.err.println("Error setting row to fixed value");
e.printStackTrace();
}
}
}
/*******************************************************
* Copyright (c): 2002, All Rights Reserved.
* Project: CS 157b Homework #4
* File: TransactionTest.h
* Purpose: This header contains the global and global
* embedded sql variables used by the
* test program for proc on this homework. (TransactionTest.pc)
* It also has the function prototypes for TransactionTest.pc
* Start date: 11/24/02
* Programmer: Chris Pollett
*
*******************************************************/
EXEC SQL BEGIN DECLARE SECTION;
varchar connectString[40]; /* SERVICE_NAME of Database (not host:port:dbname */
varchar login[20]; /* user's login on Oracle */
varchar password[20]; /* user's login on Oracle */
int first; /* firstDigit of COUNTER table*/
int second;/* secondDigit of COUNTER table*/
EXEC SQL END DECLARE SECTION;
int trials; /* number of trials to do to calculate average time */
void initTransaction(char* connect, char* uname, char* pword, char *numTrials);
/* sets up the info for the dbconnection as well number of trials */
void doTrials(); /* performs all the proc test for this homework*/
void trial(int firstDigit, int secondDigit);/*computes avg time from given start row value*/
void execute(); /*function call to run transaction*/
void setRow(int first, int second); /*set COUNTER's value to the given value */
/*******************************************************
* Copyright (c): 2002, All Rights Reserved.
* Project: CS 157b Homework #4
* File: Transaction.h
* Purpose: This header contains the external embedded sql variables
* used by a proc transaction in this homework. (Either IncrementTransaction
* or DecrementTransaction)
* Start date: 11/24/02
* Programmer: Chris Pollett
*
*******************************************************/
EXEC SQL BEGIN DECLARE SECTION;
extern varchar connectString[40]; /* SID of Database (not host:port:dbname */
extern varchar login[20]; /* user's login on Oracle */
extern varchar password[20]; /* user's login on Oracle */
extern int first; /* firstDigit of COUNTER table*/
extern int second;/* secondDigit of COUNTER table*/
EXEC SQL END DECLARE SECTION;
/*******************************************************
* Copyright (c): 2002, All Rights Reserved.
* Project: CS 157b Homework #4
* File: TransactionTest.pc
* Purpose: This is the main source file to perform the proc tests for HW4
* Start date: 11/24/02
* Programmer: Chris Pollett
*
*******************************************************/
#include<stdio.h>
#include<stdlib.h>
#include<time.h>
EXEC SQL include "TransactionTest.h";
#include<sqlca.h>
/*-----------------------------------------------*/
void sql_error()
/*
PURPOSE: called whenever there is a Embedded SQL error to print the error message
RECEIVES: nothing
RETURNS: nothing
REMARKS: NONE
*/
{
char error[512];
long buflen;
long msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buflen = sizeof(error);
sqlglm(error,&buflen,&msglen);
printf("Oracle Error:\t%25i\n", sqlca.sqlcode);
printf("%.*s\n", msglen, error);
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
/*-----------------------------------------------*/
void initTransaction(char* connect, char* lgn, char* pword, char* numTrials)
/*
PURPOSE: sets up embedded sql global variabled used to connect to DB
RECEIVES: connect -- SERVICE_NAME of oracle DB (not host:port:dbname)
lgn -- login
pword -- password
numTrials -- number of time transaction run to get avg time
RETURNS: nothing
REMARKS: NONE
*/
{
strcpy(connectString.arr, connect);
connectString.len = strlen(connectString.arr);
strcpy(login.arr, lgn);
login.len = strlen(login.arr);
strcpy(password.arr, pword);
password.len = strlen(password.arr);
trials = atoi(numTrials);
if(trials == 0)
{
trials = 1;
printf("We need to perform at least one trial\n");
}
}
void doTrials()
/*
PURPOSE: Tests how long the transaction takes for various starting row values
Outputs the results to the screen.
RECEIVES: nothing
RETURNS: nothing
REMARKS: NONE
*/
{
/* Would be better to read from a file */
trial(0,0);
trial(9,0);
trial(0,1);
trial(9,9);
}
/*-----------------------------------------------*/
void setRow(int f, int s)
/*
PURPOSE: sets the ORACLE table Counter to the given value
RECEIVES: f - firstDigit value for table
s - secondDigit value for table
RETURNS: nothing
REMARKS: NONE
*/
{
EXEC SQL WHENEVER SQLERROR DO sql_error();
first = f;
second = s;
EXEC SQL CONNECT :login IDENTIFIED BY :password USING :connectString;
EXEC SQL UPDATE COUNTER SET firstDigit = :first , secondDigit = :second;
EXEC SQL COMMIT RELEASE;
}
/*-----------------------------------------------*/
void trial(int firstDigit, int secondDigit)
/*
PURPOSE: used to calculate the average time to perform the given transaction
based on the given sgtrating row value
RECEIVES: firstDigit and secondDigit of COUNTER's starting row value
RETURNS: nothing
REMARKS: NONE
*/
{
/*Time setRow operation*/
clock_t startTime = clock();
clock_t deltaRowTime;
long totalTimeTrials;
int i;
for(i=0; i < trials; i++)
{
setRow(firstDigit, secondDigit);
}
deltaRowTime = clock()-startTime;
startTime = clock();
for(i=0; i< trials; i++)
{
setRow(firstDigit, secondDigit);
execute();
}
totalTimeTrials =
clock() - startTime - deltaRowTime;
printf("Avg. time to update when row value (%d, %d) in ms : %d\n",
firstDigit, secondDigit, totalTimeTrials/(trials));
}
/*-----------------------------------------------*/
void main(int argc, char *argv[])
/*
PURPOSE: main driver function to perform tests of a transaction
RECEIVES: command line arguments saying how to connect and number of trials
RETURNS: nothing
REMARKS: NONE
*/
{
if(argc < 5)
{
printf("This program should be run with a line like:\n\n");
printf("[TransactionTest] connect login password ");
printf("trials\n\nwhere\n\n");
printf("[TransactionTest] - name of this program\n");
printf("service_name - service name for Oracle DB\n");
printf("login - your login on oracle\n");
printf("password - your password on oracle\n");
printf("trials - number of trials would like to test\n");
}
else
{
initTransaction(argv[1],argv[2],argv[3],argv[4]);
doTrials();
}
}
/*******************************************************
* Copyright (c): 2002, All Rights Reserved.
* Project: CS 157b Homework #4
* File: IncrementTransaction.pc
* Purpose: This is the source file to implement the proc version of
* version of the increment transaction as described in HW4
* Start date: 11/24/02
* Programmer: Chris Pollett
*
*******************************************************/
EXEC SQL INCLUDE "Transaction.h";
#include<sqlca.h>
/*-----------------------------------------------*/
void execute()
/*
PURPOSE: carries out the decrement transaction of HW4
RECEIVES: nothing
RETURNS: nothing
REMARKS: NONE
*/
{
EXEC SQL CONNECT :login IDENTIFIED BY :password USING :connectString;
EXEC SQL SELECT firstDigit INTO :first FROM COUNTER;
first++;
EXEC SQL UPDATE COUNTER SET firstDigit = :first;
if( first < 10) EXEC SQL COMMIT RELEASE;
else
{
EXEC SQL SELECT secondDigit INTO :second FROM COUNTER;
if(second < 9)
{
first = 0;
second++;
EXEC SQL UPDATE COUNTER SET firstDigit = :first, secondDigit = :second;
EXEC SQL COMMIT RELEASE;
}
else EXEC SQL ROLLBACK RELEASE;
}
}
/*******************************************************
* Copyright (c): 2002, All Rights Reserved.
* Project: CS 157b Homework #4
* File: DecrementTransaction.pc
* Purpose: This is the source file to implement the proc version of
* version of the decrement transaction as described in HW4
* Start date: 11/24/02
* Programmer: Chris Pollett
*
*******************************************************/
EXEC SQL INCLUDE "Transaction.h";
#include<sqlca.h>
/*-----------------------------------------------*/
void execute()
/*
PURPOSE: carries out the decrement transaction of HW4
RECEIVES: nothing
RETURNS: nothing
REMARKS: NONE
*/
{
EXEC SQL CONNECT :login IDENTIFIED BY :password USING :connectString;
EXEC SQL SELECT firstDigit INTO :first FROM COUNTER;
if(first > 0)
{
first--;
EXEC SQL UPDATE COUNTER SET firstDigit = :first;
EXEC SQL COMMIT RELEASE;
}
else
{
EXEC SQL SELECT secondDigit INTO :second FROM COUNTER;
if(second == 0)
EXEC SQL ROLLBACK RELEASE;
else
{
first = 9;
second --;
EXEC SQL UPDATE COUNTER SET firstDigit = :first, secondDigit = :second;
EXEC SQL COMMIT RELEASE;
}
}
}
|