> 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

Reply via email to