SQL BackEnd Clarification

[John Ralls]

On Nov 22, 2013, at 12:48 PM, David T. <sunfish62 at yahoo.com> wrote:

> I have yet to use the SQL back end with GnuCash, but in following the many discussions on the lists (both users and devel), I have read seemingly contradictory statements about the nature of data storage with the SQL back end. 
>  
> Specifically, at times we are told that GnuCash loads the entire SQL database into memory, and that simultaneous access to the same data file by multiple users will fail because GnuCash writes out the entire database upon closing. However, at other times, we are told that the Save button is never activated with the SQL back end because the changes are saved to the SQL back end immediately.
>  
> Could someone explain this apparent paradox to me?

You’re confusing the two backends. Both XML and SQL backends do load everything into memory at startup. The XML backend writes everything out when you run Save and periodically if you have auto-save turned on. The SQL backend writes at almost every time you do something, and that ‘almost’ is why we don’t recommend using it for production. We think that there are a few places that we haven’t found yet where a change doesn’t trigger a write or even mark the object as dirty. That works out OK in the XML backend because something else the user does or did will require a save and everything gets saved, but if the object isn’t marked dirty, the SQL backend won’t save it even if a write of some other object is triggered.

The only locking in either backend is database-wide, and once the SQL backend has read the database it doesn’t do so again until you either switch databases or restart Gnucash. That’s why it’s single-user.

Regards,
John Ralls