On Fri, Oct 19, 2012 at 3:16 AM, Ben Morris <magospiet...@gmail.com> wrote:
> Hello all, > > I've recently joined an organization that is attempting to use SQLite in a > multi-user environment (C#, using System.Data.SQLite, layered under the > DevExpress XPO ORM). Due to the high-latency nature of the networks on > which our application is deployed, we're seeing a very high number of > database locked exceptions generated (as well as very poor performance). > > Performance was understandably worse when the default journalling mode was > enabled, so this has been turned off, leading to almost daily occurrences > of malformed databases. > > I've repeatedly argued that SQLite is clearly inappropriate for use in a > multi-user environment, as per the documentation, and that we should make > the switch to a client/server RDBMS as soon as possible. However, given my > perceived lack of seniority, the management team has elected to go with a > local-master synchronisation process as recommended by the primary > developer. > > This developer is currently implementing this synchronisation algorithm to > allow users to work on a local SQLite database and have that data > transferred to a master SQLite database on the network. > > When a local row is changed, a Sync flag is set to true. The > synchronisation algorithm is triggered by a timer running every second, and > consists of two methods: Upload and Download. Any SQL commands mentioned > below are being built using String.Format, by combining data and metadata > extracted from methods available in the ORM layer. > > During Upload, the local database is scanned for rows where the Sync flag > is true. Each row like this is either updated or inserted into the master > database (depending on whether a row can be found with the same PK). > > During Download, every local table is compared, row-by-row, field-by-field, > to the corresponding table in the master database (both tables are loaded > into memory as array structures to speed up the comparison). If any > differences are found, or the local row is missing, the local row is > updated/inserted using the data from the master database > > Ignoring the obvious fact that this will not resolve our locking and > malformation problems, I'm also deeply concerned about the maintainability > and supportability of such code. Given the fact that I cannot get any > traction with my management team, I was hoping an appeal to authority might > make them see sense. > > If anyone could share their honest opinions of both my suggested approach, > and my colleague's, I would be very grateful. > A lot depends on what your application is trying to do... If you need consistency between all clients, then using a client/server RDBMS is clearly the best approach. But no every application needs this. Using a separate SQLite database on each client to serve as a local cache of the master database and then periodically synchronizing with the master is a very reasonable thing to do in many situations. Such an approach can be very bandwidth efficient (if implemented correctly) and it has the huge advantage that is allows for disconnected operation - it allows the clients to continue functioning when the network goes down. The disadvantage is that individual clients are not always completely up-to-date with the latest changes. After they've had a chance to synchronize, their display will be up-to-date. (We say that the clients are "eventually consistent".) But it might take a few seconds or a few minutes to achieve consistency, depending on how the network is doing. So there are trade-offs. Is it important that your clients have an absolutely consistent view of the state of the system at all times, but can put up with outages due to network issues? Or is it better to have eventual consistency and be tolerant of network failures? If you need absolute consistency, then a client/server RDBMS is clearly the best approach. But if eventual consistency is sufficient and network failure tolerance is desirable, then using a local SQLite cache of a master database might be the superior approach. A familiar example of an eventually-consistent system would be the Fossil DVCS. Fossil stores all content in a local SQLite database, then synchronizes with peers over the network. Fossil (unlike CVS or Subversion) allows development to continue while off network, and then synchronizes when connectivity is restored. Git works the same way, though Git uses an ad-hoc pile-of-files database for its local cache rather than an RDBMS. Which reminds me - another advantage of having local caches of the master database is that you get automatic backups. If the server caches fire or otherwise self-destructs, all of your content can be recovered from any of the clients. In other words, you get automatic backups. > > Many thanks, > > Magos > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
