14

Let's say there are two python scripts that want to write data to the same table which is stored in an SQLite file using the sqlite3 module. The SQLite-file is stored on an NFS filesystem. In the SQLite-FAQ I read:

SQLite uses reader/writer locks to control access to the database. [...] But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.

Does this mean it is not possible at all or is there some way to ensure that one process waits until the other is done?

The INSERTs are not complex. Just some:

INSERT_STATEMENT = "INSERT INTO some_table (row, col, val) VALUES (?, ?, ?)"
connection.executemany(INSERT_STATEMENT, triples)

And the inserted sets are disjoint.

A further question: Does the NFS-Problems occure when two processes try to write to the same table or when they try to write to the same database (which is a file)? Would it be a workaround to let each process create its own table in the same database (file) and write to that?

Aufwind
  • 25,310
  • 38
  • 109
  • 154

1 Answers1

23

Do not use SQLite with NFS. It is as simple as that. NFS semantics are different than regular filesystems and are looser. You will eventually get corruption. Every now and then someone on the SQLite-users mailing list posts with their "workarounds". They never work although they appear to in the short term.

Roger Binns
  • 3,203
  • 1
  • 24
  • 33
  • 1
    Unfortunately I have to use SQLite. I ended up giving each process its own file to write to, and merge the scripts afterwards. But thanks for your answer! :-) – Aufwind Apr 02 '12 at 11:15
  • 4
    When you eventually get corruption, do make sure to mention that you used NFS. And don't be surprised when the response from everyone is "do not do that". – Roger Binns Apr 02 '12 at 20:39
  • 1
    "do not do that", even if a single process will access the file? –  May 14 '14 at 01:37
  • 1
    You would need perfect code and processes to be absolutely certain that under no circumstances is there concurrent access. If you *ever* fail perfection, you will get unnoticed corruption which you'll eventually discover too late. – Roger Binns May 14 '14 at 05:39
  • 1
    It sounds like the answer might be more like "it's possible, if the NFS implementation does X, Y, and Z correctly, which some don't" For instance, https://aws.amazon.com/about-aws/whats-new/2017/03/amazon-elastic-file-system-amazon-efs-now-supports-nfsv4-lock-upgrading-and-downgrading/ mentions that EFS (AWS NFS impl) now supports NFSv4 lock upgrading and downgrading, and specifically calls out SQLite. That seems to address the warning about locking. Are there some other filesystem semantics that SQLite relies on and that are iffy for some NFS implementations? If so, what? – pchiusano May 26 '22 at 20:25
  • NFS will work fine under normal circumstances (as will virtually anything). But when things go wrong there are now two (or more systems involved), all of which have to get *everything* correct *every* time interacting with each other. That just doesn't happen, and is why the SQLite team continues to recommend against networked filesystems. There are other solutions like litestream.io for networking your SQLite database. – Roger Binns May 27 '22 at 15:47