c# - SQLite 数据库在执行 alter table 查询 C# 时被锁定

Question

以下函数用于我的应用程序的较新版本，它需要向现有数据库添加一列。

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);

            }

        }
    }

当我执行此操作时会出现异常

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

但这并不总是发生。如果 DB Fil 的大小很大，就会发生这种情况。此外，从 IDE 调试时不存在该问题。

Alter Table 查询有任何限制吗？谁能找出导致数据库锁定的问题吗？

Answer 1

我建议启动一个新的SQLiteCommand

Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5).SQLITE_BUSY means that another database connection (probably inanother process) is using the database in a way that prevents you fromusing it. SQLITE_LOCKED means the source of contention is internal andcomes from the same database connection that received theSQLITE_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