We need to prevent multi-user access to the SQL backend (Re: New GnuCash article on LWN)

[Tao Wang]

On Wed, May 19, 2010 at 6:58 PM, Christian Stimming <stimming at tuhh.de> wrote:
>
> The article raises one valid serious concern: The database backend does
> nothing to prevent multiple user access. This is bad because simultaneous
> access to the SQL database from multiple users will almost surely cause data
> loss. On the other hand, the technology term "SQL backend" will almost
> surely raise the user expectation that multiple user access were possible,
> so for sure people will give it a try. Since it will corrupt their data, we
> need to build in some prevention measure - the equivalent of the "lock
> file", but inside the SQL database. Ideas, anyone?
>

Create a table in database, and

1) insert a entry contains a GUID of current user/process/computer
when GnuCash is loading the database.
2) remove the entry when it exit.
3) Let user decide whether force to overwrite the entry the entry
exist, such as last time GnuCash crashed, or there is another process
is using it.
4) Read the entry everytime GnuCash is accessing the database, to make
sure it's not overwrite by others.

Instead of the lock of whole database, another solution is recording
locks of each entity/object in the table, everytime user trying to
edit something, gnucash create a lock(a entry of the lock_table) on
the object, and any other user trying to edit that object will get a
warning, and will not be able to edit the object unless the previous
user release the lock.

-- 
Regards

Tao Wang