New database backend and multi-user

[Geert Janssens]

Hi,

I'm very interested to use the new db backend in the upcoming 2.4 series.

Thinking about this, I was wondering if the current implementation deals well
with multi-user access to the same db. I mean can two users from two different
computers use gnucash with the same (postgres or mysql) gnucash database at
the same time ?

I am aware that the db backend in the current implementation is mainly the xml
file concept translated into a db backend, but so far it's not clear to me if
this has implications for concurrent access to the db or not (when access is
properly done via the gnucash engine).

Regards,

Geert
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

[Phil Longstaff]

On September 6, 2009 06:37:32 am Geert Janssens wrote:

> Hi,
>
> I'm very interested to use the new db backend in the upcoming 2.4 series.
>
> Thinking about this, I was wondering if the current implementation deals
> well with multi-user access to the same db. I mean can two users from two
> different computers use gnucash with the same (postgres or mysql) gnucash
> database at the same time ?
>
> I am aware that the db backend in the current implementation is mainly the
> xml file concept translated into a db backend, but so far it's not clear to
> me if this has implications for concurrent access to the db or not (when
> access is properly done via the gnucash engine).

There are a couple of problems with the current db back-end with respect to
multi-user access.

1) locking/transactions - db transactions are used whenever an object is
written or updated.  However, no locking is done, and there are certain cases
where related objects are not saved in the same db transaction, because the
back-end does not have enough information.  For example, when a transaction
with multiple splits is saved, each of these is a separate engine object and
is saved separately.  The back-end API would need to be modified to add "start-
transaction" and "end-transaction" calls so that multiple objects could be
saved/committed/rolled back together.

2) Update notification - Most objects are read when the db is opened and not
read again.  Some databases (postgres? not mysql?) provide a callback when an
update is made, so that data can be refreshed.  An alternative would be a
timer to refresh.

3) Mechanism to handle conflicts - How should the cases be handled when
different people make conflicting changes?

Phil
_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

[Geert Janssens]

Thanks for the info. I have added some more comments inside your replies.

From what I understood by following the dev list, converting gnucash to a true
db based application is at best a long-term goal. That would solve the multi-
user aspects automatically, as the consistency in concurrent access would be
guaranteed by the db engine.

However, gnucash is far from there yet. So the alternative seems to be: make
sure there's only one user accessing the db at any one time. With xml files
this was handled with a lock file. Is something similar implemented in the db
backends ? I mean, if one person is running gnucash a postgres backed book,
and another person tries to start gnucash with the same postgres backed book,
will he get the "This book seems to be open by another person" dialog box ?
That would be sufficient for now.

Regards,

Geert

On Sunday 6 September 2009, Phil Longstaff wrote:

>
> There are a couple of problems with the current db back-end with respect to
> multi-user access.
>
> 1) locking/transactions - db transactions are used whenever an object is
> written or updated.  However, no locking is done, and there are certain
> cases where related objects are not saved in the same db transaction,
> because the back-end does not have enough information.  For example, when a
> transaction with multiple splits is saved, each of these is a separate
> engine object and is saved separately.  The back-end API would need to be
> modified to add "start- transaction" and "end-transaction" calls so that
> multiple objects could be saved/committed/rolled back together.
>
> 2) Update notification - Most objects are read when the db is opened and
> not read again.  Some databases (postgres? not mysql?) provide a callback
> when an update is made, so that data can be refreshed.  An alternative
> would be a timer to refresh.
>
> 3) Mechanism to handle conflicts - How should the cases be handled when
> different people make conflicting changes?
>
> Phil
> _______________________________________________
> gnucash-devel mailing list
> [hidden email]
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel

_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel

[Graham Leggett]

Phil Longstaff wrote:

> 1) locking/transactions - db transactions are used whenever an object is
> written or updated.  However, no locking is done, and there are certain cases
> where related objects are not saved in the same db transaction, because the
> back-end does not have enough information.  For example, when a transaction
> with multiple splits is saved, each of these is a separate engine object and
> is saved separately.  The back-end API would need to be modified to add "start-
> transaction" and "end-transaction" calls so that multiple objects could be
> saved/committed/rolled back together.

I would say this is a prerequisite whether multi-user or not. We don't
want an error to happen half way through the save, and the end up with a
corrupt database.

This didn't matter in the XML world so much, as if an error occurred
that corrupted data you were unlikely to get the opportunity to save the
corrupted xml file.

> 2) Update notification - Most objects are read when the db is opened and not
> read again.  Some databases (postgres? not mysql?) provide a callback when an
> update is made, so that data can be refreshed.  An alternative would be a
> timer to refresh.

A far simpler approach is to just react to errors intelligently.

For example, if you have chosen an option from a dialog box, and you
attempt to commit a transaction depending on that option, and you
discover the option you selected in the dialog box no longer exists
(it's been changed, whatever), just tell the user "sorry, conflict,
please make your change again, as the database changed out from beneath
you".

No need for sophisticated error handling, but we do need something more
than the evil message "an error has occurred".

> 3) Mechanism to handle conflicts - How should the cases be handled when
> different people make conflicting changes?

You simply pop up an error message to the end user, telling them that a
conflict has occurred and they must abandon their changes and try again.

In theory you won't store up a long list of changes, resulting in a big
conflict, you would rather do one small changes at a time, resulting in
small conflicts which are trivial to redo. Small conflicts are also
extremely unlikely to happen, making the pain both small and seldom.

Don't be tempted to offer the end user the option "use my changes and
ignore the conflicted ones", as this is a recipe for pain and angst for
the end user.

Regards,
Graham
--


_______________________________________________
gnucash-devel mailing list
[hidden email]
https://lists.gnucash.org/mailman/listinfo/gnucash-devel