Bug 301520 – SQLite WASM database remains locked even after closing

• Status: NEW
• Component: WebAssembly

[Jure Rotar]

Created attachment 477204 [details]
Video reproduction of "SQLite WASM database remains locked even after closing" bug

Hi!

Sorry if this bug report already exists, I couldn't find anything similar.

I'm developing an app which uses sqlite-wasm (https://github.com/sqlite/sqlite-wasm) for storing data. As part of a normal user flow, users are able to stop their current work, which clears all prepared statements, closes the database, pauses the VFS and closes the worker which originally spawned the database. This process redirects them to the homepage, from which they are able to continue their work at any time. 

The issue occurs when a user attempts to reopen their work. When attempting to reopen an existing database, a "InvalidStateError: The object is in an invalid state" error is raised. This error does not appear if user manually navigates away before attempting to stop their work "normally". If however user does encounter this error, even a hard refresh or a repeated attempt to open a database, which encountered this error, will not work. 
My speculation is this happens because a lock is not properly cleared when a database & worker closes. 

This was tested on iPhone 13 with iOS 26, Windows's 11 on multiple browsers and a couple of Android phones. All of them, except iOS, worked fine.

Full reproduction steps (please see attached video if any step is unclear):

1) Open https://test-safari-error-debug--pillagefirst.netlify.app

2) Click "Create a new world" on the homepage. You'll be redirected to https://test-safari-error-debug--pillagefirst.netlify.app/create-new-server. Click "Create server" button.

3) You'll be redirected to a game world. Leave the game world through the exit button (top-right if using landscape-mode tablet or desktop, bottom-right if using a mobile device).

4) You'll now appear back on the homepage. Attempt to re-enter your existing game world and an error will appear. If you attempt a full-refresh, you'll now be greeted with a corrupted database. (SQLITE_ERROR: no such table events).


I've prepared a branch with the issue, should you need to reproduce: https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/tree/test/safari-error-debug
Relevant code is found in: https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/blob/test/safari-error-debug/app/(game)/api/api-worker.ts

[Karl Dubost]

This is the error log which is happening when using the steps to reproduce at the top.


In the page:
SQLITE_ERROR: sqlite3 result code 1: no such table: events


In the console:
[Error] Origin https://test-safari-error-debug--pillagefirst.netlify.app is not allowed by Access-Control-Allow-Origin. Status code: 200
[Error] Failed to load resource: Origin https://test-safari-error-debug--pillagefirst.netlify.app is not allowed by Access-Control-Allow-Origin. Status code: 200 (data:text/javascript;base64,aW1…g==, line 0)
[Error] SQLite3Error: SQLITE_ERROR: sqlite3 result code 1: no such table: events
	Module Code (api-worker-DP1-234H.js:862:724)
[Error] Error: SQLITE_ERROR: sqlite3 result code 1: no such table: events — api-provider-DQeV5Lrv.js:1:1596
	ul (entry.client-BnGvsX4c.js:8:68108)
	pl (entry.client-BnGvsX4c.js:8:68302)
	(anonymous function) (entry.client-BnGvsX4c.js:8:68818)
	hs (entry.client-BnGvsX4c.js:8:49844)
	gs (entry.client-BnGvsX4c.js:8:49937)
	lu (entry.client-BnGvsX4c.js:8:97492)
	Au (entry.client-BnGvsX4c.js:8:102817)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103135)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103135)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102378)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102378)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102868)
	hf (entry.client-BnGvsX4c.js:8:132435)
	pf (entry.client-BnGvsX4c.js:8:130885)
	Gd (entry.client-BnGvsX4c.js:8:123465)
[Error] React Router caught the following error during render – Error: SQLITE_ERROR: sqlite3 result code 1: no such table: events — api-provider-DQeV5Lrv.js:1:1596
Error: SQLITE_ERROR: sqlite3 result code 1: no such table: events — api-provider-DQeV5Lrv.js:1:1596
	componentDidCatch (chunk-OIYGIGL5-gdoSadV7.js:4:3045)
	(anonymous function) (entry.client-BnGvsX4c.js:8:68928)
	hs (entry.client-BnGvsX4c.js:8:49844)
	gs (entry.client-BnGvsX4c.js:8:49937)
	lu (entry.client-BnGvsX4c.js:8:97492)
	Au (entry.client-BnGvsX4c.js:8:102817)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103135)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103135)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102378)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102378)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102341)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:103697)
	Wu (entry.client-BnGvsX4c.js:8:112350)
	Au (entry.client-BnGvsX4c.js:8:102868)
	hf (entry.client-BnGvsX4c.js:8:132435)
	pf (entry.client-BnGvsX4c.js:8:130885)
	Gd (entry.client-BnGvsX4c.js:8:123465)

[Karl Dubost]

There is no error in Firefox and Chrome.

[Radar WebKit Bug Importer]

<rdar://problem/163557366>

[Karl Dubost]

I tested the failure on Safari technology preview 230, and a recent version of macOS. 
It reproduces each time.

[Karl Dubost]

See the discussion on https://github.com/sqlite/sqlite-wasm/pull/117

[Jure Rotar]

Another relevant discussion can be found in this issue thread as well: https://github.com/sqlite/sqlite-wasm/issues/79

[Karl Dubost]

The Access-Control-Allow-Origin issues are interesting too. Because I don't see them in Firefox and Chrome consoles.

[Jure Rotar]

Created attachment 477209 [details]
Video 2 of reproduction of "SQLite WASM database remains locked even after closing" bug

I'm attaching another reproduction video. In this video you can see SQLite creation and persistence working fine when doing page refreshes. It also works fine with using a back navigation button and re-entering a game world. 

It however breaks as soon we attempt to close the database and worker (which happens when using the in-game logout button). After that, database appears to get corrupted and no longer works in subsequent attempts at opening.

[Karl Dubost]

so in the JS code the try/catch starts here.
https://test-safari-error-debug--pillagefirst.netlify.app/assets/api-worker-DP1-234H.js

And it's when the first error happens.
```
try {
    let e = new URLSearchParams(self.location.search).get(`server-slug`),
        t = await (await tl()).installOpfsSAHPoolVfs({
            directory: `/pillage-first-ask-questions-later/${e}`,
            forceReinitIfPreviouslyFailed: !0
        }),
        n = new t.OpfsSAHPoolDb(`/${e}.sqlite3`);

… a lot of code…


} catch (e) {
    console.error(e),
    self.postMessage({
        eventKey: `event:worker-initialization-error`,
        error: e
    })
}
```

The error is happening during

```
    let r = el(n, !1);
    $(r),
    self.addEventListener(`message`, e => {
        let {data: t, ports: n} = e,
            {type: i} = t;
        if (i !== `WORKER_MESSAGE`)
            return;
        e.stopImmediatePropagation();
        let [a] = n,
            {url: o, method: s, body: c} = t;
        try {
            let {handler: e, params: t} = Qc(o, s),
                n = e(r, {
                    params: t,
                    body: c
                });
            s !== `GET` && self.postMessage({
                eventKey: `event:worker-event-creation-success`,
                ...c,
                ...t
            }),
            a.postMessage({
                data: n
            })
        } catch (e) {
            console.error(e),
            self.postMessage({
                eventKey: `event:worker-event-creation-error`,
                ...c
            });
            return
        }
    }),
```

[Karl Dubost]

    let r = el(n, !1);

n here is a filename `/s-b5d2.sqlite3`.

[Jure Rotar]

Full, un-minified code is available here: https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/blob/test/safari-error-debug/app/(game)/api/api-worker.ts

Essentially, it mounts up the database based on pre-existing db file. It then listens for messages from the main thread. Once it receives a message of type "WORKER_CLOSE", it closes db, closes sahpool vfs and the worker itself. This is the point where I believe the issue happens.

[Karl Dubost]

Ah thanks! With my habits to only usually see only obfuscated minified code, this should help. :)

[Jure Rotar]

Glad to help!

I'll add more context. When I initially noticed the issue, I assumed the issue was with OPFS supporting reading concurrency. As a result, I've changed the DB driver from 001 to SAH pool, which only supports exclusive reader, in this commit: https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/commit/4d1bd3aafc8f3b825b4775cfc0f833875e34ecaf. This hasn't resolved the issue, so I investigated further. I found this thread: https://github.com/sqlite/sqlite-wasm/issues/79, where another user encountered a similar (same?) issue. 

I've then tried manually clearing the cached prepared statements and pausing the VFS (https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/commit/8b217f7b6cef84d53841c6bba00a09fee78aa878), but again, to no avail. 

You can find the history of these changes here: https://github.com/jurerotar/Pillage-First-Ask-Questions-Later/commits/test/safari-error-debug/app/(game)/api/api-worker.ts

Hope this helps!