hi Irum
did you see
http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200501.mbox/[EMAIL
PROTECTED]
it's about hsql isolation (and derby)
gr
wim
search on google with hsqldb isolation
On Wed, 30 Mar 2005 12:24:14 -0800 (PST), Irum Godil
<[EMAIL PROTECTED]> wrote:
> 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
-------------------------------------------------------
This SF.net email is sponsored by Demarc:
A global provider of Threat Management Solutions.
Download our HomeAdmin security software for free today!
http://www.demarc.com/info/Sentarus/hamr30
_______________________________________________
hsqldb-developers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
