> Le 13 juin 2016 à 14:20, Luc DAVID <lucdavid....@free.fr> a écrit : > > Hello, > > I'm planning to install an application using sqlite on a windows 2003 > server. > > The application exe + sqlite dll + database will be installed in the same > server directory.
Best would be to compile sqlite itself within the application, no need to depend on a dll version. > The application will be used by ± 10 users via RDP TSE clients. > > I need to take care of possible database locks during writing operations > (not much) (10% writing, 90% reading). > > I am wondering if WAL mode is the way to go and if it is safe to use sqlite > in WAL mode on this kind of configuration. Keep in mind SQLite is multi-readers, one single writer, even in WAL mode. What WAL brings is that readers do not block a writer and that readers do get to see a stable snapshot of the database for the duration of their (read) transactions (won't see what writers have written after the start of their read transaction). Though a writer will block any other writer, a behavior which doesn't change with WAL journal. So: - Keep your write transactions as short as possible and never dependent on end-user reaction (or on possibly long duration subtasks - as connecting a web service for instance). This is the number one recommendation. If you need to follow only one, it is this one. - Keep in mind (or be prepared for) that if you start transactions as read then upgrade them to write, you will easily get BUSY_SNAPSHOT denials, if any other write transaction occurred in-between. Use BEGIN IMMEDIATE explicitly to start transactions that will have to write and use BEGIN (implied DEFERRED) to start transactions intended to only read. That makes the intent clearer in your code and will help not be surprised by this return code. - If sometimes relying on the implicit transaction which any statement execution starts in the absence of an explicit transaction, take care of correctly calling reset() or finalize() on any stepped but incomplete statement. If you don't, the implicit transaction won't auto-commit immediately (as the statement is still live) and you will easily find more BUSY_SNAPSHOT situations which might look unexplained to you at first. If you need to debug such situations, the APIs sqlite3_next_stmt(...) and sqlite3_stmt_busy(...) are your friends. Using WAL mode, we have excellent results from an application-server kind application (using multiple threads to serve multiple clients for a wide range of different services) with good application code organisation essentially based on the above principles. On some instances of these servers we have more than 50 live users behind. The one-writer-at-a-time limitation of SQLite is not an issue if the application design takes that correctly into account. - If application has to be multithreaded our best results come from using SQLite in MULTITHREADED mode instead of SERIALIZED, and taking care that NO database attachment is ever shared by multiple threads, alongside using the PRIVATE_CACHE mode and not the SHARED_CACHE mode. This provides for the least contention on data structures. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
