[Hsqldb-user] Selection of concurrency control for concurrent reads and writes

[Utkarsh S. <utk...@gm...>]

Hello,

I am evaluating hsqldb2.0 for this use case:
High writes: ~50-100/sec
Low reads: ~1/10 sec

But that data which I am storing is not INSERT intensive but UPDATE
intensive. I.e. out of the 100 writes/sec, around 70 will be updates
(increment counts) to the 30 inserted rows.

And a daemon will be triggered every 10 seconds to sweep the data in
HSQLDB, process it and ideally *clean it up.*

There are two difference threads doing the writes and reads.

So my questions:
1. What is your recommendation on the selection of the transaction control?
I assume it should be MVCC.

2. How does the setting up of a transaction control work? If I set: SET
DATABASE TRANSACTION CONTROL MVCC when the hsqldb is created for the first
time. Will this setting survive new connections, hsqldb server restarts? Or
I need to do this every time I obtain a connection?

3. How do I check the performance of MVCC vs LOCKS for my use case? Any
pointers?

4. Does HSQLDB have SELECT ... FOR UPDATE syntax like MYSQL? (this will
help me in making safe updates). I didn't find it in the documentation.

5. What is your recommendation about flushing the data once its read? Can I
place a lock around truncate and call it immediately after I SELECT the
data? The problem with this is, I can loose some data, but I think that is
acceptable.

Would love to see your inputs on this!

-- 
Thanks,
-Utkarsh