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. Many thanks, Magos _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
