Concurrency issue on Chrome & Safari with OPFS

[xvrh]

We are desperately trying to find a workaround for an issue where users are getting stuck infinitely on our loading screen. This occurs on both Chrome and Safari.

We have set up a reproduction repository for experimentation, and we can trigger various edge cases that leave the database in a bad state. After hitting the bug, the only way to restore functionality is to kill the shared service worker or restart the browser.
https://github.com/xvrh/powersync-web-hang-repro

Chrome:
After a few attempts, the database fails to connect and throws the following error in the console: A call to getWebLock has been aborted. Normal functionality is restored once the shared worker is killed.
https://github.com/user-attachments/assets/87ba737e-ebe6-4f1a-b1e3-d3c09121b9c6

Safari:
ERROR: SqliteException(0): Dangling transaction detected. If you want to use BEGIN statements manually, COMMIT or ROLLBACK them before returning from writeLock.
https://github.com/user-attachments/assets/cfe933c9-9bf1-4cf4-9a0a-312c97641e4d

I also encountered a couple other variations of this bug on Safari with different behaviours, though I was unable to capture them on camera.

I am submitting this report in hopes that it helps isolate the underlying bug our users are experiencing.

[simolus3]

Thank you for the very helpful report and repro! I can reproduce the issue locally. I've recently seen a similar issue in one of our demos, but that one got fixed in version 0.8.1 of sqlite3_web. Your demo still reproduces the issue with a PowerSync worker compiled against the latest sqlite3_web version. We'll investigate.

[simolus3]

Oh silly me, I missed that your repro copies files to build/ so I didn't actually test with an updated worker.

Recompiling the PowerSync worker against 0.8.1 seems to improve things. I'll try to get a few more web-related fixes in today before cutting a release, but you could use this worker as powersync_db.worker.js in the meantime. With that worker and no further changes, the sync process seems to recover well but the tab is stuck at coldReset:clearing. But after also adding dependency overrides to unreleased powersync versions, everything seems to work well (I've only tested Chrome so far though).

[simolus3]

We've just released version 2.3.0 which should improve this. Could you check whether updating to that version and worker fixes the issue in your app?

[xvrh]

@simolus3 we ran our Safari stress test suite with the latest v2.3.0 (and the updated powersync_db.worker.js).
It seems to have improved the situation (~25% improvement) BUT we are still experiencing hangs.

This is a stress test so it's not really normal user behaviour. (it exercises very intensively dual-tab with forcing database cleanup etc...) but I think it could still hide something interesting to be fixed

[simolus3]

I managed to reduce this to a very minimal issue, and I think this is a Safari/WebKit bug. I have opened this issue, let's see what comes out of it. Basically, it looks like locks held by workers are still marked as active when the tab is reloaded through window.location.href changes.

I can see a straightforward workaround (acquire database locks on the tab using it instead of in the worker), but that's an annoying refactoring for sqlite3_web, especially if it's temporary.

Do you also have similar cases in your app that trigger these deadlocks? Changing the URL would seem like a somewhat rare thing for a Flutter web app, if there are more cases that trigger this I can see if those reduce to a similar minimal case as well.

[xvrh]

Yeah the URL navigation is purely fabricated just to force the reproduction. We don't do that in our app.

What is sure is that: we have real users complains that sometime the app doesn't load.

We challenged the IA to stress-test powersync and it found those ways to reproduce it (url navigation and refreshing the page with cold database in dual-tab mode). I'm not really happy with those findings because it hardly explain what our users see but I though it was a start 😜

[simolus3]

Fair enough, FWIW I wouldn't be surprised if the root cause for that minimal issue has other ways to manifest itself. So maybe there's more than one way for workers to lock up in Safari.

It would definitely be good to know if you find additional issues outside of Safari. I'll see how much effort a workaround for this one would be.