sqlite database is locked exception

Question

I am using c# application and running the application in server machine as windows service.When i perform insert , update delete operation from client machine it throws database lock error as below,

database is locked
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.SQLite.SQLiteTransaction.Commit()

Below is my code

public int ExecuteNonQuerySQL(SQLiteCommand cmd)
   {
        int ireturn = 0;
         if (conn.State != ConnectionState.Open)
               Open(DataFile);
          using (SQLiteTransaction dbtrans = conn.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                  using (cmd.Connection=conn)
                    {
                         cmd.CommandText =cmd.CommandText ;
                          cmd.Transaction = dbtrans;
                           ireturn = cmd.ExecuteNonQuery();
                           dbtrans.Commit();
                           cmd.Dispose();
                        }
                   }
               }

Please help me in this , I have done lot of googling and i must find some solution to solve this . Regards

Before Execute nonquery performed , the db connection is closed and reopen again.. — user642378, Oct 05 '12 at 07:13
And after it? Lock exception might be because when you open db connection it locks file. You need to release lock - close connection. So ensure that you really close connection. — Renatas M., Oct 05 '12 at 07:23

Tigran · Accepted Answer · 2012-10-05T07:15:24.900

0

From the code provided it seems that you do not close the connecttion.

Pay attention also on fact, don't know if it's relevant in your case or not, that the Sqlite not very good on supporting concurent inserts and sometimes db got lock, if you operate on it from multiple threads.

edited Oct 05 '12 at 07:15

answered Oct 05 '12 at 07:05

Tigran

61,654
8
86
123

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Change the way you create your Connection and Command objects to be more like this: https://stackoverflow.com/a/17592858/8479

Make sure you're not re-using any Sqlite object on a different thread to the one it was created on.

Sqlite is normally great at concurrency provided you follow those two rules.

edited May 23 '17 at 11:43

Community

1
1

answered Apr 13 '15 at 12:05

Rory

40,559
52
175
261

score 0 · Answer 3 · answered Jul 17 '22 at 22:04

In my case closing the connection was not an issue. The issue was i was opening and closing connection for each Table although the database was same. So do not open connection multiple times for the same database in SQlite.

Open Connection
Do your all of your transactions in just that opened connection.
Close connection.

SQlite is not good at handling this. So if you cannot afford that solution its better to use a Server like SQLServer etc.

sqlite database is locked exception

3 Answers3

Linked