----- Original Message -----
Sent: 30 March 2005 21:24
Subject: [Hsqldb-developers] isolation
support in HSQL 1.8.0 version
Hi,
I downloaded HSQL version 1.8.0 from the website and wrote a small test
that has 2 connection variables to the same database. I wanted to see how is
locking for isolation support working.
Both connections update the same row of the database. Unfortunately, this
is allowed in the system, and I would expect that the second connections will
not be able to do so, given the locking support. Following is my test and its
results. Please see the bolded red lines where I am updating the 2 rows.
Also, when I run the test in version 1.7.3 I get the same results. Can
someone please tell me if I have written something wrong, or if my locking
understanding is incorrect. I will really appreciate your response.
public class IsolationIrumTest {
Connection conn1;
Connection
conn2;
//our connnection to the db - presist for life of
program
// we dont want this garbage collected until we are
done
public IsolationIrumTest(String
db_file_name_prefix) throws Exception { // note more general
exception
// Load the HSQL Database Engine
JDBC driver
// hsqldb.jar should
be in the class path or made part of the current
jar
Class.forName("org.hsqldb.jdbcDriver");
conn1 =
DriverManager.getConnection("jdbc:hsqldb:"
+ db_file_name_prefix, //
filenames
"sa",
//
username
"");
conn2 =
DriverManager.getConnection("jdbc:hsqldb:"
+ db_file_name_prefix, //
filenames
"sa",
//
username
"");
conn1.setAutoCommit(false);
conn2.setAutoCommit(false);
}
public void shutdown() throws SQLException {
update1("SHUTDOWN");
conn1.close(); // if there are no other open
connection
conn2.close();
//
}
public synchronized void query1(String _expression_)
throws SQLException {
Statement st =
null;
ResultSet rs = null;
st =
conn1.createStatement(); //
statement objects can be reused with
// repeated calls to execute but
we
// choose to make a new one
each time
rs =
st.executeQuery(_expression_); // run the query
// do something with the result
set.
dump(rs);
st.close(); // NOTE!! if you close a statement the
associated ResultSet is
// closed
too
// so you should copy the
contents to some other object.
// the result set is invalidated also if you recycle an
Statement
// and try to execute
some other query before the result set has
been
// completely
examined.
}
public synchronized void query2(String _expression_)
throws SQLException {
Statement st =
null;
ResultSet rs = null;
st =
conn2.createStatement(); //
statement objects can be reused with
// repeated calls to execute but
we
// choose to make a new one
each time
rs =
st.executeQuery(_expression_); // run the query
// do something with the result
set.
dump(rs);
st.close(); // NOTE!! if you close a statement the
associated ResultSet is
}
public synchronized void update1(String _expression_)
throws SQLException {
Statement st = null;
st =
conn1.createStatement(); // statements
int i =
st.executeUpdate(_expression_); // run the query
if (i == -1)
{
System.out.println("db error : " +
_expression_);
}
st.close();
} // void update()
public
synchronized void update2(String _expression_) throws SQLException {
Statement st = null;
st =
conn2.createStatement(); // statements
int i =
st.executeUpdate(_expression_); // run the query
if (i == -1)
{
System.out.println("db error : " +
_expression_);
}
st.close();
} // void update()
public static void dump(ResultSet rs) throws
SQLException {
// the order of the rows in a
cursor
// are implementation
dependent unless you use the SQL ORDER
statement
ResultSetMetaData
meta =
rs.getMetaData();
int
colmax = meta.getColumnCount();
int
i;
Object o =
null;
// the result set is a cursor
into the data. You can
only
// point to one row at a
time
// assume we are pointing
to BEFORE the first row
//
rs.next() points to next row and returns
true
// or false if there is no
next row, which breaks the loop
for (; rs.next(); )
{
for (i
= 0; i < colmax; ++i)
{
o = rs.getObject(i + 1); // Is SQL the first column is
indexed
// with 1 not
0
System.out.print(o.toString() + "
");
}
System.out.println(" ");
}
}
//void dump( ResultSet rs )
public static void main(String[] args) {
IsolationIrumTest db = null;
try
{
db =
new
IsolationIrumTest("db_file");
}
catch (Exception ex1)
{
ex1.printStackTrace(); // could not start db
return;
// bye bye
}
try {
//FROM CONNECTION 1
db.update1(
"CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256),
num_col INTEGER, num_col2
INTEGER)");
} catch
(SQLException ex2) {
//ignore
//ex2.printStackTrace(); // second time we run
program
// should throw execption since
table
//
already
there
//
//
this will have no effect on the
db
}
try {
// add
some rows - will create duplicates if run more then
once
//
the id column is automatically
generated
db.update1(
"INSERT INTO sample_table(str_col,num_col, num_col2) VALUES('Ford', 100,
50)");
// do a
query
db.query1("SELECT * FROM
sample_table");
//NOW
UPDATE THROUGH
CONNECTION1
db.update1("UPDATE sample_table SET num_col = '34' WHERE
str_col = 'Ford'");
System.out.println("From connection1:
");
db.query1("SELECT * FROM
sample_table");
//now
update throught
connection2.
db.update2("UPDATE sample_table SET num_col = '36'
WHERE str_col =
'Ford'");
System.out.println("From connection2:
");
db.query2("SELECT * FROM
sample_table");
// at end of
program
db.shutdown();
} catch
(SQLException ex3)
{
ex3.printStackTrace();
}
} // main()
}
// class Testdb
My Test Results are:
0 Ford 100 50
From connection1:
0 Ford 34 50
From connection2:
0 Ford 36 50
Thanks a lot.
Sincerely,
Irum Godil.
__________________________________________________
Do You
Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
