HyperSQL Database Engine (HSQLDB)

This looks more like a code review :)

The following two commands are executed as nested transactions:

INSERT INTO ... SELECT... FROM ..
UPDATE ...

The reason INSERT and DELETE are not, is because adequate checks for
referential integrity and NOT NULL constraints could be performed without
inserting or deleting the rows. With the above commands, however, those
checks could not be performed without actually performing the action.

Since that time, changes have been made to the implementation of the UPDATE
command and recently CHECK constraints have been introduced. I think, UPDATE
now performs the checks before performing the action and , with some lines
of code, the required checks for INSERT INTO ... SELECT ... FROM can be made
before insertion.

The difference between nested transaction and others should be obvious. If
autocommit is false, you want the last UPDATE to roll back if it is not
successful, but you don't want the previously executed commands to roll
back.

I don't see what good it would do to group the UPDATE nested transaction
commands. These commands are rolled back immediately if the action is not
successful. Otherwise, they become just ordinary members of the transaction
list.

You can think of nested transaction as a SAVEPOINT to which we immediately
roll back if the command is not successful. It might be an idea to use the
internal SAVEPOINT semantics for any nested transactions (if we find out we
still need them)

In any case, with a multi-version concurrency scheme, transaction control
would be done differently and we may not have memory resident lists such as
tTransaction.

Re visibility of Database, etc. All the HSQLDB core is in one package to
make life simple. Only those classes that are accessed from outside are
Public. All the rest are package-private. If your changes need some classes
to become public, then it should be fine to change the visibility.

Fred

----- Original Message -----
From: "Daniel Schaller" <daniel@...>
To: "HSQL Developer" <hsqldb-developers@...>
Cc: <alex@...>
Sent: 20 January 2004 21:41
Subject: [Hsqldb-developers] Nested transactions not grouped in a parent
transaction


Hi,

just found out the following:

A delete or a insert command, which effect more then one tuple/row, is
transformed in several inserts or deletes on the table/index.

Each of theses inserts/deletes is put in a own transaction object to
perform a rollback if necessary. The flag 'isNested' is false in these
transaction (tx) objects. Theses tx are added to the list 'tTransaction'
of the current session.

If you perfom an update, which usually results in several deletes and
inserts on the table/index, there will be tx objects created too. However,
in this case, the flag 'isNested' is set to true. Finally, the tx objects
are added to the list 'tTransaction' in the current session to.

My questions:

- What's the difference between these tx objects.

- Why are not the tx objects with 'isNested == true' grouped in a parent
transaction which represents the Update command. I think, it would be a
good idea, wouldn't it?

Regards,

Daniel

P.S. Some classes in org.hsqldb have no visbility modifier like private or
public, e.g. Database, Transaction.
Is there a reason for it or just forgotten? Anyway, I will and have to
change it for my modifications.



-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

On Tue, 20 Jan 2004 22:59:34 -0000, fredt <fredt@...>  
wrote with possible deletions:

> This looks more like a code review :)

well, I think it's the only way to get used to ;-)


> The difference between nested transaction and others should be obvious.  
> If
> autocommit is false, you want the last UPDATE to roll back if it is not
> successful, but you don't want the previously executed commands to roll
> back.

> I don't see what good it would do to group the UPDATE nested transaction
> commands. These commands are rolled back immediately if the action is not
> successful. Otherwise, they become just ordinary members of the  
> transaction
> list.
>
> You can think of nested transaction as a SAVEPOINT to which we  
> immediately
> roll back if the command is not successful. It might be an idea to use  
> the
> internal SAVEPOINT semantics for any nested transactions (if we find out  
> we
> still need them)

ok, I just realized that we have/had a different understanding of a  
transaction and so on ;-)
In HSQLDB each operation on the table/index is encapsulated in a tx object.
In my understanding a transaction is a putting together of several  
table/index operations, that means everything between the 'set autocommit  
false' or last 'rollback/commit' command and a further 'commit/rollback'  
command. This is realized as the 'tTransaction' attribute in the Session  
class. So, I understand now. I hope, others do undstand it as well ;-)


Even more, the approach to encapsulate each table/index operation in a  
serveral transaction object is quite nice. It's useful to implement FOCC.  
Even though there are some modifications necessary to build up to proper  
Read-Sets (RS) and Write-Sets (WS) of each transaction - transaction in my  
understanding.

> In any case, with a multi-version concurrency scheme, transaction control
> would be done differently and we may not have memory resident lists such  
> as
> tTransaction.

tTransaction would get a new meaning in the context of MVC with FOCC, s.a.  
RS and WS

> Re visibility of Database, etc. All the HSQLDB core is in one package to
> make life simple. Only those classes that are accessed from outside are
> Public. All the rest are package-private. If your changes need some  
> classes
> to become public, then it should be fine to change the visibility.

Well, I don't want to change to much original code. So all new  
functionality will be implemented in their own packages.

I also probably would like to put all classes beloinging to the JDBC  
driver in a own package. The org.hsqldb package is a bit crammed full at  
the moment for my taste. ;-) (Again, no critism, just my opinion)

Regards,

Daniel

There is no major difference between HSQLDB's understanding of a transaction
and the general use of this. As you say a transaction is basically what
happens between two ROLLBACK/COMMIT commands. Obviously different
implementation will need different internal data structures and these should
be named appropriately.

Re packaging, it might be a good idea to change things at some point in the
future, but any publicly visible change would have an effect on the programs
that use HSQLDB. For example, putting the JDBC classes in a different
package would mean changing the connection code. You are free to put your
additions in a new Java package.

Fred


----- Original Message -----
From: "Daniel Schaller" <daniel@...>
To: "fredt" <fredt@...>; "HSQL Developer"
<hsqldb-developers@...>
Cc: <alex@...>
Sent: 21 January 2004 08:29
Subject: Re: [Hsqldb-developers] Nested transactions not grouped in a parent
transaction


On Tue, 20 Jan 2004 22:59:34 -0000, fredt <fredt@...>
wrote with possible deletions:

> This looks more like a code review :)

well, I think it's the only way to get used to ;-)


> The difference between nested transaction and others should be obvious.
> If
> autocommit is false, you want the last UPDATE to roll back if it is not
> successful, but you don't want the previously executed commands to roll
> back.

> I don't see what good it would do to group the UPDATE nested transaction
> commands. These commands are rolled back immediately if the action is not
> successful. Otherwise, they become just ordinary members of the
> transaction
> list.
>
> You can think of nested transaction as a SAVEPOINT to which we
> immediately
> roll back if the command is not successful. It might be an idea to use
> the
> internal SAVEPOINT semantics for any nested transactions (if we find out
> we
> still need them)

ok, I just realized that we have/had a different understanding of a
transaction and so on ;-)
In HSQLDB each operation on the table/index is encapsulated in a tx object.
In my understanding a transaction is a putting together of several
table/index operations, that means everything between the 'set autocommit
false' or last 'rollback/commit' command and a further 'commit/rollback'
command. This is realized as the 'tTransaction' attribute in the Session
class. So, I understand now. I hope, others do undstand it as well ;-)


Even more, the approach to encapsulate each table/index operation in a
serveral transaction object is quite nice. It's useful to implement FOCC.
Even though there are some modifications necessary to build up to proper
Read-Sets (RS) and Write-Sets (WS) of each transaction - transaction in my
understanding.

> In any case, with a multi-version concurrency scheme, transaction control
> would be done differently and we may not have memory resident lists such
> as
> tTransaction.

tTransaction would get a new meaning in the context of MVC with FOCC, s.a.
RS and WS

> Re visibility of Database, etc. All the HSQLDB core is in one package to
> make life simple. Only those classes that are accessed from outside are
> Public. All the rest are package-private. If your changes need some
> classes
> to become public, then it should be fine to change the visibility.

Well, I don't want to change to much original code. So all new
functionality will be implemented in their own packages.

I also probably would like to put all classes beloinging to the JDBC
driver in a own package. The org.hsqldb package is a bit crammed full at
the moment for my taste. ;-) (Again, no critism, just my opinion)

Regards,

Daniel


-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

On Wed, 21 Jan 2004 09:34:15 -0000, fredt <fredt@...>  
wrote with possible deletions:


Thanks Fred.

> There is no major difference between HSQLDB's understanding of a  
> transaction
> and the general use of this. As you say a transaction is basically what
> happens between two ROLLBACK/COMMIT commands. Obviously different
> implementation will need different internal data structures and these  
> should
> be named appropriately.

Ok, will try to make the best of all and when I have hopefully implemented  
the first step, the FOCC, I will post, what I have done (renaming, new  
things and so on)

> Re packaging, it might be a good idea to change things at some point in  
> the
> future, but any publicly visible change would have an effect on the  
> programs
> that use HSQLDB. For example, putting the JDBC classes in a different
> package would mean changing the connection code. You are free to put your
> additions in a new Java package.

Ok, that's right and always a big problem/issue when making a big changes  
in a widespread system ;-)

Have a nice day!

Daniel

I have tried everything to get hibernate to release the lock. No luck.
Trying to do it forcefully just gives an access denied error.

Any ideas at all how I can get past this..

The way it tands now I have to restart tomcat evertime I open a HSQL
connection.

Obviously a huge issue.

If I can't get past this soon I will have to look to an alternative for HSQL
or just not use Hibernate and do direct JDBC for this and map the objects
manually!

Hoepfully I can resolve this...

Troy



> I can't get Hibernate to disconnect from the database once it is
connected.
> It creates a HSQLDB.lck file and I have to terminate the Tomcat before I
can
> access it again.
>
> I have tried: (With no luck..)
>
>
>   try {
>             Configuration cfg = new
> Configuration().configure("/hibernate-HSQLDB.cfg.xml");
>             SessionFactory sessions = cfg.buildSessionFactory();
>             Long user = new Long(0);
>             AuditInterceptor aii = new AuditInterceptor(user);
>             session = sessions.openSession(aii);
>             sw.start();
>             list = session.find("from " + table + " in class " +
> StringUtils.capitalise(table));
>             // Edited by tmckinnon: 20-Jan-2004 03:56:27 PM PST
>             // trying to get HSQLDB lck to release...
>             if(session != null && session.isOpen()) {
>                 Connection con = session.connection();
>                 Statement st = con.createStatement();
>                 st.execute("SHUTDOWN COMPACT");
>                 con.commit();
>                 st.close();
>                 con.close();
>                 logger.trace("Shut down HSQLDB session!");
>                 session.disconnect();
>                 if(session != null) session.close();
>             }
>              hibernate.push(list.toArray(new Object[list.size()]), clazz);
>         } catch(Exception e) {
>             throw e;
>         } finally {
>             try {
>                 if(session != null && session.isOpen()) {
>                     logger.trace("Closing CSV session!");
>                     session.disconnect();
>                     if(session!=null) session.close();
>                 }
>             } catch(Exception e) {
>                 logger.trace("Error trying to close session");
>                 throw e;
>             }
>         }
>
>
>
>
>
> but just get:
>
> 16:20:06,032  WARN [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:38 - SQL Error: -33, SQLState:
> null
> 16:20:06,032 ERROR [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:46 - Access is denied in
> statement [SHUTDOWN COMPACT]
> 16:20:06,042 ERROR [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:38 - Cannot close connection
> java.sql.SQLException: Access is denied in statement [SHUTDOWN COMPACT]
>  at org.hsqldb.jdbcResultSet.<init>(Unknown Source)
>  at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source)
>  at org.hsqldb.jdbcConnection.execute(Unknown Source)
>  at org.hsqldb.jdbcConnection.rollback(Unknown Source)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection.reset(C3P0PooledConnection.jav
> a:251)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection.access$000(C3P0PooledConnectio
> n.java:35)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandl
> er.doSilentClose(C3P0PooledConnection.java:531)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandl
> er.invoke(C3P0PooledConnection.java:636)
>  at com.mchange.v2.c3p0.impl.$Proxy0.close(Unknown Source)
>  at
>
net.sf.hibernate.connection.C3P0ConnectionProvider.closeConnection(C3P0Conne
> ctionProvider.java:40)
>  at
net.sf.hibernate.impl.BatcherImpl.closeConnection(BatcherImpl.java:275)
>  at net.sf.hibernate.impl.SessionImpl.disconnect(SessionImpl.java:3299)
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> hibernate-devel mailing list
> hibernate-devel@...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel
>

I will treat this as a feature request. In 1.7.2 the lifetime of an
in-process database is infinite (untill an explicit SHUTDOWN command sent
via SQL). In previous versions, the database was released when the last
connection was closed (and caused other problems). We may be able to add a
connection property to use the old behaviour if specified.

Thanks for your report. I don't think your subscription has gone through as
your emails are still blocked. Please try again.

Fred



----- Original Message -----
From: "Troy McKinnon" <TroyMcKinnon@...>
To: "Troy McKinnon" <TroyMcKinnon@...>;
<hibernate-devel@...>; "fredt"
<fredt@...>; <hsqldb-developers@...>
Sent: 21 January 2004 07:18
Subject: Hibernate Spring and HSQLDB - locking issue


I have tried everything to get hibernate to release the lock. No luck.
Trying to do it forcefully just gives an access denied error.

Any ideas at all how I can get past this..

The way it tands now I have to restart tomcat evertime I open a HSQL
connection.

Obviously a huge issue.

If I can't get past this soon I will have to look to an alternative for HSQL
or just not use Hibernate and do direct JDBC for this and map the objects
manually!

Hoepfully I can resolve this...

Troy



> I can't get Hibernate to disconnect from the database once it is
connected.
> It creates a HSQLDB.lck file and I have to terminate the Tomcat before I
can
> access it again.
>
> I have tried: (With no luck..)
>
>
>   try {
>             Configuration cfg = new
> Configuration().configure("/hibernate-HSQLDB.cfg.xml");
>             SessionFactory sessions = cfg.buildSessionFactory();
>             Long user = new Long(0);
>             AuditInterceptor aii = new AuditInterceptor(user);
>             session = sessions.openSession(aii);
>             sw.start();
>             list = session.find("from " + table + " in class " +
> StringUtils.capitalise(table));
>             // Edited by tmckinnon: 20-Jan-2004 03:56:27 PM PST
>             // trying to get HSQLDB lck to release...
>             if(session != null && session.isOpen()) {
>                 Connection con = session.connection();
>                 Statement st = con.createStatement();
>                 st.execute("SHUTDOWN COMPACT");
>                 con.commit();
>                 st.close();
>                 con.close();
>                 logger.trace("Shut down HSQLDB session!");
>                 session.disconnect();
>                 if(session != null) session.close();
>             }
>              hibernate.push(list.toArray(new Object[list.size()]), clazz);
>         } catch(Exception e) {
>             throw e;
>         } finally {
>             try {
>                 if(session != null && session.isOpen()) {
>                     logger.trace("Closing CSV session!");
>                     session.disconnect();
>                     if(session!=null) session.close();
>                 }
>             } catch(Exception e) {
>                 logger.trace("Error trying to close session");
>                 throw e;
>             }
>         }
>
>
>
>
>
> but just get:
>
> 16:20:06,032  WARN [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:38 - SQL Error: -33, SQLState:
> null
> 16:20:06,032 ERROR [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:46 - Access is denied in
> statement [SHUTDOWN COMPACT]
> 16:20:06,042 ERROR [http8100-Processor4]
> net.sf.hibernate.util.JDBCExceptionReporter:38 - Cannot close connection
> java.sql.SQLException: Access is denied in statement [SHUTDOWN COMPACT]
>  at org.hsqldb.jdbcResultSet.<init>(Unknown Source)
>  at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source)
>  at org.hsqldb.jdbcConnection.execute(Unknown Source)
>  at org.hsqldb.jdbcConnection.rollback(Unknown Source)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection.reset(C3P0PooledConnection.jav
> a:251)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection.access$000(C3P0PooledConnectio
> n.java:35)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandl
> er.doSilentClose(C3P0PooledConnection.java:531)
>  at
>
com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandl
> er.invoke(C3P0PooledConnection.java:636)
>  at com.mchange.v2.c3p0.impl.$Proxy0.close(Unknown Source)
>  at
>
net.sf.hibernate.connection.C3P0ConnectionProvider.closeConnection(C3P0Conne
> ctionProvider.java:40)
>  at
net.sf.hibernate.impl.BatcherImpl.closeConnection(BatcherImpl.java:275)
>  at net.sf.hibernate.impl.SessionImpl.disconnect(SessionImpl.java:3299)
>
>
> -------------------------------------------------------
> The SF.Net email is sponsored by EclipseCon 2004
> Premiere Conference on Open Tools Development and Integration
> See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
> http://www.eclipsecon.org/osdn
> _______________________________________________
> hibernate-devel mailing list
> hibernate-devel@...
> https://lists.sourceforge.net/lists/listinfo/hibernate-devel
>

The .lck file prevents two processes from simultaneously opening the =
database file. It stays there until you shutdown the database.  If it is =
causing a problem, please state what it is.

Fred
  ----- Original Message -----=20
  From: Troy McKinnon=20
  To: hsqldb-developers@...
  Cc: Troy McKinnon=20
  Sent: 20 January 2004 22:12
  Subject: [Hsqldb-developers] HSQLDB.lck file issues


  I am having trouble using HSQLDB using CSV type files.  Running it in =
tomcat using Hibernate... as soon as the context starts up the lock file =
appears and it doesn't ever go away.  What exactly creates the lck file =
and how can I get by it?

  Troy

My issue is basically this...

I am using the FILE version of HSQLDB to access CVS files so I can =
import them into my (Oracle /or/ Postgres) database.

I manually create these files with one process and using the HSQL driver =
and Hibernate to access the data and take care of all the Dirty Work for =
me.

Works very well stand alone.  But when I tried to incorporate it into my =
Tomcat/Spring/Hibernate framework, it just locks up (HSQLDB.lck).

I discovered this is because I created a HSQLHibernateSessionFactory =
bean using the HSQLDataSource in my application-context.xml.

On startup Spring must load these and lock them. It doesn't release them =
again until tomcat is terminated.

I removed the references from my application-context.xml and used a =
straight forward hibernate-HSQLDB.cfg.xml file configuration.  Basically =
skipped spring all together.. and it works fine.

I would like to utilize the spring framework.. is there a better way to =
do this?  I don't want to run SERVER mode on HSQL since they are =
basically only temporary import and export files.


What would you suggest?

Cheers

Troy


  ----- Original Message -----=20
  From: fredt=20
  To: Troy McKinnon ; hsqldb-developers@...
  Cc: Troy McKinnon=20
  Sent: Tuesday, January 20, 2004 3:02 PM
  Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


  The .lck file prevents two processes from simultaneously opening the =
database file. It stays there until you shutdown the database.  If it is =
causing a problem, please state what it is.

  Fred
    ----- Original Message -----=20
    From: Troy McKinnon=20
    To: hsqldb-developers@...
    Cc: Troy McKinnon=20
    Sent: 20 January 2004 22:12
    Subject: [Hsqldb-developers] HSQLDB.lck file issues


    I am having trouble using HSQLDB using CSV type files.  Running it =
in tomcat using Hibernate... as soon as the context starts up the lock =
file appears and it doesn't ever go away.  What exactly creates the lck =
file and how can I get by it?

    Troy

As far as HSQLDB is concerned, an in-process or server instance that =
opens a database, creates the *.lck file and does not release it until =
the SHUTDOWN command has been issued on the database. You should aim at =
incorporating the SHUTDOWN into your framework when you no longer need =
the connection to that particular database.

If you are using SHUTDOWN and it does not release the *.lck then please =
let us know. I don't know about the Sprint framework (reference please).

Please subscribe to the hsqldb-developers if you want to post messages =
here.

Fred


  ----- Original Message -----=20
  From: Troy McKinnon=20
  To: fredt ; hsqldb-developers@...
  Sent: 20 January 2004 23:11
  Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


  My issue is basically this...

  I am using the FILE version of HSQLDB to access CVS files so I can =
import them into my (Oracle /or/ Postgres) database.

  I manually create these files with one process and using the HSQL =
driver and Hibernate to access the data and take care of all the Dirty =
Work for me.

  Works very well stand alone.  But when I tried to incorporate it into =
my Tomcat/Spring/Hibernate framework, it just locks up (HSQLDB.lck).

  I discovered this is because I created a HSQLHibernateSessionFactory =
bean using the HSQLDataSource in my application-context.xml.

  On startup Spring must load these and lock them. It doesn't release =
them again until tomcat is terminated.

  I removed the references from my application-context.xml and used a =
straight forward hibernate-HSQLDB.cfg.xml file configuration.  Basically =
skipped spring all together.. and it works fine.

  I would like to utilize the spring framework.. is there a better way =
to do this?  I don't want to run SERVER mode on HSQL since they are =
basically only temporary import and export files.


  What would you suggest?

  Cheers

  Troy


    ----- Original Message -----=20
    From: fredt=20
    To: Troy McKinnon ; hsqldb-developers@...
    Cc: Troy McKinnon=20
    Sent: Tuesday, January 20, 2004 3:02 PM
    Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


    The .lck file prevents two processes from simultaneously opening the =
database file. It stays there until you shutdown the database.  If it is =
causing a problem, please state what it is.

    Fred
      ----- Original Message -----=20
      From: Troy McKinnon=20
      To: hsqldb-developers@...
      Cc: Troy McKinnon=20
      Sent: 20 January 2004 22:12
      Subject: [Hsqldb-developers] HSQLDB.lck file issues


      I am having trouble using HSQLDB using CSV type files.  Running it =
in tomcat using Hibernate... as soon as the context starts up the lock =
file appears and it doesn't ever go away.  What exactly creates the lck =
file and how can I get by it?

      Troy

Note: I posted this to springframework forum as well since it is =
applicable to both.

The springframework is an open source IoC MVC framework. It also allows =
you to incorportate Hibernate framework, easily, for the model side of =
things.

http://www.springframework.org/docs/reference/index.html

As far as my question below.  I am not familiar with the SHUTDOWN. How =
would I do that?

But as far as with the Spring Framework.  I don't really have control of =
that as far as I know.  The framework is locking the database at server =
startup, because I have HSQLDB datasource in my application-context.xml.

  ----- Original Message -----=20
  From: fredt=20
  To: Troy McKinnon ; hsqldb-developers@...
  Sent: Tuesday, January 20, 2004 3:31 PM
  Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


  As far as HSQLDB is concerned, an in-process or server instance that =
opens a database, creates the *.lck file and does not release it until =
the SHUTDOWN command has been issued on the database. You should aim at =
incorporating the SHUTDOWN into your framework when you no longer need =
the connection to that particular database.

  If you are using SHUTDOWN and it does not release the *.lck then =
please let us know. I don't know about the Sprint framework (reference =
please).

  Please subscribe to the hsqldb-developers if you want to post messages =
here.

  Fred


    ----- Original Message -----=20
    From: Troy McKinnon=20
    To: fredt ; hsqldb-developers@...
    Sent: 20 January 2004 23:11
    Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


    My issue is basically this...

    I am using the FILE version of HSQLDB to access CVS files so I can =
import them into my (Oracle /or/ Postgres) database.

    I manually create these files with one process and using the HSQL =
driver and Hibernate to access the data and take care of all the Dirty =
Work for me.

    Works very well stand alone.  But when I tried to incorporate it =
into my Tomcat/Spring/Hibernate framework, it just locks up =
(HSQLDB.lck).

    I discovered this is because I created a HSQLHibernateSessionFactory =
bean using the HSQLDataSource in my application-context.xml.

    On startup Spring must load these and lock them. It doesn't release =
them again until tomcat is terminated.

    I removed the references from my application-context.xml and used a =
straight forward hibernate-HSQLDB.cfg.xml file configuration.  Basically =
skipped spring all together.. and it works fine.

    I would like to utilize the spring framework.. is there a better way =
to do this?  I don't want to run SERVER mode on HSQL since they are =
basically only temporary import and export files.


    What would you suggest?

    Cheers

    Troy


      ----- Original Message -----=20
      From: fredt=20
      To: Troy McKinnon ; hsqldb-developers@...
      Cc: Troy McKinnon=20
      Sent: Tuesday, January 20, 2004 3:02 PM
      Subject: Re: [Hsqldb-developers] HSQLDB.lck file issues


      The .lck file prevents two processes from simultaneously opening =
the database file. It stays there until you shutdown the database.  If =
it is causing a problem, please state what it is.

      Fred
        ----- Original Message -----=20
        From: Troy McKinnon=20
        To: hsqldb-developers@...
        Cc: Troy McKinnon=20
        Sent: 20 January 2004 22:12
        Subject: [Hsqldb-developers] HSQLDB.lck file issues


        I am having trouble using HSQLDB using CSV type files.  Running =
it in tomcat using Hibernate... as soon as the context starts up the =
lock file appears and it doesn't ever go away.  What exactly creates the =
lck file and how can I get by it?

        Troy

-- How modular is the current transaction support integrated with the rest
of HSQLDB?

Hard to say how modular it is, but it is a simple scheme and is covered in a
few classes. Session keeps a list of rows deleted / inserted in memory and
when a commit is performed, the list is discarded. If a rollback is
performed, the inverse action is performed on the database and the list is
then discarded.

This simple scheme means changes by other sessions are visible
(READ_UNCOMMITTED) and conflicts between actions by two sessions are not
resolved.

-- Who decides about possibel extensions of the SQL language support in
HSQLDB?

I am the maintainer and decide on these issues. My approach is to allow any
useful extensions if they are modular (optional) and do not increase the
core size and complexity. Extensions that are part of the SQL language _can_
increase core size and complexity to some extent.

-- Is there a way, if and how two (or more) HSQLDB server instances can
communicate together, if possible without using SQL angain?!

Yes, support for this has been developed as proof of concept (HSQLDB/R by
Bela Ban - downloadable from our files section) using JGroups. We would like
to make this a standard extension when we have improved the core for wider
transaction isolation support.

We would like to move to multi-version concurrency control in the core (it
is possible to do this without radical changes). I don't know what
"cache-constrained based caching enhancements" is, but any enhancement
sounds like a good idea :) Your work could fit neatly with our future plans
so I would welcome your involvement.

Fred Toussi


----- Original Message -----
From: "Daniel Schaller" <daniel@...>
To: <hsqldb-developers@...>
Cc: <alex@...>
Sent: 18 January 2004 15:29
Subject: [Hsqldb-developers] Better Transaction Handling and Cache
Enhancement


Hi,

I and a fellow student of me are currently working on our diploma thesis
at the Dresden University of Technology (Germany), Chair for Database
Technology.
Our thesises deal about multi-version concurrency control and
cache-constrained based caching enhancements in relational databases.

To implement our features we have decided to use HSQLDB because it is
written in Java and the source code is free available. Furthermore, we
have to implement our features as a kind of core extension of relational
database and not just as a middleware approach.

We would like to know if you as the HSQLDB developers are interested in
these features. If you should be, it would be nice, if you could give us
some hints about the following questions:

- How modular is the current transaction support integrated with the rest
of HSQLDB?
- Who decides about possibel extensions of the SQL language support in
HSQLDB?
- Is there a way, if and how two (or more) HSQLDB server instances can
communicate together, if possible without using SQL angain?!

Thanks in advance

Daniel


-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

Hallo Fred,

Thanks for your quick response.

On Sun, 18 Jan 2004 16:42:14 -0000, fredt <fredt@...>  
wrote with possible deletions:


> This simple scheme means changes by other sessions are visible
> (READ_UNCOMMITTED) and conflicts between actions by two sessions are not
> resolved.

I have read this in the documentation. That was one reason why I thought,  
our developments might be interesting for the HSQLDB community.


> Yes, support for this has been developed as proof of concept (HSQLDB/R by
> Bela Ban - downloadable from our files section) using JGroups. We would  
> like
> to make this a standard extension when we have improved the core for  
> wider
> transaction isolation support.

We will look, if we could use it for our purpose.
If not or if it is to complicated to get used to, we will try to implement  
a simple solution which should have, however, well defined interfaces.

> We would like to move to multi-version concurrency control in the core  
> (it
> is possible to do this without radical changes).

Are there already ongoing works for it or is it "only" point on the to-do  
list?

> I don't know what
> "cache-constrained based caching enhancements" is, but any enhancement
> sounds like a good idea :) Your work could fit neatly with our future  
> plans
> so I would welcome your involvement.

ok, we will try to have a short description of it in English within the  
next days.
However, just for your information: it is based on ongoing research works  
of IBM.

Regards,

Daniel

Re HSQLDB/R, it is not complicated as it uses a well developed framework for
replication. The HSQLDB side though must be improved with better transaction
isolation so that only committed transactions are replicated.

No direct work has been done on multi-version concurrency but some side
issues have been fixed to allow this. In short, with the current indexing
and persistence scheme, it should be possible to retrofit the AVL indexes to
store and return different rows from different sessions, depending on the
transaction isolation level set for the current session. My proposed
solution is to add both inserted rows and deleted rows for each session,
with the deleted rows marked as such and both types having a session id.
Index search and traversal will take the id's and flags into account to
return only the rows that are visible in the session context.

Fred

----- Original Message -----
From: "Daniel Schaller" <daniel@...>
To: "fredt" <fredt@...>;
<hsqldb-developers@...>
Cc: <alex@...>
Sent: 18 January 2004 17:04
Subject: Re: [Hsqldb-developers] Better Transaction Handling and Cache
Enhancement


Hallo Fred,

Thanks for your quick response.

On Sun, 18 Jan 2004 16:42:14 -0000, fredt <fredt@...>
wrote with possible deletions:


> This simple scheme means changes by other sessions are visible
> (READ_UNCOMMITTED) and conflicts between actions by two sessions are not
> resolved.

I have read this in the documentation. That was one reason why I thought,
our developments might be interesting for the HSQLDB community.


> Yes, support for this has been developed as proof of concept (HSQLDB/R by
> Bela Ban - downloadable from our files section) using JGroups. We would
> like
> to make this a standard extension when we have improved the core for
> wider
> transaction isolation support.

We will look, if we could use it for our purpose.
If not or if it is to complicated to get used to, we will try to implement
a simple solution which should have, however, well defined interfaces.

> We would like to move to multi-version concurrency control in the core
> (it
> is possible to do this without radical changes).

Are there already ongoing works for it or is it "only" point on the to-do
list?

> I don't know what
> "cache-constrained based caching enhancements" is, but any enhancement
> sounds like a good idea :) Your work could fit neatly with our future
> plans
> so I would welcome your involvement.

ok, we will try to have a short description of it in English within the
next days.
However, just for your information: it is based on ongoing research works
of IBM.

Regards,

Daniel




-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers

On Sun, 18 Jan 2004 17:34:11 -0000, fredt <fredt@...>  
wrote with possible deletions:

> Re HSQLDB/R, it is not complicated as it uses a well developed framework  
> for
> replication. The HSQLDB side though must be improved with better  
> transaction
> isolation so that only committed transactions are replicated.

Does it also work with the 1.7.2 RC1 candidate. I ask because it is  
mentioned that it works with 1.7.2 Alpha_B version

> No direct work has been done on multi-version concurrency but some side
> issues have been fixed to allow this. In short, with the current indexing
> and persistence scheme, it should be possible to retrofit the AVL  
> indexes to
> store and return different rows from different sessions, depending on the
> transaction isolation level set for the current session. My proposed
> solution is to add both inserted rows and deleted rows for each session,
> with the deleted rows marked as such and both types having a session id.
> Index search and traversal will take the id's and flags into account to
> return only the rows that are visible in the session context.

Ok, some other questions, I hope it's ok:

- Is the index (implemented as as AVL tree) the common way to access the  
data from a table? Is the index always used? Or: how must the index be  
seen in relation with the table?
- Multi-version concurrency control (MVC) demands a strategy to keep  
Write-TX consistent. We want to use FOCC (forward oriented concurrency  
control). For this, we need global (logical) timestamps, which we want to  
create by the Lamport procedure. I hope I can find out how the classes  
Table, Index and so on work. If I know that, I can say more about what  
modifications would be necessary and so on.
- Am I right in the assumption, that currently all request to the database  
are executed serial and not parallel?

And just for my interest:

- Who or why did the decision come into being using a AVL tree as index  
data structure and not the well-known B-Tree (or one of its sub-classes)?
And was the descision made to name a table which is written to the disk as  
"cached" table? I find this terminology a bit confusing due to in other  
databases a normal table is written to disk. When I heard "cached table"  
the first time in the HSQLDB context, I thought this is a in-memory table.  
However, here it is the very opposite: a normal table is a in-memory  
table, a "cached table" is written to disk. Maybe you know how this came  
into being. It's not critism at all, just interest. ;-)

Regards,

Daniel

Daniel Schaller wrote:
Fredt wrote:
>> I don't know what
>> "cache-constrained based caching enhancements" is, but any enhancement
>> sounds like a good idea :) Your work could fit neatly with our future  
>> plans
>> so I would welcome your involvement.
> 
> ok, we will try to have a short description of it in English within the  
> next days.
> However, just for your information: it is based on ongoing research 
> works  of IBM.

Maybe you could post a URL to some of their papers? It sounds quite 
interesting ...

Harald

On Sun, 18 Jan 2004 19:41:39 +0100, Harald Niesche <harald@...> wrote  
with possible deletions:

> Daniel Schaller wrote:
> Fredt wrote:
>>> I don't know what
>>> "cache-constrained based caching enhancements" is, but any enhancement
>>> sounds like a good idea :) Your work could fit neatly with our future   
>>> plans
>>> so I would welcome your involvement.
>>  ok, we will try to have a short description of it in English within  
>> the  next days.
>> However, just for your information: it is based on ongoing research  
>> works  of IBM.
>
> Maybe you could post a URL to some of their papers? It sounds quite  
> interesting ...

not possible at the moment. The papers have been published at ACM.

We will try to write a short explaination and so on and post the link to  
that.

Regards,

Daniel

comments inline

Fred

----- Original Message -----
From: "Daniel Schaller" <daniel@...>
To: "fredt" <fredt@...>;
<hsqldb-developers@...>
Cc: <alex@...>
Sent: 18 January 2004 18:26
Subject: Re: [Hsqldb-developers] Better Transaction Handling and Cache
Enhancement



On Sun, 18 Jan 2004 17:34:11 -0000, fredt <fredt@...>
wrote with possible deletions:

> Re HSQLDB/R, it is not complicated as it uses a well developed framework
> for
> replication. The HSQLDB side though must be improved with better
> transaction
> isolation so that only committed transactions are replicated.

Does it also work with the 1.7.2 RC1 candidate. I ask because it is
mentioned that it works with 1.7.2 Alpha_B version
------------------
HSQLDB/R was recently converted by someone to work with RC1 but I haven't
got the code. I have asked him to send it. Even the old version can easily
be converted.
------------------
> No direct work has been done on multi-version concurrency but some side
> issues have been fixed to allow this. In short, with the current indexing
> and persistence scheme, it should be possible to retrofit the AVL
> indexes to
> store and return different rows from different sessions, depending on the
> transaction isolation level set for the current session. My proposed
> solution is to add both inserted rows and deleted rows for each session,
> with the deleted rows marked as such and both types having a session id.
> Index search and traversal will take the id's and flags into account to
> return only the rows that are visible in the session context.

Ok, some other questions, I hope it's ok:

- Is the index (implemented as as AVL tree) the common way to access the
data from a table? Is the index always used? Or: how must the index be
seen in relation with the table?
-----------------
Yes. All access to data is via indexes. For tables without explicit primary
keys, there is a hidden one backed by an index.
-----------------
- Multi-version concurrency control (MVC) demands a strategy to keep
Write-TX consistent. We want to use FOCC (forward oriented concurrency
control). For this, we need global (logical) timestamps, which we want to
create by the Lamport procedure. I hope I can find out how the classes
Table, Index and so on work. If I know that, I can say more about what
modifications would be necessary and so on.
-----------------
That's fine in prinicple. We will discuss the details once you have studied
the existing code a bit more.

To find out, look at the methods for insert and delete in Table. It goes
through the list of indexes and inserts/deletes the row into/from each
index. That's all. No count of the rows or other information is kept. A
table is empty when its index root node is null.
-----------------
- Am I right in the assumption, that currently all request to the database
are executed serial and not parallel?
-----------------
Yes. The future plan is to change this with table level locking so that
ideally the engine blocks only for one row insert/delete.
-----------------
And just for my interest:

- Who or why did the decision come into being using a AVL tree as index
data structure and not the well-known B-Tree (or one of its sub-classes)?
And was the descision made to name a table which is written to the disk as
"cached" table? I find this terminology a bit confusing due to in other
databases a normal table is written to disk. When I heard "cached table"
the first time in the HSQLDB context, I thought this is a in-memory table.
However, here it is the very opposite: a normal table is a in-memory
table, a "cached table" is written to disk. Maybe you know how this came
into being. It's not critism at all, just interest. ;-)

-------------------
HSQLDB is based on Hypresonic and these decisions were made by Thomas, the
original author.

AVL trees are OK, especially for searching, but they take up more memory
than B-Tree and its derivatives. Also, because of their homogenous
structure, AVL indexe nodes are easier to read/write to disk with the data.
In HSQLDB, the nodes from different indexes for each row are written
together with the row data.

A CACHED table is so called because a number of rows from the disk are
cached in memory. A MEMORY table is fully memory-resident and is written to
disk as SQL statements.
----------------------



Regards,

Daniel


-------------------------------------------------------
The SF.Net email is sponsored by EclipseCon 2004
Premiere Conference on Open Tools Development and Integration
See the breadth of Eclipse activity. February 3-5 in Anaheim, CA.
http://www.eclipsecon.org/osdn
_______________________________________________
hsqldb-developers mailing list
hsqldb-developers@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-developers