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

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

Oh and I missed to answer your question.
I have a hsqldb is running as a local server and I connect like this:
dbc:hsqldb:hsql://localhost/counters_db

Also, I am trying out MVLOCKS. As the documentation says: "In heavily
updated data sets, this mode allows uninterrupted read access to the
data.". Is this true for TRUNCATE and DELETE also?
I.e. as I am truncating a table, if other threads are reading it will it
get a snapshot view for reads?

Thanks,
-Utkarsh


On Thu, Jan 10, 2013 at 5:58 PM, Utkarsh Sengar <utk...@gm...>wrote:

> Thanks for the answer, that helps!
>
> Also, what do you recommend to do upserts in hsqldb?
> I tried this approach and my main thread somehow goes out of memory when I
> do this.
>
> MERGE INTO counters
>    USING
>        (VALUES('ddd', 1, 'dd', 'fd', 'ddd')) AS v(id, col1, col2, col3,
> col4)
>    ON
>        counters.id = v.id
>
>    WHEN MATCHED THEN
>      UPDATE SET counters.col1 = v.col1+1
>
>    WHEN NOT MATCHED THEN
>      INSERT VALUES v.id, v.col1, v.col2, v.col3, v.col4;
>
>
> Doing this works (i.e. no OOM) but do I need to start a transaction around
> this?
> SELECT count from table where id=val
>  IF FOUND: UPDATE
>  ELSE: INSERT
>
>
>
>
> Thanks,
> -Utkarsh
>
>
> On Thu, Jan 10, 2013 at 3:33 AM, Fred Toussi <fr...@us...>wrote:
>
>>   1. This depends on whether the table is a MEMORY table or CACHED
>> table. With memory tables, things happen more quickly, therefore you may be
>> able to use LOCKS. Another interesting option is MVLOCKS which allows
>> readonly connections to read the data regardless of writes. The choice also
>> depends on the number of updating threads. With few threads, there is less
>> difference between the different transaction modes.
>>
>>  2. Transaction control is a stored property of the database. Try a
>> sample file: database and you will see the settings in the .script file.
>>
>>  3. Write a test case with more than one thread that does something
>> similar to your app.
>>
>>  4. SELECT FOR UPDATE locks the table in LOCKS and MVLOCKS but not in
>> MVCC. It is better to avoid it and use normal UPDATE statements.
>>
>>  5. Re flushing the data, you can use DELETE or TRUNCATE. The engine
>> handles all the necessary locking safely.
>>
>>  Fred
>>
>>  On Thu, Jan 10, 2013, at 8:48, Utkarsh Sengar wrote:
>>
>>   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
>>
>> ------------------------------------------------------------------------------
>>  Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
>>  MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>>  with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>>  MVPs and experts. ON SALE this month only -- learn more at:
>>  http://p.sf.net/sfu/learnmore_122712
>>  *_______________________________________________*
>>  Hsqldb-user mailing list
>>  Hsq...@li...
>>  https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
>> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
>> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
>> MVPs and experts. ON SALE this month only -- learn more at:
>> http://p.sf.net/sfu/learnmore_122712
>> _______________________________________________
>> Hsqldb-user mailing list
>> Hsq...@li...
>> https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>>
>>
>
>
> --
> Thanks,
> -Utkarsh
>



-- 
Thanks,
-Utkarsh