5

It is not a good idea to use a SQLite database, for write access, on a CIFS share. Understood.

I have a need to do so on a very infrequent basis. The database is written very infrequently on the Windows server (Actually windows 10, and like once ever few weeks) and equally infrequently from the Linux (Ubuntu 16.04.02 if it matters) server. The chances of simultaneous writes is near zero (which is not zero of course).

As I understand it (and I am not sure I do) using the "nobrl" option on the mount allows this to work (and indeed it does work for me), but does so by disabling locking entirely (right? Unless there are other types?).

Is there a technique, without deploying code on the Windows side, to ensure that this is in fact safe -- options for SQLite for example, that might not be the default. Locking the entire database is perfectly acceptable during the update on the ubuntu side, performance is not an issue, and simultaneous access is not required. The main restriction is I cannot change the process on the windows side.

Linwood
  • 268
  • 3
  • 9
  • 1
    SQLite works if all the involved file systems implement locking correctly. How would "nobrl" be helpful? – CL. Mar 10 '17 at 16:17
  • Run `sqlite3` on both machines, do a bunch of transactions by hand, and check that you get lock errors when needed. – CL. Mar 10 '17 at 16:18
  • @CL: without nobrl, at least with these two OS's, the database returns "locked" on any write action. It's documented in many places that the workaround is nobrl. And indeed it works. But the suggestion to force locks and see if I get them is good, let me see if I can hold a transaction open (I don't want to depend on hitting return on two windows at once as a sync technique :) ). – Linwood Mar 10 '17 at 16:59
  • Just use [BEGIN](http://www.sqlite.org/lang_transaction.html)/END. – CL. Mar 10 '17 at 17:24
  • I ran sqlite3 (command) on linux, and firefox's sqlite manager on windows. I tried inserting the same row (violating a constraint) on both, starting with a begin on linux and not committing until it inserted (and it did) on windows. When I commited on linux I got an "disk I/o error". So I have a feeling it is not working properly in terms of locking, even though the result was (appropriately) rejection of the duplicate (actually I think it should have prevented windows if working right). So yes, I think the default behavior is to risk corruption. – Linwood Mar 11 '17 at 00:59
  • 1
    Any attempt to change anything tries to take a RESERVED lock. The second attempt to write should have resulted in "database is locked" (before the commit). Anyway, you don't need actual data conflicts; any concurrent writes are forbidded. Executing `BEGIN IMMEDIATE` on both must fail. Can you find some CIFS client configuration that works? – CL. Mar 11 '17 at 07:24
  • Does `cache=none` make it work? – CL. Mar 11 '17 at 07:38
  • 1
    I got the sqlite3 command line for windows so I would be using the same on both. I confirmed on the same system BEGIN IMMEDIATE will fail ("database is locked") if done from two separate processes, but does not fail when one process is over the CIFS share. cache=none makes no difference. – Linwood Mar 11 '17 at 12:19

0 Answers0