File .db always locked using EF6 / WebApi (.Net)

(1) By MaRaNtZ (marantz) on 2020-04-15 11:27:23 [source]

Hi guys.

I have a problem with SQLite that is making me dizzy.

The environment is rather complex. I uses an IIS server that access a SQLite database using Entity Framework EF6 for Dot.Net.

The specific provider i use is System.Data.SQLite.EF6

(https://system.data.sqlite.org/)

As far as i access the database writing something to it, the DB remain locked, and the only way to unlock it is bringing down IIS session!

I know some "connection pool" are probably in use, and this is good, because i always access the DB using disconnected recordset via WebApi, but i wonder if exist a way, a low level call, whatseover, to ABRUPTALLY CLOSE/KILL ALL DB CONNECTIONS and release the file, for maintenance purposes.

Typical usage pattern is with "Using" c# statement, so i'm pretty sure everything is disposed, including the EF6 connection

    public List<behaviour> LoadBehaviours()
    {
        using (var ShapeDb = ShapeDbEntities(true))
        {
            ShapeDb.behaviours.Load();
            return new List<behaviour>(ShapeDb.behaviours);
        }
    }

I have attempted to close the collection pool using this:

    public static void CloseAllConnectionPools()
    {
        if (SQLiteConnection.ConnectionPool!=null)
        {
            SQLiteConnection.ConnectionPool.ClearAllPools();
        }
        SQLiteConnection.ClearAllPools();
    }

But this do not work.

Is it possible to drill down to SQLite core api and force releasing of pending connections ??

Regards, Paolo Marani, Italy

(2) By Gunter Hick (gunter_hick) on 2020-04-16 10:56:13 in reply to 1 [link] [source]

If the actual return code from SQLite ist SQLITE_BUSY, this would indicate that whatever you are doing to "write to the database" is not properly closing the Transaction. E.g. you (or the wrapper) are doing

BEGIN;
INSERT …; and/or UPDATE …;

and never get round to

COMMIT; or ROLLBACK;

and terminating the IIS Session will imply ROLLBACK, i.e. the data you were writing has "misteriously disappeared" when you reconnect.