Clarification on what read concurrency + write serialization means in implementation?

[abhay-agarwal]

I'm using IDBBatchAtomicVFS with the Shared Web Locks modification as described in the FAQ and documentation. However, I'm still a bit confused on what that means in practice. For example, the sqlite-api.js seems to enforce certain access patterns that confused me about how to write a SQL transaction correctly.

In order to create an async write transaction I need:

1. the ability to start and commit a transaction, which is executed through an async call or set of calls.
   a. This seems to be solved by the provided API, e.g. sqlite3.exec(db, `BEGIN TRANSACTION IMMEDIATE; ...\`)
2. the ability for a different async action to wait on the VFS being ready to accept a new write transaction.

The primary mechanism for accomplishing (2) seems to be the navigator.locks API. Does that mean that in my code, I need to identify queries that will write to the database and place them inside of a global lock namespace? Or is there a lower-level functionality that will auto-queue write requests as part of the API?

[rhashimoto]

Does that mean that in my code, I need to identify queries that will write to the database and place them inside of a global lock namespace?

No, you don't need to do this at the application level if you are using a VFS that support multiple connections, like IDBBatchAtomicVFS.

Or is there a lower-level functionality that will auto-queue write requests as part of the API?

Yes, any database connection that needs a lock to proceed will automatically try to acquire the necessary WebLocks. If it cannot my locking implementation will either suspend the caller until the lock is available or return SQLITE_BUSY. If it returns SQLITE_BUSY (which happens only with shared locks) then a deadlock has occurred and you must ROLLBACK your transaction and resubmit it to break the deadlock.

[abhay-agarwal]

Thanks for the help! My issue is then that I don’t seem to be able to coax the database to return an SQLITE_BUSY. I only seem to be receiving Runtime Errors. I am using a fork that replaces WebLocksExclusive with WebLocksShared. Could you share a brief snippet or pseudocode that does this check and rollback procedure? Or if there is a place in the demo code that does this, that would help clarify things a lot for me.

And lest it not go unsaid, amazing work on this implementation!

[rhashimoto]

Ah, I can see how this could be confusing. The underlying SQLite C library API returns integer result codes from almost every function, but my JavaScript wrapper API throws an exception for most codes that are not SQLITE_OK, including SQLITE_BUSY. This is an opinionated policy to prevent lazy developers (e.g. me) from ignoring the return codes. The original code is available as the property "code" on the exception object.

You should handle this in a try/catch block or rejected Promise handler. So something like:

while (true) {
  try {
    // Execute some SQL transaction, e.g.:
    const results = [];
    await sqlite3.exec(`
      BEGIN IMMEDIATE;
      ...
      COMMIT;
    `, (row, columns) => results.push(row));
    return results;
  } catch (e) {
    // Propagate errors other than SQLITE_BUSY.
    if (e?.code !== SQLite.SQLITE_BUSY) {
      throw e;
    }

    // Rollback and retry.
    await sqlite3.exec(`ROLLBACK`);

    // Optional delay to reduce the chance of livelock.
    await new Promise(resolve => setTimeout(resolve, 5 * Math.random()));
  }
}

Note that the SQL submitted in the try block should be a complete transaction, and ideally only one transaction unless you're fine with repeating everything if SQLITE_BUSY is encountered.

[abhay-agarwal]

Ah, I see what was occurring now. So I had code that split the transaction into several, incrementally executed blocks that were executed using either several execs or the for (await ...) pattern.

In this case, it seems like the overhead of using navigator.locks for transactions involving writes might be preferable? Since that ensures that there's never any need to rollback, and there's a guaranteed queue that minimizes worst-case behavior. Do you have a sense of what the runtime cost of using navigator.locks would be compared to your rollback code here?

Alternatively, (just out of curiosity), it seems like the "best" solution would be one that had the write behavior of WebLocksExclusive while had the read behavior of WebLocksShared. With the drawback that the application would need to be able to select the behavior at runtime. This could be, e.g. an additional flag sent to the sqlite3.exec function.

For now (for me) it seems like the best DevEx is actually with the navigator.locks pattern, given that my higher level API knows which calls are writes vs reads, and I require the guaranteed SLA implicit within the queue (and also generally feel allergic to infinite while loops).

[rhashimoto]

In this case, it seems like the overhead of using navigator.locks for transactions involving writes might be preferable? Since that ensures that there's never any need to rollback, and there's a guaranteed queue that minimizes worst-case behavior. Do you have a sense of what the runtime cost of using navigator.locks would be compared to your rollback code here?

It seems like you're suggesting locking at the application level using a single WebLock and acquiring a shared lock for read transactions and an exclusive lock for read/write transactions. My WebLocksShared implementation needs to use two WebLocks (because the WebLocks API doesn't support upgrading a lock) so I expect your suggestion to be more efficient. I haven't actually measured WebLocks performance, but my sense is they aren't expensive compared to everything else going on. Even so, your suggestion looks like a win for both performance and code complexity.

The only reservation I would have is you'll need to be absolutely certain that your code identifies read/write transactions correctly. If you let any writes slip by without the exclusive lock then that opens the door to database corruption. To be safe you could write a custom VFS that verifies the application lock status before proceeding with any writes.

[abhay-agarwal]

Makes sense! Yes, for my application, I am not creating a generic interface such as a Template Tag. The developer (me in this case for now) would build on top of a ORM-like abstraction. I am also using the SQLite database as a cache, and therefore durability is not mission critical.

Thanks for the help!