[GNC-dev] [GNC] mysql backend, second user (lock, for example)

[John Ralls]

> On Nov 8, 2018, at 6:42 AM, Craig Arno <craig at arno.com> wrote:
> 
> On 11/6/2018 3:54 PM, John Ralls wrote:
>> Read about SQLite3 locking: https://www.sqlite.org/lockingv3.html <https://www.sqlite.org/lockingv3.html>. They’re locking virtual memory pages, totally independent of table or record structure. In practice what that means is that at the application level only using the SQL Transaction API makes sense, the application doesn’t have enough visibility of the internals to be able to implement finer-grained controls.
> 
> Then record locking may not be the best approach to solving the multi-source SQLite database issue.  For asynchronous concurrency caused by peer or multi-thread database request input sources either an Event Queue or Message Queue design pattern could be built as a serializing front-end for database transactions.
> 
> Using a queue has other benefits if you find yourself in a situation where there are heavy database updates.  As a for instance "Write updates" could be given priority over "Read requests" so Read Requests can be assured of returning the latest and most relevant results.  For SQLite this might be because the user put their database on a slow device, like a USB stick, or as you suggested earlier, DropBox.
> 
> Interesting read on SQLite record locking.  I had no idea.  Thanks!

Always keep GnuCash’s target audience (Personal/Small Business) in mind: We’re definitely not designing for “heavy database updates”. Any business that needs an auditor won’t be allowed to use GnuCash because of the complete absence of internal controls. In order to use a queue serialization model we’d have to disable update queries and allow only inserts for the offline session. Remember, no conflict resolution: An update query just overwrites the record, there’s no check to see if the old value is what was expected. In GnuCash terms that means that when you click on an existing transaction in the register or open an Edit Foo dialog box the session needs to acquire an exclusive lock on the record *in the database* and hold it until you exit the transaction or close the dialog box.

Regards,
John Ralls