bug: "database is locked (code 5)" on 7.0.1 and 7.0.2 #659

Activity

1. freezzone

   added

   needs: triage

   bug/fixSomething isn't workingSomething isn't working

   on Sep 22, 2025
2. freezzone

   changed the title [-]bug: "database is locked (code 5)" on 7.0.1[/-] [+]bug: "database is locked (code 5)" on 7.0.1 and 7.0.2[/+] on Oct 19, 2025
3. freezzone commented on Oct 20, 2025

   

   freezzone

   on Oct 20, 2025

   Author

   More actions

   I found a workaround:

   // after db connection is open, `pagehide` event is subscribed
   window.addEventListener('pagehide', (event) => {
         // if there is an ongoing transaction, then an `rollback` query is made
         if (
           this.transactionInProgress &&
           !event.persisted // check if it is an actual page unload event
         ) {
           console.info('Closing db transaction before page unload', this.dbName);
           void this.db.query('ROLLBACK')
         }
   }
   
4. KingSora commented on Nov 13, 2025

   

   KingSora

   on Nov 13, 2025

   More actions

   Having the same problem, but in addition the same issue occurs when the app is being terminated and then immediately restarted.
5. robingenz commented on Nov 13, 2025

   

   robingenz

   on Nov 13, 2025

   Member

   More actions

   I close this issue since a workaround exists. However, if anyone finds a better solution that we can implement in the plugin, PRs are welcome.
6. robingenz

   closed this as completedon Nov 13, 2025
7. KingSora commented on Nov 13, 2025

   

   KingSora

   on Nov 13, 2025

   Last edited by KingSora

   More actions

   @robingenz the workaround doesn't work in case the app is being terminated. - I've digged into this error and the following fix would work:

   1. Copy the DB file to a different location
   2. Delete the original DB file
   3. Move the copied DB file back to the location of the original file
   4. Try to open the DB again

   I'm really not sure if this is the best fix, but nothing else worked for me at all (and I've tried many things, even closing the lingering DB connections didn't work)

   If you accept this solution i would be ready to submit a PR
8. robingenz commented on Nov 13, 2025

   

   robingenz

   on Nov 13, 2025

   Member

   More actions

   Sounds more like an experimental solution to me. ^^ The plugin is used extensively in the Capacitor community. Therefore, the change must be stable. I'll wait and see if more users are affected by the problem, and maybe we'll find a better solution.
9. KingSora commented on Nov 13, 2025

   

   KingSora

   on Nov 13, 2025

   More actions

   @robingenz in that case I think it would be better to re-open the issue though
10. robingenz

    reopened this on Nov 13, 2025
11. KingSora commented on Nov 16, 2025

    

    KingSora

    on Nov 16, 2025

    Last edited by KingSora

    More actions

    @robingenz fixed both issues with my own sqlite abstraction. The key differences to this abstraction are:

    1. The database runs on the main thread and not on the capacitor bridge thread (on android when the app is being terminated by swiping it away, the capacitor thread lingers a bit longer which causes the database locked issue)
    2. When the database is being closed, all transactions are ended with db.endTransaction() before the database.close() call. This ensures that running transaction are rolled back in case the website is being reload mid transaction. This also helps when the app is being exited mid transaction.
    3. There can only be a single database connection to a specific database. When JS Code tries to establish a second connection the first connection is re-used under the hood. This makes sure that when the page is being re-loaded there are no stale connections.

    This changes may don't work for everyone, but are fixing my usecase. - Just for future reference in case someone is interested in my findings.
12. patrickroy07 commented on Jul 29, 2026

    

    patrickroy07

    on Jul 29, 2026

    More actions

    We hit this exact bug in production (Pixel 7, Android 16, plugin 8.1.0 still present at latest), root-caused it, and have been running a one-loop in-plugin fix via patch-package that eliminates it. Posting the Claude analysis since it explains why none of the plugin's close APIs can recover the lock, and why @KingSora's endTransaction()-before-close approach is the right fix to ship in the plugin itself.

    Why close() / closeConnection() / checkConnectionsConsistency() can't fix it

    android-database-sqlcipher mirrors AOSP android.database.sqlite:

    1. A transaction — including a raw BEGIN routed through execSQL (AOSP handles STATEMENT_BEGIN specially) — is owned by a SQLiteSession bound to the thread that executed it. The session keeps the connection checked out of the pool until endTransaction() runs on that same thread.
    2. When the WebView document reloads mid-transaction, the JS context that would have sent COMMIT/ROLLBACK is gone. The native session keeps the connection checked out forever, holding the database file's write lock.
    3. Database.close() → SQLiteDatabase.close() closes the pool, but a checked-out connection is only truly closed when returned — which never happens. So closeConnection() "succeeds" (removes the dict entry) while the locked connection lives on.
    4. Even GC can't reclaim it promptly: the session is strongly referenced from the executor thread's ThreadLocal map. In our tests the lock only released after ~13 minutes, when incidental ThreadLocal housekeeping finally unpinned it and the finalizer ran (W SQLiteConnectionPool: A SQLiteConnection object ... was leaked! Please fix your application to end transactions in progress properly...). We even tested a native System.gc() + runFinalization() method — it collected other leaked pools but never the pinned one.

    This also explains why the pagehide + ROLLBACK workaround is unreliable: it fire-and-forgets an async bridge call while the document is being torn down. In our on-device testing that call never reached native before the context died — the wedge reproduced every time despite the listener.

    Note the iOS asymmetry: the iOS implementation closes with sqlite3_close_v2(), which rolls back any open transaction as part of closing — so iOS recovers in this scenario and Android does not.

    Fix (verified on-device)

    Restore parity with iOS by ending the current thread's open transaction before closing, in Database.close() (android/.../SQLite/Database.java):

    public void close() throws Exception {
        if (_db.isOpen()) {
            try {
                while (_db.inTransaction()) {
                    _db.endTransaction();
                }
            } catch (Exception e) {
                Log.v(TAG, "close: failed to end open transaction: " + e.getMessage());
            }
            try {
                _db.close();
                _isOpen = false;
                ...

    Because plugin calls execute on the same serialized executor thread that ran the orphaned BEGIN, inTransaction() (thread-scoped) sees the orphan, endTransaction() rolls it back and returns the connection to the pool, and the close completes for real — the file lock is released. For a normal close (no open transaction on the closing thread) the loop is a no-op, so existing behavior is unchanged.

    With this patch the previously 100%-reproducible wedge is gone: reload mid-transaction → next closeConnection()/createConnection() cycle reconnects cleanly in ~2s, no database is locked.

    Caveat, which also explains @KingSora's app-termination variant: the rollback only works when close runs on the thread that owns the session. That holds for the reload case (same executor thread), but a close dispatched on a different thread — e.g. during app termination — would still leak; that case likely needs the connection to live on a dedicated thread, as KingSora did in his abstraction.

    👍React with 👍2Reacted by Rene Haas and Raqueebuddin Aziz
13. vanillacode314 commented on Aug 7, 2026

    

    vanillacode314

    on Aug 7, 2026

    More actions

    @patrickroy07 patch fixed this issue in my app as well, would be amazing if this got merged