"Database is locked" with SQLite and C#

Question

I have a problem with my code. I want to delete some rows in my SQLite database but I get a "Database is locked" exception. I read several posts about that like this post, but my problem is still here.

Here is my code :

using (var c = new SQLiteConnection(_connectionSQLite))
{
    c.Open();

    if (c.State == ConnectionState.Open)
    {
        var reqExist = string.Concat("SELECT id FROM ... ");
        using (var cmdExist = new SQLiteCommand(reqExist, c))
        {
            var reqUpdate = string.Concat("UPDATE ... WHERE id = ", cmdExist.ExecuteScalar());

            using (var cmdUpdate = new SQLiteCommand(reqUpdate, c))
            {
                cmdUpdate.ExecuteNonQuery();
            }
        }
    }

    c.Close();
}

I get the "database is locked" exception on the line cmdUpdate.ExecuteNonQuery();. I tried replace with a DELETE FROM, same result, but with a SELECT, it works, I really don't understand what's wrong with my code.

Thanks for any help.

@AbhilashRVankayala - That's terrible advice. There's nothing wrong with nested using statements, and such statements (or equiv try-finallys) are necessary for ensuring proper resource disposal - which is one such cause of their issue. — antiduh, Jul 05 '16 at 19:11
Are you opening multiple connections to the same database at the same time? — Lasse V. Karlsen, Jul 05 '16 at 19:12
Nasty things will happen if that ExecuteScalar doesn't find any id. It seems a clever idea to concat the return of ExecuteScalar but .... — Steve, Jul 05 '16 at 19:13
@LasseV.Karlsen no. It's just a save function. Insert and Select works fine, but Update and Delete got a "database is closed" exception — BlackAlpha, Jul 05 '16 at 19:29
@Steve I simplify my code, it's not the complete one, don't worry about that — BlackAlpha, Jul 05 '16 at 19:29
Try adding cmdExist.Dispose(); before the reqUpdate line. If that fixes it change the code to reuse the same command instead of creating two. You can also declare requpdate outside of the first using and not nest the two commands. — Gaspa79, Jul 05 '16 at 19:36
@Gaspa79 I tried cmdExist.Dispose(); and not nest the two commands but the problem is still here — BlackAlpha, Jul 05 '16 at 19:44
@BlackAlpha do you know the type of the lock? Can you execute a simple update/insert to that table? — Gaspa79, Jul 05 '16 at 19:48
@Gaspa79 Oh ... I just noticed I can't insert, it works once but I can just select now ... I am a little bit lost — BlackAlpha, Jul 05 '16 at 19:53
If you can only insert once it means that something is going wrong. Either the connection is not correctly closed or the command is not disposed or something. That means that it might not be a problem with the code above but with something else. Try getting the lock type first so it's easier to know what's causing it. — Gaspa79, Jul 05 '16 at 19:54
Agree with Gaspa79. Try moving the code to somewhere you can properly run it by itself and test it without other code potentially accessing the database (and make sure you don't have the database open in another tool such as a designer). — Tone, Jul 05 '16 at 19:59
Should I use a particular connection string ? I just use @"Datasource=;Version=3", no problem with this ? — BlackAlpha, Jul 05 '16 at 20:05

score 6 · Answer 1 · answered Jul 05 '16 at 20:10

6

Ok everyone, thanks for your help. I just have to .dispose() two readers in an other function before insert/update, and it works !

answered Jul 05 '16 at 20:10

BlackAlpha

376
1
5
15

2

Omg thanks for the heads up, i spent 2 hours trying to figure out why my database keep getting locked sometimes and it was because i accidentally deleted a reader.close()!! usually when i forget to close the reader the app will throw an error saying the reader is still open but not this time because the reader was located inside another function – wren May 28 '19 at 12:37

score 1 · Answer 2 · edited May 23 '17 at 11:58

1

You're trying to run a second command before you've disposed the first. cmdExist will likely have the database locked when you try to run cmdUdpdate.

You should refactor your code so that the using blocks for your commands aren't nested.

Does SQLite lock the database file on reads? seems to suggest you'd be able to read then write, so I'd also make sure you don't have the database open from elsewhere. It does still seem to be the likely culprit though.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 05 '16 at 19:33

Tone

1,701
1
17
18

I separate each request, but I still have the problem, even with dispose after each command. – BlackAlpha Jul 05 '16 at 19:47

score 0 · Answer 3 · answered Jul 05 '16 at 19:54

Try simplifying your select then update into a single statement like this:

UPDATE ... WHERE id = (SELECT id FROM ... )

If this succeeds, then you know that the failure was due to the interaction between the select and the update -- i.e. the separate select must have held a shared lock open on the database that the update ran into.

If this combined statement fails due to a locked database, then you know that something else is blocking the update -- not the select. There is something else in the application causing the database lock failure.

"Database is locked" with SQLite and C#

3 Answers3