exclusive locking_mode in two tabs

[steida]

I just tested my app with two tabs open, and on the second tab, DB access is always somehow locked. When I don't use exclusive locking, my app works as expected. Is it possible I am doing something wrong?

const asyncModule = await SQLiteAsyncESMFactory();
const sqlite3 = SQLite.Factory(asyncModule);
sqlite3.vfs_register(
  new IDBBatchAtomicVFS("evolu", { durability: "relaxed" })
);
const connection = await sqlite3.open_v2("app", undefined, "evolu");
// await sqlite3.exec(connection, "PRAGMA locking_mode=exclusive");

[rhashimoto]

I think the behavior you describe is working as expected. That's how exclusive locking works; it takes a lock and holds on to it:

When the locking-mode is set to EXCLUSIVE, the database connection never releases file-locks. The first time the database is read in EXCLUSIVE mode, a shared lock is obtained and held. The first time the database is written, an exclusive lock is obtained and held.

Note that while this says that when the database is read a shared lock is held, the stock IDBBatchAtomicVFS doesn't use shared locking, i.e. only one connection can hold the lock at a time even if only reading.

If you want to release the exclusive mode lock so another context can access the database, you have to either close the connection or change the mode and access the database:

Database locks obtained by a connection in EXCLUSIVE mode may be released either by closing the database connection, or by setting the locking-mode back to NORMAL using this pragma and then accessing the database file (for read or write). Simply setting the locking-mode to NORMAL is not enough - locks are not released until the next time the database file is accessed.

[steida]

Thank you for your answer. If I understand it correctly, it means I have to implement some mechanism over wa-sqlite, if I want to use it with more than one tab. I have almost no idea, how that mechanism should work. Should I observe tab focus/blur? A simple example would be great.

Meanwhile, if I will not use EXCLUSIVE mode, do I have to use WebLock? Probably yes. Then I don't understand the point of the whole async API, because, with custom WebLock, the sync version should be OK. Am I correct? Thank you for your work.

[rhashimoto]

I'm not sure why you want to use locking_mode=exclusive. There are valid reasons for it; I just want to check that you have one. If you say that your application is working as expected without it, why do you need it?

The default locking mode will ensure that each tab's view of the database is consistent and only one can modify the database at a time, so you don't need to change the locking mode to get that. You only need to group related statements in transactions.

If you do indeed require locking_mode=exclusive then you can enable it when it is needed and disable it when you are done.

[steida]

I thought locking_mode=exclusive is for multi-tab usage. I did not test parallel/simultaneous access across tabs (contexts). Glad to hear it's not required. Can you please clarify when locking_mode=exclusive is required? Thank you.

[rhashimoto]

There are three reasons to set the locking-mode to EXCLUSIVE.

1. The application wants to prevent other processes from accessing the database file.
2. The number of system calls for filesystem operations is reduced, possibly resulting in a small performance increase. [Performance increases can be substantial, e.g. for many small transactions]
3. WAL databases can be accessed in EXCLUSIVE mode without the use of shared memory.