Making certain transactions more durable

[Adam McMahon]

I am trying to understand H2's durability and how to get improved durability for certain transactions.  In general I think that the default durability of H2 is quite appropriate for my use case (knowing that in a rare crash I could lose about one second of data).  But there are certain transactions that I would like to flush directly to disk. 

By putting ACCESS_MODE_DATA=rws in a connection url, does that only affect the this connection or does that affect all connections?  If it only affects this connection, then I think could use 'rws' followed by a CHECKPOINT SYNC to get specific transactions to flush.  Would this work on specific transactions while running most everything else in default mode?

Are there any other suggestions on how to use the default durability, and then also have the ability to flush specific transactions? 

Thanks,

-Adam

[Noel Grandin]

On Sat, Oct 27, 2012 at 2:09 AM, Adam McMahon <ad...@cs.miami.edu> wrote:
> By putting ACCESS_MODE_DATA=rws in a connection url, does that only affect
> the this connection or does that affect all connections? If it only affects

If it is the first connection i.e. the one that causes the database to
be opened, it will affect all connections.
Otherwise it will have no affect.

> this connection, then I think could use 'rws' followed by a CHECKPOINT SYNC
> to get specific transactions to flush. Would this work on specific
> transactions while running most everything else in default mode?
>

You can just CHECKPOINT SYNC the stuff that is really important to you.
Note that
(1) this will affect all recently closed transactions because that
layer of the DB does not distinguish between data belonging to
different connections
(2) there is no guarantee that the data has actually hit the disk,
because most modern disks have some kind write-behind buffer. So there
is still a small window of data that you might lose.

In general, a properly configured UPS is a much better solution.

[Adam McMahon]

Thanks for the reply. The H2 community is always helpful!

That makes sense with the ACCESS_MODE_DATA.

I just want to make sure that I understand how to use the "CHECKPOINT SYNC".  Assuming I use the default durability and the defualt ACCESS_MODE_DATA, then I can simply call CHECKPOINT SYNC after an important update/insert.   And that is it?  I understand that there are still no absolute guarantees with this, but this seems like it should work.

My environment has pretty good power backup.

[Noel Grandin]

On Sat, Oct 27, 2012 at 3:55 PM, Adam McMahon <ad...@cs.miami.edu> wrote:
> I just want to make sure that I understand how to use the "CHECKPOINT SYNC".
> Assuming I use the default durability and the defualt ACCESS_MODE_DATA, then
> I can simply call CHECKPOINT SYNC after an important update/insert. And
> that is it? I understand that there are still no absolute guarantees with
> this, but this seems like it should work.

That's pretty much it, yes. Just be warned that this will stall other
concurrent operations, so it might impact what other connections are
doing.