C# SQLite 数据库锁定异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17592671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite Database Locked exception
提问by
I am getting Database is lockedexception from SQLitefor some queries only.
我仅针对某些查询从SQLite获取数据库被锁定异常。
Below is my code:
When I execute any select statement it works fine.
When I am executing any write statement on JobsTable it also works fine.
下面是我的代码： 当我执行任何 select 语句时，它工作正常。
当我在JobsTable上执行任何 write 语句时，它也能正常工作。
This works fine:
这工作正常：
ExecuteNonQuery("DELETE FROM Jobs WHERE id=1");
But the same way if I am executing queries for Employeestable it is throwing an exception that database is locked.
This throws Exception:
但同样的，如果我正在执行对Employees表的查询，它会抛出数据库被锁定的异常。
这引发异常：
ExecuteNonQuery("DELETE FROM Employees WHERE id=1");
Below are my functions:
以下是我的功能：
public bool OpenConnection()
{
if (Con == null)
{
Con = new SQLiteConnection(ConnectionString);
}
if (Con.State == ConnectionState.Closed)
{
Con.Open();
//Cmd = new SQLiteCommand("PRAGMA FOREIGN_KEYS=ON", Con);
//Cmd.ExecuteNonQuery();
//Cmd.Dispose();
//Cmd=null;
return true;
}
if (IsConnectionBusy())
{
Msg.Log(new Exception("Connection busy"));
}
return false;
}
public Boolean CloseConnection()
{
if (Con != null && Con.State == ConnectionState.Open)
{
if (Cmd != null) Cmd.Dispose();
Cmd = null;
Con.Close();
return true;
}
return false;
}
public Boolean ExecuteNonQuery(string sql)
{
if (sql == null) return false;
try
{
if (!OpenConnection())
return false;
else
{
//Tx = Con.BeginTransaction(IsolationLevel.ReadCommitted);
Cmd = new SQLiteCommand(sql, Con);
Cmd.ExecuteNonQuery();
//Tx.Commit();
return true;
}
}
catch (Exception exception)
{
//Tx.Rollback();
Msg.Log(exception);
return false;
}
finally
{
CloseConnection();
}
}
This is the Exception:
At line 103 : Cmd.ExecuteNonQuery();
这是例外：在第 103 行： Cmd.ExecuteNonQuery();
Exception Found: Type: System.Data.SQLite.SQLiteException Message: database is locked database is locked Source: System.Data.SQLite
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() at TimeSheet6.DbOp.ExecuteNonQuery(String sql) in d:\Projects\C# Applications\Completed Projects\TimeSheet6\TimeSheet6\DbOp.cs:line 103
发现异常：类型：System.Data.SQLite.SQLiteException 消息：数据库已锁定数据库已锁定来源：System.Data.SQLite
堆栈跟踪：在 System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) 在 System.Data.SQLite.SQLiteDataReader.NextResult() 在 System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior 行为) 在 System.Data .SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery() at TimeSheet6.DbOp.ExecuteNonQuery(String sql) in d:\Projects\C# Applications\Completed Projects\TimeSheet6\TimeSheet6\DbOp。 cs:第103行
采纳答案by Mike Perrenoud
Somewhere along the way a connection is getting left open. Get rid of OpenConnectionand CloseConnectionand change ExecuteNonQueryto this:
在此过程中的某个地方，连接处于打开状态。摆脱OpenConnection与CloseConnection和改变ExecuteNonQuery这样的：
using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
c.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
cmd.ExecuteNonQuery();
}
}
Further, change the way you readdata to this:
此外，将读取数据的方式更改为：
using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
c.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
...
}
}
}
Do not attempt,to manage connection pooling on your own like you are here. First, it's much more complex than what you have coded, but second, it's handled already inside the SQLiteConnectionobject. Finally, if you're not leveraging using, you're not disposingthese objects properly and you end up with issues like what you're seeing now.
不要尝试，像您在这里一样自行管理连接池。首先，它比您编写的代码复杂得多，但其次，它已经在SQLiteConnection对象内部处理了。最后，如果您没有利用using，您就没有正确处理这些对象，最终会出现像您现在看到的那样的问题。
回答by Damith
You can use 'using' statement as below, that will make sure connection & command disposed correctly even in exception
您可以使用下面的“using”语句，这将确保即使在异常情况下也能正确处理连接和命令
private static void ExecuteNonQuery(string queryString)
{
using (var connection = new SQLiteConnection(
ConnectionString))
{
using (var command = new SQLiteCommand(queryString, connection))
{
command.Connection.Open();
command.ExecuteNonQuery();
}
}
}
回答by RAM DANE
I was also getting the same error here:
我在这里也遇到了同样的错误：
if (new basics.HindiMessageBox(HMsg, HTitle).ShowDialog()==true)
{
SQLiteConnection m_dbConnection = new SQLiteConnection(MainWindow.con);
m_dbConnection.Open();
sql = "DELETE FROM `users` WHERE `id`=" + SelectedUser.Id;
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
m_dbConnection.Close();
LoadUserDG();
}
but when I just changed SQLiteConnection declaration location
但是当我刚刚更改 SQLiteConnection 声明位置时
public partial class User : Window
{
SQLiteCommand command;
string sql;
AddUser AddUserObj;
List<basics.users> usersList;
basics.users SelectedUser;
SQLiteConnection m_dbConnection;
// ...
private void DeleteBtn_Click(object sender, RoutedEventArgs e)
{
// ...
if (new basics.HindiMessageBox(HMsg, HTitle).ShowDialog()==true)
{
m_dbConnection = new SQLiteConnection(MainWindow.con);
m_dbConnection.Open();
sql = "DELETE FROM `users` WHERE `id`=" + SelectedUser.Id;
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
m_dbConnection.Close();
LoadUserDG();
}
}
Everything is fine now. I hope this may work for you, too. If someone can say how this happened, I would like to know the details to improve my knowledge, please.
现在一切都很好。我希望这对你也有用。如果有人可以说这是怎么发生的，我想知道细节以提高我的知识，请。
回答by salman
You should close your DataReader before attempting to write any data to the database. Use:
在尝试将任何数据写入数据库之前，您应该关闭 DataReader。用：
dr.Close();
after you finish using the DataReader.
使用完 DataReader 后。
回答by Rohan Shenoy
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.
在我的情况下，我很愚蠢，我在 SQLite 浏览器中进行了更改并且没有单击写入更改，这锁定了要由服务修改的数据库。单击“写入更改”按钮后，所有发布请求都按预期工作。
回答by Andrew
A lot of helpful posts here for folks that may have forgotten to clean up a dangling connection, but there is another way this can happen: SQLite does not support concurrent INSERTs; if you issue two INSERTs at the same time the will be processed in serial. When the INSERTs are quick this is fine, but if an INSERT takes longer than the timeout the second INSERT can fail with this message.
这里有很多有用的帖子供可能忘记清理悬空连接的人使用，但还有另一种可能发生的方式：SQLite 不支持并发插入；如果您同时发出两个 INSERT，将按顺序处理。当 INSERT 很快时，这很好，但是如果 INSERT 花费的时间比超时时间长，则第二个 INSERT 可能会失败并显示此消息。
I had this happen when I used a long running transaction to accumulate a bunch of INSERTs into one big commit. Basically I locked the database from any other activity during the transaction. Switching to journal_mode=WALwill allow concurrent writes and reads, but not concurrent writes.
当我使用长时间运行的事务将一堆 INSERT 累积到一个大提交中时，我发生了这种情况。基本上，我在事务期间将数据库从任何其他活动中锁定。切换到journal_mode=WAL将允许并发写入和读取，但不允许并发写入。
I got rid of the long running transaction and let each INSERT autocommit, and that solved my problem.
我摆脱了长时间运行的事务并让每个 INSERT 自动提交，这解决了我的问题。
