3

I have recently converted my workspace file format for my application to sqlite. In order to ensure robust operation on NFS I've used a common update policy, I do all modifications to a copy stored in a temp location on the local harddisk. Only when saving do I modify the original file (potentially on NFS) by copying over the original file with the temp file. I only open the orginal file to keep an exclusive lock on it so it someone else tries to open they will be warned that someone else is using it.

The problem is this: When I go to save my temp file back over the original file I must release the lock on the orginal file, this provides a window for someone else to get in and take the original, albeit a small window.

I can think of a few ways around this:

(1) being to simply dump the contents of the temp in to the orginal by using sql, i.e. drop tables on original, vacumm original, select from temp and insert into orginal. I don't like doing sql operations on a sqlite file stored on NFS though. This scares me with corruptions issues. Am I right to think like this?

(2) Use various extra files to act as a guard to prevent other from coming in while copying the temp over the original. Using files as a mutex is problematic at best. I also don't like the idea of having extra files hanging around if the application crashes.

I'm wondering if anyone has any different solutions for this. Again to copy the temp file over the original file while ensuring other application don't sneak in and grab the original file while doing so?

I'm using python2.5, sqlalchemy 0.6.6 and sqlite 3.6.20

Thanks, Dean

Sandstone
  • 148
  • 2
  • 8

1 Answers1

2

SQLite NFS issues are due to broken caching and locking. If your process is the only one accessing the file on NFS then you'll be ok.

The SQLite backup API was designed to solve exactly your problem. You can either backup directly to the NFS database or to another local temp file and then copy that. The backup API deals with all the locking and concurrency issues.

You can use APSW to get access to the backup API or the most recent version of pysqlite. (Disclosure: I am the APSW author.)

Roger Binns
  • 3,203
  • 1
  • 24
  • 33
  • Thanks Roger I was unaware of that api. After reading the sqlite api docs I still have a question I hope you can answer. In my application I lock the original file with "PRAGMA main.locking_mode=EXCLUSIVE" then copy to a temp file where I do all my operations. I lock the original so if another application comes along an error can be shown to the user that the file is in use by someone else. My questions is: Will I have to release the lock on the original file to start the backup? Meaning will there be a small window where a second application can sneak in and lock the file from the first. – Sandstone Feb 24 '11 at 20:11
  • Doing a backup locks the destination exclusively and the source while pages are being copied. You can copy all the pages at once or copy a smaller subset to allow others access to the database. The copy process will correctly deal with other accessors making changes (or not). – Roger Binns Feb 26 '11 at 19:01