PRAGMA locking_mode minimizes IDB transactions

[rhashimoto]

I've made the new IndexedDbVFS journaling compatible with PRAGMA locking_mode=exclusive, which basically locks the database at a session level instead of a transaction level.

As @jlongster has demonstrated, IndexedDB transactions are very expensive, and so optimizing performance mainly depends on using as few as possible. Inspired by that work, IndexedDbVFS does try to reuse IndexedDB transactions, but that is usually not possible across a Web Locks API call. Test 1 on the benchmarks page on my local machine takes about 14.3 seconds for 1000 INSERTs where each insert is its own transaction, and only 0.7 seconds for 25000 INSERTs in a single transaction.

Exclusive locking uses only one or two Web Locks calls until the normal mode is restored or the database is closed, and that greatly improves IndexedDB transaction reuse. The benchmarks page allows a SQL "preamble" for this sort of configuration, and with PRAGMA locking_mode=exclusive; added, the Test 1 time drops to under 0.08 seconds. That is roughly 175x faster (!).

You could get essentially the same timings by wrapping all your SQL in a BEGIN...COMMIT transaction, but only if your SQL doesn't already contain transactions (because they don't nest). So having the exclusive locking setting is a nice feature that doesn't take away any SQL semantics. Just be aware that the database locking will now be much coarser and this may affect concurrency.

Note that extending a IndexedDB transaction lifetime across multiple SQLite transactions means that the database is not actually durable (durable is the "D" in ACID). This means that when a SQLite transaction returns, you aren't guaranteed that it has reached permanent storage. In the case of a power loss or other interruption the database will remain in a consistent state (consistent is the "C") but may not have all the latest transactions recorded. This design choice is partly to improve typical use performance, but also because in general IndexedDB itself may not be truly durable on all browsers anyway (which partially explains why Chromium browsers are slower).