Re: [Springframework-developer] JDBC Insert functionality for HSQL

[<tri...@tr...>]

Ken,

> I have been using the incrementers for Mysql and Hsql for the petclinic 
> demo. The Mysql version of the incrementer was changed to use an  UPDATE 
> statement on the sequence. The Hsql version uses an INSERT statement as 
> the Mysql version used to do. I notice that the Hsql version  leaves 
> extra rows in the sequence table because of this. There also is a 
> different way  of caching blocks of keys. I don't understand why this is 
> so. Is this something that just never got changed after the Mysql 
> version got reworked ???
> 

I don't think HSQL has the feature, of bumping up the id, that I used for MySQL.
 The most reliable way of retrieving new ids was actually retrieving them from a
sequence table and caching the ids.  We could clean up the extra rows after we
are done however, and just leave the last one.  I'll make that change today or
tomorrow.  

A more generic approach would be the following:

"select current_id from sequence_table;"
store current_id in x
"update sequence_table set current_id = current_id + 10 where current_id = x"
check update count - if it is 0 then repeat the whole process again (someone
else incremented the sequence)

This approach does not work if you are using transactions with isolation level
serializable.  If someone else increments the sequence while your transaction is
running, but before you try to increment the sequnce, then you will never be
able to retrieve the new current_id and your whole transaction will fail.

HSQL does not support any isolation levels other than READ_UNCOMMITTED, so this
approach would work.  I'll try it when I am making the change.

Unless we can come up with a general solution that will always work in all
databases, I would prefer to provide a few specific solutions and let anybody
that needs a different solution provide their own implementation.

Do you have a new download available - I would like to set up a datasource and
deploy the demo app on the website.

Thomas