Menu
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?
487
When a database is accessed by multiple connections, and one of the processes modifies the database, the SQLite database is locked until that transaction is committed. The timeout parameter specifies how long the connection should wait for the lock to go away until raising an exception.
There's no use checking before (if that is possible), it still can get locked between the check and your next call. Better would be to make your call, and handle possible exceptions, eg. retry a number of times and report an error after too much retries.
Overview. Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.
SQLite engine do not have built-in security to protect databases, rather, it relies on its environment such as the operating system to provide security for database content. While Android provides security mechanisms for SQLite databases, it has been shown to be inadequate.
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.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
