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

[Craig Arno]

On 11/6/2018 3:54 PM, John Ralls wrote:
> Read about SQLite3 locking: 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!

Craig