> On Sunday, 14 October, 2012, at 20:28, Larry Knibb said: > I'm trialling a centralised SQLite database to store process > automation metadata with a known, small, fixed number clients (around > 10) as part of a Jenkins CI system running on Windows. The clients > connect occasionally and run simple, quick (sub-second) SELECT queries > and even less frequent INSERTs using Python APIs. Due to the nature of > the workflow, I'd estimate the chances of concurrent access are pretty > low, although not impossible by any means. Recently, I've started > seeing these errors related to some of the INSERT calls:
> DatabaseError: database disk image is malformed > StorageError: database disk image is malformed Define "clients". Do you mean multiple client processes running on a single computer against a database hosted on an attached local device, such as on a Terminal Server for example? Or do you mean multiple clients connecting over LANMAN/CIFS/NFS to a database file sitting on a remote fileserver? > Given the scenario (and being familiar with > http://www.sqlite.org/whentouse.html), I suspect this almost certainly > an issue caused by concurrent writes to the database, but I'd like > some advice before move to MySQL or similar. If file locking is working correctly, you cannot have multiple writes to a database. Write operations to the database are exclusive. > Would using TRANSACTIONs on the INSERTs prevent this issue? If > necessary, by combining this with a delay-retry mechanism in the > client code. You may wish to try forcing locks to be acquired sooner when updating the database by using BEGIN IMMEDIATE or perhaps even BEGIN EXCLUSIVE before updating the database, and COMMIT when you are done. You will then also need a busy-timeout so that other readers/writers will wait for that operation to complete. You can set the timeout when creating the connection with sqlite3 in python, or by using PRAGMA busy_timeout > Any other tips or tricks to shore-up the robustness, or should I just > limber up my throwing arm and call for the towel? > > Cheers, > Larry > > P.S. just fmi, I'd also love to know -->definitively<-- if concurrent > SELECT calls would ever be an issue. Multiple connections can concurrently read (as in SELECT). Writing is exclusive. For multiple processes on the same machine, WAL mode permits multiple readers and a single writer where readers do not block the writer and the writer does not block readers. Latency of locking operations over network connections can play havoc, however. It is possible if you are using a network mounted database file that the write operations are not being flushed properly and or the locks are not propagating in a timely fashion. http://www.sqlite.org/lang_transaction.html http://www.sqlite.org/lockingv3.html --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
