sqlite database is locked on insertion

Question

There's a simple code

var insert = 
    @"INSERT INTO [files] (
     [Name],
     [FullName],
     [MD5])
     VALUES (@Name, @FullName, @MD5);";
using (var con = _db.OpenConnection())
{
    using (var cmd = con.CreateCommand())
    {
        cmd.CommandText = insert;
        cmd.Parameters.AddWithValue("@Name", item.Name);
        cmd.Parameters.AddWithValue("@FullName", item.FullName);
        cmd.Parameters.AddWithValue("@MD5", item.MD5);
        cmd.ExecuteNonQuery();
    }
 }

applications hangs for some time when executing

cmd.ExecuteNonQuery();

and then fails with exception "database is locked". Why this is happening? Application is not multithreaded. DB file is just-created.

Check your Task Manager and see if there is a process holding on to your SQLite database. — Habib, Mar 24 '14 at 14:54
There is some other connection with an active transaction. This might be from the same or another process. — CL., Mar 24 '14 at 16:07
Yes, you're right. Before this code there was call to SqlCommand.ExecuteReader. Though connection and command which created this reader were disposed, this reader itself wasn't disposed. After fixing with "using(reader)" connection was closed properly and error above disappeared. — Evil Beaver, Mar 25 '14 at 05:31

score 4 · Accepted Answer · answered Mar 25 '14 at 05:33

4

Problem solved. Before this code there was call to SqlCommand.ExecuteReader(). Though connection and command which created this reader were disposed, this reader itself wasn't disposed. After fixing with "using(reader)" connection was closed properly and error above disappeared.

In total: DataReader can still hold a connection even if connection and SqlCommand were disposed explicitly.

answered Mar 25 '14 at 05:33

Evil Beaver

379
3
12

1

Can you share please example of inserting data? – POV Nov 09 '17 at 22:53
no, unfirtunately, I leaved this project and thrown the code away. – Evil Beaver Nov 20 '17 at 08:04

score 2 · Answer 2 · answered Aug 06 '21 at 06:54

Be aware to click on Write changes on SQLite browser if it is running and there are any unsaved changes!

In my case it was very stupid of me, I was making changes in SQLite browser and did not click on write changes, which locked the DB to be modified by the services. After I clicked the Write changes button, all the post request worked as expected.
According to @Rohan Shenoy in this topic: SQLite Database Locked exception

score -2 · Answer 3 · edited Jun 06 '18 at 17:49

Your code lacks the call to prepare()

int i=0;
        try
        {
            Conectar();
            SQLiteCommand comando = new SQLiteCommand(con);
            comando.CommandText = "INSERT INTO EMPRESA (NAME,ESTADO) VALUES (@name, @estado)";
            comando.Parameters.AddWithValue("@name", art.NAME);
            comando.Parameters.AddWithValue("@estado", art.ESTADO);
            comando.Prepare();
            con.Open();
            i = comando.ExecuteNonQuery();
            con.Close();
        }
        catch (SQLiteException ex)
        {
            string a = ex.Message.ToString();   
            throw;
        }

        return i;

This is an Englsh speaking forum. Please write only in English. — Alejandro, Jun 06 '18 at 17:48

sqlite database is locked on insertion

3 Answers3

Linked

Related