Transaction isolation

[steida]

I'm porting my code from AbsurdSQL leveraging full transaction isolation (because it's sync) and I'm not sure how transaction isolation works in wa-sqlite IDB. What if I need to select a row, update it, and then save it without some other thread changing it meanwhile. What I need is a lock on a specific row. Do I need Web Locks? Thank you

[rhashimoto]

SQLite calls the database VFS xLock/xUnlock methods at the beginning and end of each transaction (assuming you don't change the default locking mode), so it's up to you (you being the VFS author) to implement the locking mechanism. All the example IndexedDB classes use Web Locks for locking, which provides safe shared locking for read-only transactions and exclusive locking for read-write transactions. As long as (1) your browser supports Web Locks (quite recent for Safari, and I found a bug that they fixed but hasn't reached production yet) and (2) your critical SQL is within a transaction, isolation is satisfied.

Web Locks is the easiest (by far) way to implement locking, but not the only way. You can also do it with a service worker) if you have to.

Be aware that because the SQLite locking model only upgrades locks when it has to, it can deadlock in certain cases. I'm not sure whether busy_timeout works in wa-sqlite (because of single threading), so you may need to use BEGIN EXCLUSIVE TRANSACTION for transactions that may write to the database. Update: The example IndexedDB class implementations now use an exclusive Web Locks lock for both reading and writing, which avoids this upgrade deadlock but does not allow concurrent read transactions. There is a drop-in replacement locking class (WebLocksShared.js) that does allow concurrent read transactions but when using this the application must handle SQLITE_BUSY exceptions by rolling back and retrying any current transaction.

On a different topic, you posted and then deleted a message about having trouble with the benchmarks page. I assume you deleted it because you resolved the issue but I'm curious what it was.

[steida]

@rhashimoto The issue I deleted was "benchmarks stopped", but it wasn't a bug. I just did not read "very slow on IDB"

[steida]

As for the answer, I'm not sure if I understand it properly. What if I want to add some DB fixtures on the app start. The first query is a check whether a table is empty. If it is empty, then add some rows, which is a second (async) query. Do BEGIN ensure that the other tabs can't run the same login between the first and the second queries? I need a lock from reading to saving. Thank you for your patience.

[rhashimoto]

Use BEGIN EXCLUSIVE TRANSACTION instead of BEGIN to avoid deadlock. You can open the demo in two windows to try it (tip: selecting statements limits execution to only those statements so you can run them one at a time in each window) to see that entering the transaction in one will block execution in the other until COMMIT or ROLLBACK. Once the transaction completes (or you close the window), the blocked window will continue.

[rhashimoto]

Thanks for having me look at this because there is a bug in WebLocks.js. Fix coming soon-ish, by tomorrow hopefully.

[rhashimoto]

Bug #49 fixed. All the IDB VFS classes use the now simplified WebLocks.js, so they won't deadlock. You can use BEGIN TRANSACTION, no EXCLUSIVE needed (which actually wouldn't have completely prevented deadlock). There is a WebLocksShared that can be used instead of WebLocks - it allows concurrent read transactions but applications will need to handle SQLITE_BUSY properly.