1650201 - Investigate if we can fallback to non-exclusive when we fail to get an exclusive lock

Status: RESOLVED FIXED

(Core :: SQLite and Embedded Database Bindings, task, P2)

Description

[Marco Bonardo [:mak]]

The problem at hand is that third party software opens and uses our Sqlite databases, and then when we try to get an exclusive lock (on Mac and Linux we use the unix-excl VFS) we fail and the product breaks.
In general we use unix-excl because it improves compatibility with NFS shares, it allows wal mode to avoid shared memory -shm files, and provides some protection from third parties while the app runs.
Unfortunately the third advantage is also a disadvantage, and there's no easy way to detect if we're on an NFS share.

It would be nice if we could have a mode where in general we try to get an exclusive lock, but if that fails we still continue. Though I'm not sure if technically that's feasible, for example with a unix-try-excl VFS, or it would break assumptions.

Comment 1

[Marco Bonardo [:mak]]

Richard, do you have any insight or suggestions for us about this matter?

Comment 2

[D. Richard Hipp]

Just brain-storming here: Maybe you can start out trying to take an exclusive lock. On unix/mac you could use unix-excl. But that is not portable to Windows, so let's assume instead you just issue the command:

      PRAGMA locking_mode=EXCLUSIVE;

Then you do some innocuous query like:

      SELECT 1 FROM sqlite_schema LIMIT 1;

If that query returns "database is locked", then switch back to:

      PRAGMA locking_mode=NORMAL;

... And carry on. Would that work for you? What other requirements do you have?

On unix, we can find out what process ID is holding the conflicting lock. And then maybe send them a SIGKILL? (They are trespassing on your database after all.) Or would that just be profoundly anti-social?

Comment 3

[Marco Bonardo [:mak]]

(In reply to D. Richard Hipp from comment #2)

Just brain-storming here: Maybe you can start out trying to take an exclusive lock. On unix/mac you could use unix-excl. But that is not portable to Windows, so let's assume instead you just issue the command:
PRAGMA locking_mode=EXCLUSIVE;

The last time I tried (honestly it was many years ago, so things may have changed, or I was not doing it properly at the time) that was also breaking our other concurrent connections trying to access the same database. I can try again of course!
We started using unix-excl mostly for the nfs locking problems, would exclusive locking have similar benefits?

On unix, we can find out what process ID is holding the conflicting lock. And then maybe send them a SIGKILL? (They are trespassing on your database after all.) Or would that just be profoundly anti-social?

Yep, I don't think we'd want to do that, let's try to cohexist with other software as far as possible.

Comment 4

[D. Richard Hipp]

If the database is on a network filesystem, and another application is using the database, and the database is in WAL mode, then there is nothing you can do, unfortunately, other than to pop up an error message explaining the situation to the user. WAL mode must have shared memory in order to coordinate access between processes - not a lot of shared memory, but some. And on a network filesystem, with access from different hosts, that is clearly not possible. You can use WAL mode on a network filesystem with an exclusive lock, because that guarantees that nobody else can be accessing the database, and hence you can use heap memory instead of shared memory. But otherwise, if the database is on a network filesystem, you have to go with one of the rollback modes, such as DELETE.

In the 20-year history of SQLite, we've always left enforcement of that restriction up to the application. Perhaps the time has come that we need to enhance SQLite to prohibit the use of a network filesystem for a WAL-mode database, except in exclusive locking mode. We'll have to use statfs(), which is not posix, and hence varies from one OS to another, but prehaps we can come up with a sufficient #ifdef so that it works on all of Linux, *BSD, and MacOS-X and would hence be sufficient for all modern use cases.

But that still doesn't solve your problem, which is, if I have this correct, that you need to read/write the database files even though they are in WAL mode on a network filesystem, and possibly being read and written by another application running on a different host. That's going to be a tough nut to crack. We are working on it....

Comment 5

[Marco Bonardo [:mak]]

(In reply to D. Richard Hipp from comment #4)

But that still doesn't solve your problem, which is, if I have this correct, that you need to read/write the database files even though they are in WAL mode on a network filesystem, and possibly being read and written by another application running on a different host. That's going to be a tough nut to crack. We are working on it....

We use WAL mode extensively for its performance benefits, of course we also have users with profiles on network shares, even if, in general, we discourage that. We don't have control over other applications, that ideally should not be touching our databases, but they do.
This is still a rare case, compared to the normal behavior. As I said we discourage profiles on network shares so it's not my primary target for now.

The more likely problem we hit is due to other apps on the same system keeping our databases busy when we try to open them in exclusive mode, that could probably be resolved with your suggestion from comment 2: try to open with "unix-excl" and if it fails fallback to "unix". Technically it should be feasible, so we can try. In some edge cases that may break WAL on network shares, we'll have to live with that.

Comment 6

[Marco Bonardo [:mak]]

Attachment: Bug 1650201 - Fix mozStorage prefs read before profile and fallback to a non-exclusive VFS when it can't get an exclusive lock. r=asuth

Comment 7

[Pulsebot]

Pushed by [e-mail]:
https://hg.mozilla.org/integration/autoland/rev/08d94377db09
Fix mozStorage prefs read before profile and fallback to a non-exclusive VFS when it can't get an exclusive lock. r=asuth,geckoview-reviewers,agi

Comment 8

[Dorel Luca [:dluca]]

https://hg.mozilla.org/mozilla-central/rev/08d94377db09