SQLite database is locked on executing alter table query c#

Question

The following function is used for the newer version of my app which needs to add a column to the existing database.

public void AddColumnIfNotexist()
    {
      try
      {
       using (SQLiteCommand cmd = _DBConnection.CreateCommand())
       {
         cmd.CommandText = string.Format("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'myTable'");
         bool hascol = false;
         using (SQLiteDataReader reader = cmd.ExecuteReader())
         {

           if (reader.Read())
           {
             //does column exists?
              hascol = reader.GetString(0).Contains(String.Format("\"{0}\"", "myNewColumn"));
             reader.Close();

            }
         }
               if (!hascol)
               {
                 StringBuilder sql = new StringBuilder(SQLMAXLENGTH);
                 sql.Append("ALTER TABLE Groups ADD COLUMN IsStayingRatio BIT NOT NULL DEFAULT 0");
                  cmd.CommandText = sql.ToString();
                  cmd.ExecuteNonQuery();


                  }
                    }
                }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message + "\r\n\r\n" + ex.StackTrace);
                LogApp.Write("Exception ex:" + ex.Message +"stacktrace "+ex.StackTrace, LogApp.Level.Error);

            }

        }
    }

When I execute this gets an exception as

database is locked

Stacktrace

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(CommandBehavior behavior)
at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
at DataPlus.DB.AddColumnIfNotexist() in D:\Projects\DB.cs:line 343

But this is not always happened. this happens if the size of the DB Fil is large. Also, the issue is not there while debugging from the IDE.

Is there any limitations for the Alter Table query? Can anyone spot out the issue which makes the DB locked?

You are calling `reader.Close()` only if the column was found. Why? — CL., Oct 31 '17 at 06:50
No it calls `if (reader.Read())` returns true right? which means it finds the table and not the column right? — JijeshKV, Oct 31 '17 at 07:12
Sorry, you're correct. But why are you calling `reader.Close()` only if the table was found? — CL., Oct 31 '17 at 09:25
Anyway, the DB is locked because some other connection has an active transaction. — CL., Oct 31 '17 at 09:26
But why are you calling reader.Close() only if the table was found? A: The purpose is to find the column in that table. It checks for the column in the once the table is found. There are app version in which the table is not there. DB is locked because some other connection has an active transaction A: But here I'm Using same connection object. Is there any problem in using such objects for SELECT as well as ALTER queries. — JijeshKV, Oct 31 '17 at 09:32
IS this the only place in your entire application where you are using a connection? — CL., Oct 31 '17 at 10:17
No. But no other function executes unless this one is finished. In other words, it is working fine once this part is not there especially the ExecuteNonquery of the Alter query. So it doesn't look like the synchronisation problem — JijeshKV, Oct 31 '17 at 10:25

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

1

I would suggest to start a new SQLiteCommand

Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5). SQLITE_BUSY means that another database connection (probably in another process) is using the database in a way that prevents you from using it. SQLITE_LOCKED means the source of contention is internal and comes from the same database connection that received the SQLITE_LOCKED error.

Sometimes people think they have finished with a SELECT statement because sqlite3_step() has returned SQLITE_DONE. But the SELECT is not really complete until sqlite3_reset() or sqlite3_finalize() have been called

Error Code SQLITE_LOCKED (6): Database Is Locked

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 31 '17 at 04:45

Patrick

1,089
14
17

1

I haven't checked the error code. it is returning 5 but the message is the database is locked. Also I have tried using different command object and even tried Disconnecting the connection used for select query and create separate connnection for Alter. All of these gives same result. There is no chance of other process accessing the DB as it is handled by my application only. – JijeshKV Oct 31 '17 at 06:25

score 1 · Answer 2 · answered Oct 31 '17 at 15:23

1

Adding the following lines after reader.Close() worked for me.

GC.Collect();
GC.WaitForPendingFinalizers();

It seems that the DB was not released on executing the ALTER query. Got the hint from here.

answered Oct 31 '17 at 15:23

JijeshKV

670
2
7
26

SQLite database is locked on executing alter table query c#

2 Answers2